为什么我的 using 语句没有关闭连接?

可能重复:
实体框架 4 未在 sql server 2005 中关闭连接分析器

好吧,stackoverflow 上的许多开发人员都说我不应该担心关闭我的连接:我的 using 语句将为我关闭连接,这里和这里和整个网站.不幸的是,我没有看到它发生.这是我的代码:

Well, lots of developers on stackoverflow are saying that I should not worry to close my connection: my using statement will close the connection for me, here and here and all over the site. Unfortunately, I do not see it happening. Here is my code:

    [Test, Explicit]
    public void ReconnectTest()
    {
        const string connString = "Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;Application Name=ReconnectTest;";
        for (int i = 0; i < 2000; i++)
        {
            try
            {
                using (var conn = new SqlConnection(connString))
                {
                    conn.Open();
                    using (var command = conn.CreateCommand())
                    {
                        command.CommandText = "SELECT 1 as a";
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
                    //conn.Close();
// optional breakpoint 1 below
                    }
                }
                catch(SqlException e)
                {
// breakpoint 2 below
                    Console.WriteLine(e);
                }
// breakpoint 3 below
            }
        }

当我启用所有断点并开始测试时,第一次迭代成功,我遇到了断点 3.此时,连接仍然打开:我在 Profiler 中看到它,并且 sp_who2 也输出了它.

When I enable all breakpoints and start my test, the first iteration succeeds, and I hit breakpoint 3. At this point, the connection is still open: I see it in the Profiler, and sp_who2 outputs it too.

假设此时我出去吃午饭,而我的连接处于空闲状态.因此,我们的生产服务器将其杀死.为了模仿它,我正在终止来自 SSMS 的连接.

Let's suppose that at this time I am out for a lunch, and my connection is idle. As such, our production server kills it. To imitate it, I am killing the connection from SSMS.

所以,当我按 F5 并运行第二次迭代时,我的连接被终止.不幸的是,它不会自动重新打开,因此 ExecuteNonQuery 会引发以下异常:发生传输级错误".当我运行第三次迭代时,我的连接实际上打开了:我将其视为 Profiler 中的一个事件,并且 sp_who2 也将其输出.

So, when I hit F5 and run the second iteration, my connection is killed. Unfortunately, it does not reopen automatically, so ExecuteNonQuery throws the following exception: "transport-level error has occurred". When I run the third iteration, my connection actually opens: I see it as an event in Profiler, and sp_who2 outputs it as well.

即使我取消了 conn.Close() 命令的注释,连接仍然没有关闭,当我从 SSMS 中终止它时,下一次迭代仍然失败.

Even when I have uncommented my conn.Close() command, the connection still does not close, and when I kill it from SSMS, the next iteration still blows up.

我错过了什么?为什么不能使用语句关闭我的连接?为什么第一次 Open() 打不开,下一次就成功了?

What am I missing? Why can't using statement close my connection? Why can't Open() actually open it the first time, but succeeds the next time?

这个问题来自我以前的一个

推荐答案

当你调用 SqlConnection.Dispose() 时,由于 using 块,连接本身没有关闭.它被释放回连接池.

When you call SqlConnection.Dispose(), which you do because of the using block, the connection is not closed, per-se. It is released back to the connection pool.

为了避免不断地建立/拆除连接,连接池将保持连接打开以供您的应用程序使用.因此,连接仍会显示为打开状态是完全合理的.

In order to avoid constantly building/tearing down connections, the connection pool will keep connections open for your application to use. So it makes perfect sense that the connection would still show as being open.

之后发生了什么,我无法直接解释 - 我知道保持随机连接打开不会导致这种情况,因为您的应用程序当然可以建立多个并发连接.

What's happening after that, I can't explain offhand - I know that keeping a random connection open would not cause that, though, because your application can certainly make more than a single concurrent connection.

相关文章