C#, Dapper, SQL Server and Connection Pooling

Question

C#, Dapper, SQL Server and Connection Pooling

c# connection-pooling dapper sql-server

Question

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.

Phil S

View more on Stack Overflow

Phil S · Accepted Answer · 5/10/2017 9:49:59 AM

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.

C#, Dapper, SQL Server and Connection Pooling

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator