Reusing database connection with Dapper in .NET Web API

Question

Most, if not all, examples of Dapper in .NET I've seen use a structure like this:

    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();
        return conn.Query<T>(sql, param);
    }

If you have a Web API , is it wise to make a new connection every time a request is made to the server? Or would it be a better pattern to abstract the connection into another class and inject it into each controller so they are using the same connection.

On the surface, it seems like reusing the connection would result in quicker responses but I don't know the nitty gritty of what's going on in a SqlConnection object so I'm not sure if it's a good idea.

Answer 1

The code is misleading. You're not actually creating a new connection when you do this in most circumstances. By default pooling is enabled for a connection unless you explicitly tell it not to.

I'm not 100% certain of the exact criteria behind pooling, but the gist of it is that if you reuse the same connection string rapidly in your code, pooling should only actually create one connection from your app to SQL Server, instead of creating a new connection every time you do new SqlConnection and open it.

By disposing of the connection, you're basically signaling that that particular usage is finished....so the logic behind maintaining the pool can know that you're done with that particular connection. Again I don't know exactly how pooling is implemented, but I imagine it's internally keeping track of how many connections from your code are made so it can decide whether to keep an actual connection to SQL Server open or not.