Handling database connections efficiently

asp.net-mvc c# dapper

Question

I am using below helper class for Dapper operations. If there are multiple entities to be mapped to complex DTO object, I am retrieving it individually and attaching to the property manually.

For ex: To retrieve a customer, I have a Customer class with Orders and Addresses property. I know about QueryMultiple method, but in real time we have different queries.

 cust.Orders = DapperHelper.ExecList<OrderDTO>(qry1, params1);
 cust.Addresses = DapperHelper.ExecList<AddressDTO>(qry2, params2);

My doubt is if I open the connection for populating child items each time will there be any performance hit. Instead, can I initialize connection in the constructor and close it in the dispose method thus utilizing a single connection for each request.

 public class DapperHelper
    {
        private static string _conn =  Convert.ToString(ConfigurationManager.ConnectionStrings["dbContext"]);


        public static IEnumerable<T> ExecList<T>(string query, object cmdParams = null, CommandType cmdType = CommandType.Text, bool buffered = true)
        {
            IEnumerable<T> list;
            using (IDbConnection _db = new SqlConnection(_conn))
            {
                list = _db.Query<T>(query, param: cmdParams, commandTimeout: 0, commandType: cmdType, buffered: buffered);
                _db.Close();
            }
            return list;
        }

         public static T ExecSingleOrDefault<T>(string query, object cmdParams = null, CommandType cmdType = CommandType.Text)
    {
        T obj;
        using (IDbConnection _db = new SqlConnection(_conn))
        {
            obj = _db.Query<T>(query, param: cmdParams, commandTimeout: 0, commandType: cmdType).SingleOrDefault();
            _db.Close();
        }
        return obj;
    }
}

Accepted Answer

SQLConnection automatically use connection pooling by default, so you don't have to worry about the network connection overhead as long as you dispose your connection correctly.


Popular Answer

If you know QueryMultiple why not place your two models in one like

public class CustomerViewModel

   List<Orders> orderList;
   List<Address> addresslit;
end class

Then when you call Dapper.Query

Map it as

 IEnumerable<CustomrerViewModel> list

This way only one sql connection is required




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why