CODEWITHSUNDEEP
c#asp.net-mvcdapper
Sunny
Sunny

Reputation: 4809

Handling database connections efficiently

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;
    }
}

Upvotes: 1

Views: 479

Answers (2)

tia
tia

Reputation: 9698

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.

Upvotes: 4

agentpx
agentpx

Reputation: 1081

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

Upvotes: 1

Related Questions