C #, Dapper, SQL Server and Connection Pooling

Question

I wrote code to write some data to SQL Server using Dapper. I don’t have to wait for this entry to finish before continuing with another job, so you want to use Task.Run () to make it asynchronous.

I have (using) statements to call this in the rest of my system:

using (IDataAccess ida = new DAL()) { ida.WriteMessageToDB(id, routingKey, msgBody); } 

My DAL automatically checks dbConnection.State when the using statement is run and tries to perform a simple fix if it is closed. This is great for any non-asynchronous / TPL calls.

However, when I dump the write load at the same time, the Task.Run () code crashed because the connection was closed for some of them - in fact, I think that the parallel nature of the code means that the state was closed by other tasks.

I “fixed” this by checking to open Connection.State in the Task.Run () code, and this seems to have “solved” the problem. For instance:

 Task.Run(() => { if (dbConnection.State == ConnectionState.Closed) { dbConnection.Open(); } if (dbConnection.State == ConnectionState.Open) { *Dapper SQL String and Execute Commands* } }); 

When I run SELECT * FROM sys.dm_exec_connections from SSMS after that, I see a lot more connections. Waiting for?

Now that I understand this:

• Dapper does not deal with connection pool
• Should SQL Server automatically handle connection pooling?

Is there something wrong with this solution? Or is the best way to do this? I would like to use the connection pool for obvious reasons and without pain.

Thanks in advance.

Answer 1

Thanks, Juharr. I answered your answer.

For references to others, I changed the recording function to wait, and Dapper async:

 private async Task WriteMessageToDB(Guid id, string tableName, string jsonString) { string sql = *Redacted* await dbConnection.ExecuteScalarAsync<int>(sql, new { ID = id, Body = jsonString }); } 

And then created a new task in the calling object that controls the outcome.

This constantly works under load and also does not create excessive new connections.