Dapper.NET Connection/Query Best Practice

.net asp.net c# dapper sql-server

Question

So i've read a bunch of links/SO questions, but i still can't get a clear answer on this.

When performing SQL queries with Dapper in an ASP.NET application, what is the best practice for opening/closing connections?

Here's the pattern i'm currently following:

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

Essentially, open/close a SQL connection each time as needed.

From my understanding, the above code should automatically open/close the SQL connection (e.g i don't need to explicity do db.Open or db.Close).

The problem i'm seeing is that after a while, i'm getting a bunch of these errors:

InvalidOperationExceptionTimeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I've had a SQL profiler trace during this time period, and i'm not seeing any long queries that would block anything, so it appears as though my ASP.NET web application is running out of connections (as opposed to taking too long to execute a query).

Can someone tell me what i'm doing wrong?

A side note: my application is running as an Azure Web App, so unfortunately i can't actually see how many connections are being opened by the web app. :(

Popular Answer

From my opinion using statement closing your connection but you tell explicity open a connection something like this:

using (var db = new SqlConnection(_connectionString))

{
       db.open();

    return db.Query("dbo.SomeQuery");
}


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