Trying to understand erratic behavior of DB Connection used by Dapper

Question

I need some help better understanding how Dapper handles DB Connections, and why DB Connection isn't being properly disposed of. I have coded around the different possible cases. In my code, I am checking to see if the _connection is null, and creating a new connection accordingly. On proceeding requests, I am finding that sometimes the connection is left open, and other times I am finding that the connection string is missing in the object (rendering it unusable, but not setting it to NULL). I am handling these cases accordingly, but would like to understand why the connection has these varying states, and is not disposed of even though the client code is expressly implementing a using statement to wrap the code. Is it ADO.net's handling of connections,is there a side effect of Dapper or just issues with my code?

Connection Management code

public class DatabaseContext : IDatabaseContext 
{
    private readonly string _connectionString;

    private DbConnection _connection;


    public DatabaseContext(string connectionString)
    {
        _connectionString = connectionString;

    }

    public IDbConnection Connection
    {
        get
        {

            if (_connection == null)
                _connection = new SqlConnection(_connectionString);

            if (string.IsNullOrEmpty(_connection.ConnectionString))
                _connection.ConnectionString = _connectionString;

            if (_connection.State != ConnectionState.Open)
                _connection.Open();

            return _connection;
        }
    }
}

Client code

public IEnumerable<PostSearResults> Search(SearchPostsBy searchPostsBy)
{
    DynamicParameters param;
    var sql = GetSearchSql(searchPostsBy,out param);//Gets SQL 

    using (var connection = _databaseContext.Connection)
    {
        var posts = connection.Query<PostSearResults>(sql, param);
        return posts.ToList();
    }
}

So you don't know why you're writing that code?! Why did you write it then? Btw code review is off topic you should post your question on codereview.stackexchange.com — MikeSW, May 04 '14 at 17:27
@MikeSW I want to know why DBConnection is not getting properly disposed even though the client code is wrapping it in using statement. Something is holding on to it. Is it ADO.net's handling of connections,is there a side effect of Dapper or just issues with my code? With these three possibilities, can it be just a review question? Why comment on me (off topic) than the issue? — Yogiraj, May 04 '14 at 22:04
You should have asked this instead of writing an explanation of what the code does. If you want to reuse a connection you should close it NOT dispose it. Disposing resets the SqlConnection and it will throw 'object already disposed' or something similar — MikeSW, May 04 '14 at 22:36
Updated. Thanks for the input. Using closes the connection as well (http://stackoverflow.com/questions/4389506/ado-net-closing-connection-when-using-using-statement) which is the intent for this particular request. For the requests (user initiated web operation) that follow the one mentioned in question, I find that the DBConnection object is still in tact and open let alone disposed. Why is this happening? I tried explicitly implementing the dispose method as well by implementing IDisposable interface. I still get the same issue. — Yogiraj, May 04 '14 at 23:14

score 2 · Answer 1 · answered Jun 10 '15 at 12:31

I use Dapper in a web application as well and I implement a DapperContext similar to yours, but I implement the IDispose on the the class as follows:

    #region IDisposable

    private bool disposed = false;

    protected virtual void Dispose(bool disposing)
    {
        if (!this.disposed)
        {
            if (disposing)
            {
                if (transaction != null)
                { transaction.Dispose(); }
                if (cn != null)
                { cn.Dispose(); }
            }
        }
        this.disposed = true;
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    #endregion

My version of Dapper is the one that opens a connection if it is closed when we make a call, it then closes after executing the query, if the connection is opened when I make the call Dapper just execute the query and leave the connection opened.

I also use DI, so my DapperContext lifetime is managed by the DI container, so it is disposed when the web request context ended and destroyed. My Dapper Repository is like as follows, with a sample method GetPE

private readonly IDbConnection context;
private readonly DapperContext dapperContext;

public SFRepository(DapperContext dbContext)
{
    dapperContext = dbContext;
    context = dbContext.Connection;
}

public PEData GetPE(int peID)
{
     PEData rec = context.Query<PEData>("SELECT * FROM PEDATA WHERE ID= @ID", new { ID = peID }).FirstOrDefault();

     return rec;
}

It has been very stable and I don't have any strange behaviour.

Trying to understand erratic behavior of DB Connection used by Dapper

1 Answers1