Best practices for connecting / querying Dapper.NET

Question

So, I read a bunch of links / questions, but I still can't get a clear answer to this.

When executing SQL queries with Dapper in an ASP.NET application, what is the best way to open / close connections?

Here is the template that I am currently executing:

using (var db = new SqlConnection(_connectionString)) { return db.Query("dbo.SomeQuery"); } 

Essentially, open / close the SQL connection every time you need it.

From my understanding, the above code should automatically open / close the SQL connection (for example, I don't need to explain db.Open or db.Close ).

The problem that I see is that after a while I get a bunch of these errors:

Error InvalidOperationExceptionTimeout. The elapsed time to wait for a connection from the pool. Perhaps this happened because all joined connections were used and the maximum pool size was reached.

During this period of time, I had a trace of the SQL profiler and I did not see any long queries that would block anything, so it seems that my ASP.NET web application is ending up with connections (as opposed to taking too much time request).

Can someone tell me what I'm doing wrong?

Note: my application works like an Azure Web App, so unfortunately I can’t see how many connections the web application opens. :(

Answer 1

From my point of view, using the operator that closes your connection, but you say that the explanation opens the connection is something like this:

 using (var db = new SqlConnection(_connectionString)) { db.open(); return db.Query("dbo.SomeQuery"); }