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

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

Answer 1

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.

Answer 2

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.