Closing Connection When Using Dapper

Closing connection when using Dapper

I am assuming that you are using latest version of Dapper.

With Dapper, there are two ways to manage connection:

Fully manage yourself:
Here, you are fully responsible for opening and closing connection. This is just like how you treat connection while working with ADO.NET.
Allow Dapper to manage it:
Dapper automatically opens the connection (if it was not opened) and closes it (if it was opened by Dapper) for you. This is similar to DataAdapter.Fill() method. ~~I personally do not recommend this way.~~ This may not be applicable every time. Following is what Marc Gravell says in one of the comment for this answer: https://stackoverflow.com/a/12629170/5779732

well, technically open/closed is different to disposed. If you are only going to be opening/closing around the individual calls, you might as well let dapper do it. If you are opening/closing at a wider granularity (per request, for example), it would be better for your code to do it and pass an open connection to dapper.

Below is the quote from here:

Dapper will close the connection if it needed to open it. So if you're just doing 1 quick query - let Dapper handle it. If you're doing many, you should open (once) and close at the end, with all the queries in the middle...just from an efficiency standpoint.

Ofcourse, you can call multiple queries on single connection. But, connection should be closed (by calling Close(), Dispose() method or by enclosing it in using block) to avoid resource leak. Closing connection returns it to connection pool. Involvement of connection pool improves the performance over new connection cost.

In addition to just handling connection, I suggest you implement UnitOfWork to manage transactions as well. Refer this excellent sample on GitHub.

Following source code may help you. Note that this is written for my needs; so it may not work for you as is.

public sealed class DalSession : IDisposable
{
    public DalSession()
    {
        _connection = new OleDbConnection(DalCommon.ConnectionString);
        _connection.Open();
        _unitOfWork = new UnitOfWork(_connection);
    }

    IDbConnection _connection = null;
    UnitOfWork _unitOfWork = null;

    public UnitOfWork UnitOfWork
    {
        get { return _unitOfWork; }
    }

    public void Dispose()
    {
        _unitOfWork.Dispose();
        _connection.Dispose();
    }
}

public sealed class UnitOfWork : IUnitOfWork
{
    internal UnitOfWork(IDbConnection connection)
    {
        _id = Guid.NewGuid();
        _connection = connection;
    }

    IDbConnection _connection = null;
    IDbTransaction _transaction = null;
    Guid _id = Guid.Empty;

    IDbConnection IUnitOfWork.Connection
    {
        get { return _connection; }
    }
    IDbTransaction IUnitOfWork.Transaction
    {
        get { return _transaction; }
    }
    Guid IUnitOfWork.Id
    {
        get { return _id; }
    }

    public void Begin()
    {
        _transaction = _connection.BeginTransaction();
    }

    public void Commit()
    {
        _transaction.Commit();
        Dispose();
    }

    public void Rollback()
    {
        _transaction.Rollback();
        Dispose();
    }

    public void Dispose()
    {
        if(_transaction != null)
            _transaction.Dispose();
        _transaction = null;
    }
}

interface IUnitOfWork : IDisposable
{
    Guid Id { get; }
    IDbConnection Connection { get; }
    IDbTransaction Transaction { get; }
    void Begin();
    void Commit();
    void Rollback();
}

Now, your repositories should accept this UnitOfWork in some way. I choose Dependency Injection with Constructor.

public sealed class MyRepository
{
    public MyRepository(IUnitOfWork unitOfWork) 
    {
        this.unitOfWork = unitOfWork;
    }

    IUnitOfWork unitOfWork = null;

    //You also need to handle other parameters like 'sql', 'param' ect. This is out of scope of this answer.
    public MyPoco Get()
    {
        return unitOfWork.Connection.Query(sql, param, unitOfWork.Transaction, .......);
    }

    public void Insert(MyPoco poco)
    {
        return unitOfWork.Connection.Execute(sql, param, unitOfWork.Transaction, .........);
    }
}

And then you call it like this:

With transaction:

using(DalSession dalSession = new DalSession())
{
    UnitOfWork unitOfWork = dalSession.UnitOfWork;
    unitOfWork.Begin();
    try
    {
        //Your database code here
        MyRepository myRepository = new MyRepository(unitOfWork);
        myRepository.Insert(myPoco);
        //You may create other repositories in similar way in same scope of UoW.

        unitOfWork.Commit();
    }
    catch
    {
        unitOfWork.Rollback();
        throw;
    }
}

Without Transaction:

using(DalSession dalSession = new DalSession())
{
    //Your database code here
    MyRepository myRepository = new MyRepository(dalSession.UnitOfWork);//UoW have no effect here as Begin() is not called.
    myRepository.Insert(myPoco);
}

This way, instead of directly exposing connection in your calling code, you control it at one location.

More details about Repository in above code could be found here.

Please note that UnitOfWork is more than just a transaction. This code handles only transaction though. You may extend this code to cover additional roles.

How does Dapper execute query without explicitly opening connection?

Dapper provide two ways to handle connection.

First is - Allow Dapper to handle it.

Here, you do not need to open the connection before sending it to Dapper. If input connection is not in Open state, Dapper will open it - Dapper will do the actions - Dapper will close the connection.

This will just close the connection. Open/Close is different than Dispose. So, if you really want to Dispose the connection better switch to second way.

Second is - Handle all yourself.

Here, you should explicitly create, open, close and dispose the connection yourself.

Please refer to following links for more details:

https://stackoverflow.com/a/51138718/5779732

https://stackoverflow.com/a/41054369/5779732

https://stackoverflow.com/a/40827671/5779732

Why doesn't Dapper dot net open and close the connection itself?

Dapper now (and for quite some time) deals with this internally. It just works™

Original (outdated) answer:

You aren't wrong. The reason I hadn't noticed this inconvenience is that for legacy reasons (specifically: we used to use LINQ-to-SQL exclusively) our primary connection-like-thing is a DataContext - so we re-expose the dapper methods as extension methods on DataContext.

The silly thing is: what these methods do is:

using(db.Connection.EnsureOpen()) {
    db.Connection.{the dapper method}
}

Here EnsureOpen is a cheeky method that:

if the connection is open, returns null
otherwise, it opens the connection, and returns an IDisposable token that closes the connection when done

So: we obviously felt exactly your pain, but we implemented it a layer further up.

Please log this as a feature request. We have all the code (although I'll need to tweak it slightly to fit the "reader" for non-buffered data) - there's absolutely no reason that dapper can't take ownership of this.

Reusing database connection with Dapper in .NET Web API

The code is misleading. You're not actually creating a new connection when you do this in most circumstances. By default pooling is enabled for a connection unless you explicitly tell it not to.

I'm not 100% certain of the exact criteria behind pooling, but the gist of it is that if you reuse the same connection string rapidly in your code, pooling should only actually create one connection from your app to SQL Server, instead of creating a new connection every time you do new SqlConnection and open it.

By disposing of the connection, you're basically signaling that that particular usage is finished....so the logic behind maintaining the pool can know that you're done with that particular connection. Again I don't know exactly how pooling is implemented, but I imagine it's internally keeping track of how many connections from your code are made so it can decide whether to keep an actual connection to SQL Server open or not.

Ensuring the database connection opens and closes every time I use Dapper to access the database

Here's how I've always done that:

SqlConnection dbConnection;
using (dbConnection = new SqlConnection(connectionString))
{
    /* 
       Whatever Dapper stuff you want to do. Dapper will open the
       connection and the using will tear it down.
    */
}

As for the second part of your question, GetClosedConnection simply instantiates a SqlConnection object, while GetOpenConnection instantiates and opens a SqlConnection object. You (or Dapper) will have to manually call Open() on the object returned by GetClosedConnection.

Dapper source code - will this dispose of my connection properly?

Note following line in the code you post:

private static async Task<IEnumerable<T>> QueryAsync<T>(this IDbConnection cnn,....

That shows QueryAsync is an extension method of IDbConnection. That means, instance of connection is created in your code somewhere.

As mentioned here, there are two ways to manage connection with Dapper:

Fully manage yourself:
Here, you are fully responsible for opening and closing connection. This is just like how you treat connection while working with ADO.NET.
Allow Dapper to manage it:
Dapper automatically opens the connection (if it was not opened) and closes it (if it was opened by Dapper) for you.

Considering this, only choice remain here is to open/close connection in your code or allow Dapper to do this for you. If you are doing this yourself then Dapper does not interfere at all.

If you want to allow Dapper to handle the open/close connection for you and are worried that whether it will close it properly, then yes, it closes it properly.

Find following code in your post:

if (wasClosed) await cnn.TryOpenAsync(cancel).ConfigureAwait(false);
....
if (wasClosed) cnn.Close();

Dapper maintains the state/flag in wasClosed variable if connection was opened by Dapper. You can see in your code that the connection is also properly closed at the end. Further, you can further check the Dapper source code to see how the this is handled in multiple methods. Specially check SqlMapper.Async.cs and SqlMapper.cs files.

Now, this is all about open/close. What about Dispose? Following is what Marc Gravell says in one of the comment for this answer: https://stackoverflow.com/a/12629170/5779732

well, technically open/closed is different to disposed. If you are only going to be opening/closing around the individual calls, you might as well let dapper do it. If you are opening/closing at a wider granularity (per request, for example), it would be better for your code to do it and pass an open connection to dapper.

So, if you really want to Dispose the connection instead of just open/close, better you wrap it in using block in your code and pass open connection to Dapper. As mentioned in the comment, this post discusses difference between Dispose and Close.

Closing Connection When Using Dapper