.NET SqlConnection类，连接池和重新连接逻辑 [英] .NET SqlConnection class, connection pooling and reconnection logic

问题描述

我们拥有它是使用SqlConnection类在.NET跟一个SQLServer数据库的一些客户端code。这是间歇性故障与此错误：

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:

ExecuteReader需要一个开放和可用的连接。该连接的当前状态为关闭

"ExecuteReader requires an open and available Connection. The connection's current state is Closed"

在临时的解决办法是重新启动的过程中，在这之后一切正常 - 不过，这显然不能令人满意。

The "temporary" solution is to reboot the process, after which everything works - however, that's obviously unsatisfactory.

在code是保持SqlConnection的实例为每个数据库高速缓存的企业之一。

The code is keeping a cache of SqlConnection instances, one for each database.

我们想重新写了code，但在此之前我做什么，我需要知道的几件事情：

We'd like to re-write the code, but before I do, I need to know a few things:

我的第一个问题是：它是低效率的反复连接和断开的SqlConnection对象，还是底层库进行连接池代表我们

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?

// Is this bad/inefficient?
for(many-times)
{
    using(SQLConnection conn = new SQLConnection(connectionString))
    {
        // do stuff with conn
    }
}

由于我们的code做的没有的做到上面，似乎什么问题的可能的原因是，有事到底层SQLServer数据库中的生命周期的连接引起的连接被关闭...

Because our code does not do 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...

如果事实证明，这是欢颜以缓存的SqlConnection对象，什么是推荐的方式来处理，可以简单地通过重新连接的数据库来解决所有的错误。我说的方案，如：

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的一个国家的属性...有查询，一个适当的方式？

I notice that there is a "State" property on SqlConnection... is there an appropriate way to query that?

最后，我有一个测试的SQLServer实例设置了完全访问权限：我怎么能去有关复制确切的错误ExecuteReader需要一个开放和可用的连接连接的当前状态为已关闭。

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"

推荐答案

没有，这不是低效率的创建大量的的SqlConnection 对象，并关闭他们每个人，当你完成。这是完全正确的事情。让.NET框架的连接池做的工作 - 不要尝试自己做。你并不需要做具体的启用连接池（不过你可以通过设置将其禁用池=假在连接字符串）。

No, it's not inefficient to create lots of SqlConnection objects 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=false in your connection string).

有很多事情，如果你尝试自己缓存的连接，可能出错。说不：）

There are many things that could go wrong if you try to cache the connection yourself. Just say no :)

这篇关于.NET SqlConnection类，连接池和重新连接逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！