C#, Dapper, SQL Server and Connection Pooling

c#, connection-pooling, dapper, sql-server

Solution

Thanks Juharr - I've upvoted your reply.

For reference to others, I changed write function to await 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 caller that monitors the outcome.

This is working consistently under load, and not seeing excessive new connections being created either.

Problem

I've written some code to write some data to SQL Server, using Dapper. I don't need to wait for this write to complete before continuing other work, so want to use Task.Run() to make this asynchronous. I have (using) statements for calling this in the rest of my system: ``` using (IDataAccess ida = new DAL()) { ida.WriteMessageToDB(id, routingKey, msgBody); } ``` My DAL will automatically check the dbConnection.State when the using statement is ran, and attempt a simple fix if it's closed. This works just fine for any non-async/TPL select calls. However, when I throw a load of writes at the same time, the Task.Run() code was falling over as the connection was closed for some of them - essentially I think the parallel nature of the code meant the state was being closed by other tasks. I 'fixed' this by doing a check to open the Connection.State within the Task.Run() code, and this appears to have 'solved' the problem. Like so: ``` 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 this, I see a lot more connections. To be expected? Now as I understand it: - Dapper doesn't deal with connection pooling - SQL Server should automatically deal with connection pooling? Is there anything wrong with this solution? Or a better way of doing it? I'd like to use connection pooling for obvious reasons, and as painlessly as possible. Thanks in advance.

Original source