I have implemented my DAL with Dapper by using following artical
But It is created a connection with db each and every database call. It does not reuse connection pool. I believe i have closed and disposed the connection properly.
This is the sample of how to call from service layer
dtoList = unitofWork.RegionalSettingRepository.GetCurrencySymbols();
unitofWork.Commit(); // To close the connections
This is Repository call
public List<CurrencySymbolDTO> GetCurrencySymbols()
{
List<CurrencySymbolDTO> dtoList = null;
try
{
string strSQL = "SELECT * from CurrencySymbol";
dtoList = this.Connection.Query<CurrencySymbolDTO>(strSQL, null, transaction: Transaction).ToList();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
}
return dtoList;
}
Can someone please tell me why lot of connections are created for each and every database call. I did use following SQL query to monitor the number of connections
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE DB_NAME(dbid) ='database name'
GROUP BY dbid, loginame
Thanks in advance
The article you referred is great for learning UoW with Dapper; I myself started my study of UoW with that article. I had similar problems while implementing it to fulfill my business needs and I modified the code a bit. You can find detailed code in this answer.
With this code, you get more control over connection/transaction; you call it like this:
With transaction:
using(DalSession dalSession = new DalSession())
{
UnitOfWork unitOfWork = dalSession.UnitOfWork;
unitOfWork.Begin();
try
{
//Your database code here
unitOfWork.Commit();
}
catch
{
unitOfWork.Rollback();
throw;
}
}
Without Transaction:
using(DalSession dalSession = new DalSession())
{
//Your database code here
}