C# .NET SqlConnection 类、连接池和重连逻辑
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1058591/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
.NET SqlConnection class, connection pooling and reconnection logic
提问by Paul Hollingsworth
We have some client code which is using the SqlConnection class in .NET to talk to a SQLServer database. It is intermittently failing with this error:
我们有一些客户端代码使用 .NET 中的 SqlConnection 类与 SQLServer 数据库对话。它间歇性地失败并出现此错误：
"ExecuteReader requires an open and available Connection. The connection's current state is Closed"
“ExecuteReader 需要一个打开且可用的连接。连接的当前状态为关闭”
The "temporary" solution is to reboot the process, after which everything works - however, that's obviously unsatisfactory.
“临时”解决方案是重新启动进程，然后一切正常——但是，这显然不能令人满意。
The code is keeping a cache of SqlConnection instances, one for each database.
该代码保留了 SqlConnection 实例的缓存，每个数据库一个。
We'd like to re-write the code, but before I do, I need to know a few things:
我们想重新编写代码，但在我这样做之前，我需要知道一些事情：
My first question is: Is it inefficient to repeatedly connect and disconnect SqlConnection objects, or does the underlying library perform connection pooling on our behalf?
我的第一个问题是：重复连接和断开 SqlConnection 对象效率低下，还是底层库代表我们执行连接池？
// Is this bad/inefficient?
for(many-times)
{
using(SQLConnection conn = new SQLConnection(connectionString))
{
// do stuff with conn
}
}
Because our code does notdo the above, what seems the likely cause of the problem is that something happens to the underlying SQLServer database during the "lifetime" of the connection that causes the connection to be closed...
因为我们的代码没有执行上述操作，问题的可能原因似乎是在连接的“生命周期”期间底层 SQLServer 数据库发生了一些事情，导致连接被关闭......
If it turns out that it is worthwile to "cache" SqlConnection objects, what is the recommended way to handle all errors that could be resolved simply by "reconnecting" to the database. I'm talking about scenarios such as:
如果事实证明“缓存”SqlConnection 对象是值得的，那么推荐的处理所有错误的方法是什么，这些错误可以通过“重新连接”到数据库来解决。我说的是这样的场景：
- The database is taken offline and brought back online, but the client process had no open transactions while this was happening
- The database was "disconnected", then "reconnected"
- 数据库脱机并重新联机，但是在发生这种情况时客户端进程没有打开的事务
- 数据库被“断开”，然后“重新连接”
I notice that there is a "State" property on SqlConnection... is there an appropriate way to query that?
我注意到 SqlConnection 上有一个“状态”属性......有没有合适的方法来查询它？
Finally, I have a test SQLServer instance set up with full access rights: how can I go about reproducing the exact error "ExecuteReader requires an open and available Connection. The connection's current state is Closed"
最后，我有一个设置了完全访问权限的测试 SQLServer 实例：如何重现确切的错误“ExecuteReader 需要一个打开且可用的连接。连接的当前状态为关闭”
采纳答案by Jon Skeet
No, it's not inefficient to create lots of SqlConnectionobjects and close each of them when you're done. That's exactly the right thing to do. Let the .NET framework connection pooling do its job - don't try to do it yourself. You don't need to do anything specific to enable connection pooling (although you can disable it by setting Pooling=falsein your connection string).
不，创建大量SqlConnection对象并在完成后关闭每个对象并不是低效的。这才是正确的做法。让 .NET 框架连接池完成它的工作 - 不要尝试自己做。您无需执行任何特定操作即可启用连接池（尽管您可以通过Pooling=false在连接字符串中进行设置来禁用它）。
There are many things that could go wrong if you try to cache the connection yourself. Just say no :)
如果您尝试自己缓存连接，有很多事情可能会出错。拒绝吧 ：）
回答by Remus Rusanu
You should enable connection poolingon your connection string. In that case the runtime will add back your connections to the 'pool' when you close them, instead of really disconencting. When a 'new' connection is taken out of the pool it will be reset (ie. sp_reset_connection is called ) then presented to your application as a brand new, fresh connection. The pool is handling transparently such cases as if the connection is closed while idling in the pool.
您应该在连接字符串上启用连接池。在这种情况下，当您关闭它们时，运行时会将您的连接添加回“池”，而不是真正断开连接。当从池中取出一个“新”连接时，它将被重置（即 sp_reset_connection 被调用），然后作为一个全新的、全新的连接呈现给您的应用程序。池正在透明地处理这种情况，例如在池中空闲时连接已关闭。
The cost of creating a new connection 'from scratch' is significant because the authentication requires several roundtrips between client and server (depending on the authentication method and on SSL settings it can be 1 roundtrip in best case vs. about 10 in worse).
“从头开始”创建新连接的成本非常高，因为身份验证需要在客户端和服务器之间进行多次往返（取决于身份验证方法和 SSL 设置，最好的情况下可以是 1 次往返，而更糟的是大约 10 次）。
And to answer your question, connection raise the OnStateChangeevent when their state changes, but you shouldn't care about this if you use the pooling.
为了回答您的问题，连接会在状态更改时引发OnStateChange事件，但是如果您使用池化，则不必关心这一点。
回答by Paul
In my recent experience if you use this code:
根据我最近的经验，如果您使用此代码：
using(SQLConnection conn = new SQLConnection(connectionString))
{
// do stuff with conn
}
have an error, and do not explicitly close the connection, it will not be closed or checked back into the pool. So use a catch or finally block to close the connection
有错误，并且不显式关闭连接，它不会被关闭或检回到池中。所以使用 catch 或 finally 块来关闭连接
