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