Dapper with UOW - Don't close connections

Question

Dapper with UOW - Don't close connections

dapper repository-pattern unit-of-work

Question

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

Denuka

View more on Stack Overflow

Amit Joshi · Accepted Answer · 8/2/2017 11:17:23 AM

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
}

Dapper with UOW - Don't close connections

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator