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:
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.
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.