How do I handle Database Connections with Dapper in .NET?

.net c# dapper

Question

I've been playing with Dapper, but I'm not sure of the best way to handle the database connection.

Most examples show the connection object being created in the example class, or even in each method. But it feels wrong to me to reference a connection string in every clss, even if it's pulling from the web.config.

My experience has been with using a DbDataContext or DbContext with Linq to SQL or Entity Framework, so this is new to me.

How do I structure my web apps when using Dapper as my Data Access strategy?

Popular Answer

I created extension methods with a property that retrieves the connection string from configuration. This lets the callers not have to know anything about the connection, whether it's open or closed, etc. This method does limit you a bit since you're hiding some of the Dapper functionality, but in our fairly simple app it's worked fine for us, and if we needed more functionality from Dapper we could always add a new extension method that exposes it.

internal static string ConnectionString = new Configuration().ConnectionString;

    internal static IEnumerable<T> Query<T>(string sql, object param = null)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            return conn.Query<T>(sql, param);
        }
    }

    internal static int Execute(string sql, object param = null)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            return conn.Execute(sql, param);
        }
    }



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