C＃，Dapper，SQL Server和连接池 [英] C#, Dapper, SQL Server and Connection Pooling

问题描述

我已经编写了一些代码，以使用Dapper将一些数据写入SQL Server。在继续其他工作之前，我不需要等待此写操作完成，因此想使用Task.Run（）使其异步。

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);
        }

在运行using语句时，我的DAL将自动检查dbConnection.State，并尝试简单的修复（如果已关闭）。

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.

但是，当我同时抛出大量写操作时，Task.Run（）代码会很好地工作。由于其中一些连接已关闭，因此崩溃了-本质上，我认为代码的并行性意味着该状态已被其他任务关闭。

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.

我已修复通过检查以打开Task.Run（）代码中的Connection.State来执行此操作，这似乎已解决了问题。像这样：

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*
                }
            });

当我运行 SELECT * FROM sys.dm_exec_connections 从SSMS之后，我看到了更多的连接。

When I run SELECT * FROM sys.dm_exec_connections from SSMS after this, I see a lot more connections. To be expected?

据我所知：

• Dapper没有要处理连接池


• SQL Server应该自动处理连接池吗？

这个解决方案有什么问题吗？还是更好的方法呢？出于明显的原因，我想使用连接池，并且要尽可能地轻松。

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.

预先感谢。

推荐答案

感谢Juharr-我对您的答复表示赞成。

Thanks Juharr - I've upvoted your reply.

为了便于参考，我将write函数更改为await和Dapper异步：

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和连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！