Menu
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. :(
658
Here we are creating the connection by 'using'. So every time it will create a new connection instead of reusing the existing connection. We are not adhering to the connection pooling. But the reality is, as we discussed earlier, Dapper is a mapping library.
C# Dapper Queryvar cars = con. Query<Car>("SELECT * FROM cars"). ToList(); The Query method executes the SELECT * FROM cars statement and returns a list of objects.
Answers. SqlConnection is used to connect to Sql Server databases only. IDbConnection is a generic interface.
Dapper is literally much faster than Entity Framework Core considering the fact that there are no bells and whistles in Dapper. It is a straight forward Micro ORM that has minimal features as well. It is always up to the developer to choose between these 2 Awesome Data Access Technologies.
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:
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;
}
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
