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.
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.
In case it wasn't explicitly clear, you should leave pooling enabled, and let the framework take care of the underlying connection for you, making sure you appropriately signal when you're done by wrapping your connections in a using statement or block. Disabling pooling should only be done in more advanced scenarios when you need explicit control over the underlying connection.
One advanced scenario where I disable pooling is that I have a deployment utility that loops through over 20,000 databases on our SQL server and applies migration .sql files to update each database to our latest specifications. I know that once I update a particular database, I'm done with that connection and don't need to reconnect to the same DB, so I have pooling disabled for this particular utility.