Dapper.NET Connection/Query Best Practice

Question

So i've read a bunch of links/SO questions, but i still can't get a clear answer on this.

When performing SQL queries with Dapper in an ASP.NET application, what is the best practice for opening/closing connections?

Here's the pattern i'm currently following:

using (var db = new SqlConnection(_connectionString))
{
    return db.Query("dbo.SomeQuery");
}

Essentially, open/close a SQL connection each time as needed.

From my understanding, the above code should automatically open/close the SQL connection (e.g i don't need to explicity do db.Open or db.Close).

The problem i'm seeing is that after a while, i'm getting a bunch of these errors:

InvalidOperationExceptionTimeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I've had a SQL profiler trace during this time period, and i'm not seeing any long queries that would block anything, so it appears as though my ASP.NET web application is running out of connections (as opposed to taking too long to execute a query).

Can someone tell me what i'm doing wrong?

A side note: my application is running as an Azure Web App, so unfortunately i can't actually see how many connections are being opened by the web app. :(

Answer 1

Here is an example of a database context using Dapper internally, I think these are good best practices. I put this together for a project and it had evolutions, best ideas from different places as well as some of my own experience/input. This context examples different types of database operations too:

• scalar
• lists
• single entity
• delete
• update
• dynamic query building
• getting newly inserted record ID
• using SqlMapper.GridReader for processing multiple result sets

Also the ASP.NET Core Options pattern is used to inject the settings/connection string information, not related to Dapper but still handy to see I think since Dapper heavily used in .NET apps.

I introduced a code style concept of calling the class hydrated with raw data "Entity" and the transformed data cleaned up for a caller a "Model". "ViewModel" was devoted to the front-end in my stack. Sometimes the Context would return a Model to the caller and sometimes a raw Entity was returned because the caller needed the raw entity to do more in depth transformations than the Context should be responsible for. This concept may not be perfect and I made it up on my own in some respects as I found that the word "model" has many meanings in different stacks, teams and companies. We model the world, the database can be referred to as the model, an entity could be considered a model...Anyways its an art and a science is what I am saying and just trying to explain the return types better in my code below. :)

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using Microsoft.Extensions.Options;
using Tsl.CustomPrice.Interfaces;
using Tsl.CustomPrice.Model;
using Tsl.CustomPrice.Model.Configuration;
using Tsl.CustomPrice.Model.Tso;
using Tsl.Shared.Enumeration;

namespace Tsl.CustomPrice.Data
{
    public class TsoContext : ITsoContext
    {
        private readonly string _connectionString;
        private IDbConnection Connection => new SqlConnection(_connectionString);

        public TsoContext(IOptions<DbSettings> settings)
        {
            _connectionString = settings.Value.ConnectionStrings.TsoConnection;
        }

        #region Custom Price Column

        public int GetCustomPriceColumnCountForUser(int userId)
        {
            using (IDbConnection conn = Connection)
            {
                var query = @"SELECT count(*)
                            FROM [TSO].[dbo].[CustomPriceColumn] (NOLOCK)
                            WHERE [EntityID] = @userId and [EntityTypeID] = 1 --User";

                return conn.ExecuteScalar<int>(query, new { userId });
            }
        }

        public CustomPriceColumnModel GetLastUpdatedCustomPriceColumn(int userId)
        {
            using (IDbConnection conn = Connection)
            {
                var query = @"SELECT [CustomPriceColumnID]
                              ,[EntityID]
                          FROM [TSO].[dbo].[CustomPriceColumn] (NOLOCK)
                            WHERE [EntityID] = @userId and [EntityTypeID] = 1 --User
                            ORDER BY [LastUpdatedDateTime] desc";

                return conn.Query<CustomPriceColumnModel>(query, new { userId }).FirstOrDefault();
            }
        }

        public CustomPriceColumnModel GetCustomPriceColumn(int customPriceColumnId, int userId)
        {
            using (IDbConnection conn = Connection)
            {
                const string query = @"SELECT [CustomPriceColumnID]
                          ,[EntityID]
                          ,[EntityTypeID]
                          ,[CustomPriceColumnTypeID]
                          ,a.[CreatedDateTime]
                          ,case when (CreatedByUserID = @userId or CustomPriceColumnTypeID = 2) then 1 else 0 end as IsEditable
                          ,b.FirstName as CreatedByFirstName
                          ,b.LastName as CreatedByLastName
                      FROM [dbo].[CustomPriceColumn] a (nolock)
                      left join [User] b on b.UserID = a.CreatedByUserID
                      WHERE [CustomPriceColumnID] = @customPriceColumnId";

                return conn.QueryFirstOrDefault<CustomPriceColumnModel>(query, new { @customPriceColumnId=customPriceColumnId, @userId=userId });
            }
        }
        public IEnumerable<CustomPriceColumnModel> GetCustomPriceColumns(int userId)
        {
            using (IDbConnection conn = Connection)
            {
                const string query = @"SELECT
                              [CustomPriceColumnID]
                              ,[EntityID]
                              ,[EntityTypeID]
                              ,case when (CreatedByUserID = @userId or CustomPriceColumnTypeID = 2) then 1 else 0 end as IsEditable
                              ,b.FirstName as CreatedByFirstName
                              ,b.LastName as CreatedByLastName
                            FROM CustomPriceColumn cpc (nolock)
                                inner join [User] u (nolock)
                                    on u.UserID = @userId
                                left join [User] b on b.UserID = CreatedByUserID
                            WHERE (EntityID = @userId and EntityTypeID = 1)
                                or (CreatedByUserID = @userId)
                                or (EntityID = u.CompanyID and EntityTypeID = 0)";

                return conn.Query<CustomPriceColumnModel>(query, new { userId });
            }
        }


        public int CreateCustomPriceColumn(string customPriceColumnName, string customPriceColumnDescription, int entityId, int createdByUserId, string countryCode, IndustryTypes industryTypeId, EntityTypes entityTypeId, CustomPriceColumnTypes customPriceColumnTypeId, string systemUserName, string actorName)
        {
            using (IDbConnection conn = Connection)
            {
                var query = @"INSERT INTO [TSO].[dbo].[CustomPriceColumn]
                                       ([EntityID]
                                       ,[EntityTypeID]
                                       ,[CustomPriceColumnTypeID]
                                       ,[CreatedByUserID]
                                       ,[IndustryTypeID]
                                       ,[CountryCode]
                                       ,[CustomPriceColumnName]
                                       ,[CustomPriceColumnDescription]
                                       ,[CreatedDateTime]
                                       ,[LastUpdatedDateTime]
                                       ,[ActorName]
                                       ,[SystemUserName])
                                 VALUES
                                       (@entityId
                                       ,@entityTypeId
                                       ,@customPriceColumnTypeId
                                       ,@createdByUserId
                                       ,@industryTypeId
                                       ,@countryCode
                                       ,@customPriceColumnName
                                       ,@customPriceColumnDescription
                                       ,getdate()
                                       ,getdate()
                                       ,@actorName
                                       ,@systemUserName);
                                    SELECT CAST(SCOPE_IDENTITY() as int)";


                return conn.ExecuteScalar<int>(query,
                    new
                    {
                        entityId,
                        entityTypeId,
                        customPriceColumnTypeId,
                        createdByUserId,
                        industryTypeId,
                        countryCode,
                        customPriceColumnName,
                        customPriceColumnDescription,
                        actorName,
                        systemUserName
                    });

            }
        }

        public void UpdateCustomPriceColumn(int customPriceColumnId, string customPriceColumnName, string customPriceColumnDescription, int entityId, IndustryTypes industryTypeId, EntityTypes entityTypeId, CustomPriceColumnTypes customPriceColumnTypeId, string systemUserName, string actorName)
        {
            using (IDbConnection conn = Connection)
            {
                var query = @"UPDATE [TSO].[dbo].[CustomPriceColumn]
                           SET [EntityID] = @entityId
                              ,[EntityTypeID] = @entityTypeId
                              ,[CustomPriceColumnTypeID] = @customPriceColumnTypeId
                              ,[IndustryTypeID] = @industryTypeId
                              ,[CustomPriceColumnName] = @customPriceColumnName
                              ,[CustomPriceColumnDescription] = @customPriceColumnDescription
                              ,[LastUpdatedDateTime] = getdate()
                         WHERE [CustomPriceColumnID] = @customPriceColumnId";


                conn.Execute(query,
                    new
                    {
                        customPriceColumnId,
                        entityId,
                        entityTypeId,
                        customPriceColumnTypeId,
                        industryTypeId,
                        customPriceColumnName,
                        customPriceColumnDescription,
                        actorName,
                        systemUserName
                    });

            }
        }

        public void DeleteCustomPriceColumn(int customPriceColumnId)
        {
            using (IDbConnection conn = Connection)
            {
                var query = @"DELETE FROM [TSO].[dbo].[CustomPriceColumn]
                             WHERE [CustomPriceColumnID] = @customPriceColumnId";


                conn.Execute(query,
                    new
                    {
                        customPriceColumnId
                    });

            }
        }

        public CustomPriceColumnMetaDataForCpfExportEntity GetCustomPriceColumnMetaDataForCpfExport(int customPriceColumnId)
        {
            var ret = new CustomPriceColumnMetaDataForCpfExportEntity();
            using (IDbConnection conn = Connection)
            {
                const string query = @"
                    -- TOTAL RULES VS. TOTAL PERCENT RULES
                    SELECT tr.TotalRules, trp.TotalPercentRules FROM
                    (SELECT CustomPriceColumnId, COUNT(*) AS TotalRules FROM tso.dbo.CustomPriceRule WHERE CustomPriceColumnID = @CustomPriceColumnId GROUP BY CustomPriceColumnID) as tr
                    JOIN
                    (SELECT CustomPriceColumnId, COUNT(*) AS TotalPercentRules FROM tso.dbo.CustomPriceRule WHERE CustomPriceColumnID = @CustomPriceColumnId AND IsPercent = 1 GROUP BY CustomPriceColumnID) AS trp
                    ON tr.CustomPriceColumnID = trp.CustomPriceColumnID;
                    -- TOTAL RULES BY BASE COLUMN
                    SELECT BaseColumnPriceTypeID, OperationTypeId, COUNT(*) AS TotalRules FROM tso.dbo.CustomPriceRule WHERE CustomPriceColumnID = @CustomPriceColumnId
                    GROUP BY BaseColumnPriceTypeID, OperationTypeId";

                using (SqlMapper.GridReader multi = conn.QueryMultiple(query, new { @customPriceColumnId = customPriceColumnId }))
                {
                    ret.MetaData = multi.Read<CustomPriceColumnMetaDataEntity>().SingleOrDefault();
                    ret.BasePriceColumnRuleCounts = multi.Read<BasePriceColumnRuleCountEntity>().ToList();
                }

                return ret;
            }
        }
        #endregion

        #region Custom Price Rule

        public IEnumerable<int> GetCustomPriceRulesIds(int customPriceColumnId)
        {

            using (IDbConnection conn = Connection)
            {
                var query =
                    @"SELECT [CustomPriceRuleId] FROM [dbo].[CustomPriceRule] (nolock) WHERE [CustomPriceColumnId] = @customPriceColumnId";

                return conn.Query<int>(query, new {customPriceColumnId});
            }

        }

        public IEnumerable<CustomPriceRuleModel> GetCustomPriceRules(int customPriceColumnId, int index, int pageSize)
        {
            //implementation can be extended to allow sorting by other 
            var sortBy = "a.CreatedDateTime desc";

            using (IDbConnection conn = Connection)
            {
                var query = @"SELECT  *
                            FROM     
                                (SELECT ROW_NUMBER() OVER ( ORDER BY {0}) AS RowNum,  
                                    COUNT(*) OVER () AS TotalRows, 
                                    [CustomPriceRuleId] 
                                    FROM [dbo].[CustomPriceRule] a (nolock) 
                                        left outer join [dbo].[Commodity] b (nolock) on a.CommodityId = b.CommodityID 
                                        left outer join [dbo].[Company] c (nolock) on a.ManufacturerCompanyId = c.CompanyId 
                                        left outer join [dbo].[Item] d (nolock) on a.ItemId = d.ItemID 
                                    WHERE [CustomPriceColumnId] = @customPriceColumnId 
                                  ) AS result 
                            WHERE RowNum BETWEEN ( ((@index - 1) * @pageSize )+ 1) AND @index*@pageSize 
                                        ORDER BY RowNum";

                query = string.Format(query, sortBy);

                return conn.Query<CustomPriceRuleModel>(query, new { customPriceColumnId, index, pageSize });
            }
        }

        public CustomPriceRuleModel GetCustomPriceRule(int customPriceRuleId)
        {
            using (IDbConnection conn = Connection)
            {
                const string query = @"SELECT [CustomPriceRuleId]
                                      ,[CustomPriceColumnId]
                                  FROM [TSO].[dbo].[CustomPriceRule]
                                  WHERE [CustomPriceRuleId] = @customPriceRuleId";

                return conn.QueryFirstOrDefault<CustomPriceRuleModel>(query, new { customPriceRuleId });
            }
        }

        public CustomPriceRuleModel GetCustomPriceRuleByItemId(int customPriceColumnId, int itemId)
        {
            using (IDbConnection conn = Connection)
            {
                const string query = @"SELECT [CustomPriceRuleId]
                                      ,[CustomPriceColumnId]
                                      ,[CustomPriceRuleLevelId]
                                  FROM [TSO].[dbo].[CustomPriceRule]
                                  WHERE [CustomPriceColumnId] = @customPriceColumnId and [ItemId] = @itemId";

                return conn.QueryFirstOrDefault<CustomPriceRuleModel>(query, new { customPriceColumnId, itemId });
            }
        }

        public CustomPriceRuleModel FindCustomPriceRule(int customPriceColumnId, CustomPriceRuleLevels customPriceRuleLevel,
                int? itemId, int? manufacturerCompanyId, int? commodityId, string ucc)
        {
            using (IDbConnection conn = Connection)
            {
                string query = @"SELECT [CustomPriceRuleId]
                                      ,[CustomPriceColumnId]
                                      ,[UCC]
                                  FROM [TSO].[dbo].[CustomPriceRule]
                                  WHERE [CustomPriceColumnId] = @customPriceColumnId
                                  AND [CustomPriceRuleLevelId] = @customPriceRuleLevel";
                var parameters = new DynamicParameters();
                parameters.Add("@customPriceColumnId", customPriceColumnId);
                parameters.Add("@customPriceRuleLevel", (int)customPriceRuleLevel);

                switch (customPriceRuleLevel)
                {
                    case (CustomPriceRuleLevels.Item):
                        query += @" AND ItemId = @itemId";
                        parameters.Add("@itemId", itemId);
                        break;
                    case (CustomPriceRuleLevels.ManufacturerAndCommodity):
                        query += @" AND ManufacturerCompanyID = @manufacturerCompanyId
                            AND CommodityId = @commodityId";
                        parameters.Add("@manufacturerCompanyId", manufacturerCompanyId);
                        parameters.Add("@commodityId", commodityId);
                       break;
                    case (CustomPriceRuleLevels.Manufacturer):
                        query += @" AND ManufacturerCompanyID = @manufacturerCompanyId";
                        parameters.Add("@manufacturerCompanyId", manufacturerCompanyId);
                        break;
                    case (CustomPriceRuleLevels.Commodity):
                        query += @" AND CommodityId = @commodityId";
                        parameters.Add("@commodityId", commodityId);
                        break;
                    case (CustomPriceRuleLevels.Ucc):
                        query += @" AND ManufacturerCompanyID = @manufacturerCompanyId
                            AND Ucc = @ucc";
                        parameters.Add("@manufacturerCompanyId", manufacturerCompanyId);
                        parameters.Add("@ucc", ucc);
                        break;
                }

                return conn.QueryFirstOrDefault<CustomPriceRuleModel>(query, parameters);
            }
        }

        public void UpdateCustomPriceRule(int customPriceRuleId, CustomPriceRuleLevels customPriceRuleLevel, int? itemId, int? manufactuerCompanyId,
            int? commodityId, PriceTypes? baseColumnPriceTypeId, CustomPriceOperations? operationTypeId, decimal customPriceRuleValue, bool isPercent, string customPriceRuleDescription,
            Uom? fixedPriceUnitIfMeasureTypeCode, string ucc, string actorName, string systemUsername)
        {

            using (IDbConnection conn = Connection)
            {
                var query = @"UPDATE [TSO].[dbo].[CustomPriceRule]
                               SET [CustomPriceRuleLevelId] = @customPriceRuleLevel
                                  ,[ItemId] = @itemId
                                  ,[ManufacturerCompanyId] = @manufactuerCompanyId
                                  ,[CommodityId] = @commodityId
                                  ,[BaseColumnPriceTypeId] = @baseColumnPriceTypeId
                                  ,[OperationTypeId] = @operationTypeId
                                  ,[CustomPriceRuleValue] = @customPriceRuleValue
                                  ,[IsPercent] = @isPercent
                                  ,[CustomPriceRuleDescription] = @customPriceRuleDescription
                                  ,[FixedPriceUnitOfMeasureTypeCode] = @strUom
                                  ,[LastUpdatedDateTime] = getdate()
                                  ,[ActorName] = @actorName
                                  ,[SystemUsername] = @systemUsername
                                  ,[UCC] = @ucc
                             WHERE [CustomPriceRuleId] = @customPriceRuleId";

                var strUom = fixedPriceUnitIfMeasureTypeCode != null ? fixedPriceUnitIfMeasureTypeCode.ToString() : null;
                // HACK: See TSL-1235 : CustomPriceOperations.FixedPrice must translate to a null in the CustomPriceRule row.
                CustomPriceOperations? opTypeId = operationTypeId == CustomPriceOperations.FixedPrice ? null : operationTypeId;

                conn.Execute(query,
                    new
                    {
                        customPriceRuleId,
                        customPriceRuleLevel,
                        itemId,
                        manufactuerCompanyId,
                        commodityId,
                        baseColumnPriceTypeId,
                        operationTypeId = opTypeId,
                        customPriceRuleValue,
                        isPercent,
                        customPriceRuleDescription,
                        strUom,
                        ucc,
                        actorName,
                        systemUsername
                    });

            }
        }



        public int CreateCustomPriceRule(int customPriceColumnId, CustomPriceRuleLevels customPriceRuleLevel, int? itemId,
        int? manufactuerCompanyId, int? commodityId, PriceTypes? baseColumnPriceTypeId, CustomPriceOperations? operationTypeId,
        decimal customPriceRuleValue, bool isPercent, string customPriceRuleDescription, Uom? fixedPriceUnitIfMeasureTypeCode,
        string ucc, string actorName, string systemUsername)
        {
            using (IDbConnection conn = Connection)
            {
                var query = @"INSERT INTO [TSO].[dbo].[CustomPriceRule]
                               ([CustomPriceColumnId]
                               ,[CustomPriceRuleLevelId]
                               ,[ItemId]
                               ,[ManufacturerCompanyId]
                               ,[CommodityId]
                               ,[BaseColumnPriceTypeId]
                               ,[OperationTypeId]
                               ,[CustomPriceRuleValue]
                               ,[IsPercent]
                               ,[CustomPriceRuleDescription]
                               ,[FixedPriceUnitOfMeasureTypeCode]
                               ,[CreatedDateTime]
                               ,[LastUpdatedDateTime]
                               ,[ActorName]
                               ,[SystemUsername]
                               ,[UCC])
                         VALUES
                               (@customPriceColumnId
                               ,@customPriceRuleLevel
                               ,@itemId
                               ,@manufactuerCompanyId
                               ,@commodityId
                               ,@baseColumnPriceTypeId
                               ,@operationTypeId
                               ,@customPriceRuleValue
                               ,@isPercent
                               ,@customPriceRuleDescription
                               ,@strUom
                               ,getdate()
                               ,getdate()
                               ,@actorName
                               ,@systemUsername
                               ,@ucc);
                                    SELECT CAST(SCOPE_IDENTITY() as int)";

                var strUom = fixedPriceUnitIfMeasureTypeCode != null ? fixedPriceUnitIfMeasureTypeCode.ToString() : null;

                return conn.ExecuteScalar<int>(query,
                    new
                    {
                        customPriceColumnId,
                        customPriceRuleLevel,
                        itemId,
                        manufactuerCompanyId,
                        commodityId,
                        baseColumnPriceTypeId,
                        operationTypeId,
                        customPriceRuleValue,
                        isPercent,
                        customPriceRuleDescription,
                        strUom,
                        ucc,
                        actorName,
                        systemUsername
                    });

            }
        }

        public void DeleteCustomPriceRule(int customPriceRuleId)
        {
            using (IDbConnection conn = Connection)
            {
                var query = @"DELETE FROM [TSO].[dbo].[CustomPriceRule]
                             WHERE [CustomPriceRuleId] = @customPriceRuleId";


                conn.Execute(query,
                    new
                    {
                        customPriceRuleId
                    });

            }
        }

        public void DeleteCustomPriceRules(IEnumerable<int> customPriceRuleIds)
        {
            var cprIdsList = customPriceRuleIds.ToList();

            if (!cprIdsList.Any()) return;

            using (IDbConnection conn = Connection)
            {
                var query = @"DELETE FROM [TSO].[dbo].[CustomPriceRule]
                             WHERE [CustomPriceRuleId] in ("
                            + string.Join(",", cprIdsList)
                            + ")";


                conn.Execute(query);

            }
        }

        public List<CustomPriceRuleForExportEntity> GetCustomPriceRulesForExport(int customPriceColumnId)
        {
            using (IDbConnection conn = Connection)
            {
                const string query = @"SELECT 
                    cpr.CustomPriceRuleLevelID
                    ,cpr.Ucc
                    ,i.Upc
                    ,c.CommodityCode
                    ,mu.ShortName as ManufacturerShortName
                    ,i.ManufacturerCatalogCode
                    ,cpr.CustomPriceRuleDescription
                    ,cpr.BaseColumnPriceTypeId
                    ,cpr.OperationTypeId
                    ,cpr.CustomPriceRuleValue
                    ,cpr.IsPercent
                    ,cpr.ItemId
                    ,cpr.ManufacturerCompanyId
                    ,cpr.CommodityId
                    FROM TSO.dbo.CustomPriceRule cpr
                    LEFT OUTER JOIN TSO.dbo.Item i ON cpr.ItemId = i.ItemId
                    LEFT OUTER JOIN TSO.dbo.ManufacturerUcc mu
                        ON ((cpr.CustomPriceRuleLevelId <> 1 AND cpr.ManufacturerCompanyId = mu.CompanyID AND cpr.UCC = mu.UCC)
                        OR (cpr.CustomPriceRuleLevelId = 1 AND LEFT(i.UPC, 6) = mu.UCC) and i.ManufacturerCompanyID = mu.CompanyID)
                    LEFT OUTER JOIN TSO.dbo.Commodity c ON cpr.CommodityId = c.CommodityId
                    WHERE cpr.CustomPriceColumnId = @customPriceColumnId";

                return conn.Query<CustomPriceRuleForExportEntity>(query, new { @customPriceColumnId = customPriceColumnId }).ToList();
            }
        }

        #endregion

        public bool IsAllowedToModifyCustomPriceColumn(int userId, int customPriceColumnId)
        {
            using (IDbConnection conn = Connection)
            {
                // Check access to CP column.
                var getCpQuery = @"SELECT [CustomPriceColumnID]
                FROM [CustomPriceColumn] cpc
                JOIN [User] u ON u.UserId = @userId
                WHERE cpc.[CustomPriceColumnId] = @customPriceColumnId
                AND ((cpc.[CreatedByUserID] = @userId) /* Created by the User */
                OR (cpc.EntityID = u.CompanyId and cpc.EntityTypeID = 0 AND CustomPriceColumnTypeID = 2)) /* OR CREATED BY SOMEONE IN THE COMPANY AND MARKED PUBLIC-EDITABLE */"; 
                return conn.Query<CustomPriceColumnModel>(getCpQuery, new { @customPriceColumnId = customPriceColumnId, @userId = userId }).SingleOrDefault() != null;

            }
        }
    }
}