Trying to understand erratic behavior of DB Connection used by Dapper

Question

Trying to understand erratic behavior of DB Connection used by Dapper

dapper micro-orm orm sqlconnection sql-server

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();
    }
}

Yogiraj

View more on Stack Overflow

Gboyega Sulaiman · Accepted Answer · 6/10/2015 12:31:49 PM

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

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator