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

c# dapper database-connection sql sql-server

Question

Here is what I am currently doing in one of my repository classes:

private IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString);

public IEnumerable<Product> GetProducts(int categoryId = null, bool? active = null)
{
    StringBuilder sql = new StringBuilder();
    sql.AppendLine("SELECT * ");
    sql.AppendLine("FROM Product ");
    sql.AppendLine("WHERE @CategoryId IS NULL OR CategoryId = @CategoryId ");
    sql.AppendLine("  AND @Active IS NULL OR Active = @Active");

    return this.db.Query<Product>(sql.ToString(), new { CategoryId = categoryId, Active = active }).ToList();
}

One thing I want to do is put the IDbConnection property in a BaseRepository that all of my other repos inherit from. What do I do to ensure my database connection opens and closes properly in each of my data access functions like the example above? Here is what I currently do with Entity Framework (w/ a using statement around each function, but now I am switching the DAL to use pure Dapper:

using (var context = new MyAppContext())
{
    var objList = (from p in context.Products
                   where (categoryId == null || p.CategoryId == categoryId) &&
                         (active == null || p.Active == active)
                   select p).ToList();

    return objList;
}

I noticed in the Dapper examples that everything is wrapped in a using statement like I would expect, but occasionally I see them wrapping their functions in the follow using:

using (var connection = Program.GetClosedConnection())

GetClosedConnection() returns a new SqlConnection, but what is the difference between the two?

public static SqlConnection GetOpenConnection(bool mars = false)
{
    var cs = connectionString;
    if (mars)
    {
        SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(cs);
        scsb.MultipleActiveResultSets = true;
        cs = scsb.ConnectionString;
    }
    var connection = new SqlConnection(cs);
    connection.Open();
    return connection;
}
public static SqlConnection GetClosedConnection()
{
    return new SqlConnection(connectionString);
}

Accepted Answer

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.


Popular Answer

This answer will be based on your wish to "avoid repitition".

Make an extension class of Dapper and put functions in it and use that instead. Like so:

    public IEnumerable<T> Query<T>(string sqlQuery, object parameters = null)
    {
        this.activeConnection.Open();

        var result = this.activeConnection
            .Query<T>(sqlQuery, parameters);

        this.activeConnection.Close();

        return result;
    }

And in the top of the class put a

    public SqlConnection activeConnection { get; private set; }

Which is always set in the constructor of the class.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why