C# 使用连接池时如何强制 SqlConnection 物理关闭？

How to force a SqlConnection to physically close, while using connection pooling?

提问by Jeff Meatball Yang

I understand that if I instantiate a SqlConnection object, I am really grabbing a connection from a connection pool. When I call Open(), it will open the connection. If I call the Close() or Dispose() method on that SqlConnection object, it is returned to the connection pool.

我知道如果我实例化一个 SqlConnection 对象，我实际上是从连接池中获取连接。当我调用 Open() 时，它将打开连接。如果我对该 SqlConnection 对象调用 Close() 或 Dispose() 方法，它将返回到连接池。

However, that doesn't really tell me if it's really closed, or if I still have an active connection to the database.

但是，这并不能真正告诉我它是否真的关闭了，或者我是否仍然有与数据库的活动连接。

How can I force a SqlConnection to close at the network level, or at least tell when it closes?

如何强制 SqlConnection 在网络级别关闭，或者至少告诉它何时关闭？

Example:

例子：

using(SqlConnection conn = new SqlConnection(DBConnString)) {

   conn.Open();
   SqlCommand cmd = conn.CreateCommand();
   ...
   cmd.ExecuteReader(CommandBehavior.CloseConnection);
   ...
}

• First run: 300 ms
• Second run: 100 ms
• Third run: 100 ms
• After waiting a long time (30 minutes): 300 ms

• 首次运行：300 毫秒
• 第二次运行：100 毫秒
• 第三次运行：100 毫秒
• 等待很长时间（30 分钟）后：300 毫秒

If the connection was TRULY closing, the second and third runs should also be 300 ms. But I know that the connection is not truly closed for those runs (I checked the SQL Server's activity monitor). It doesn't take the extra 200ms to perform authentication/etc.

如果连接真正关闭，第二次和第三次运行也应该是 300 毫秒。但我知道这些运行并没有真正关闭连接（我检查了 SQL Server 的活动监视器）。执行身份验证/等不需要额外的 200 毫秒。

How do I force the connection to truly close?

如何强制连接真正关闭？

Ideas

想法

• Does CommandBehavior.CloseConnection work? (apparently not?)
• Does setting "Max Pool Size = 0" in the connection string work? (this would be a pyrrhic solution)
• Does Dispose() work?

• CommandBehavior.CloseConnection 工作吗？（显然不是？）
• 在连接字符串中设置“Max Pool Size = 0”是否有效？（这将是一种有害的解决方案）
• Dispose() 有效吗？

References

参考

• Article on Connection Pooling
• Here's another one that tells us that Close() doesn't really closethe connection.
• An article on pros and cons connection pooling

• 关于连接池的文章
• 这是另一个告诉我们Close() 并没有真正关闭连接。
• 一篇关于优缺点连接池的文章

采纳答案by Moe Sisko

Maybe SqlConnection.ClearPool?

也许SqlConnection.ClearPool？

回答by Cerebrus

CommandBehavior.CloseConnectionis usually discouraged because of this very fact - You can't be sure that the Connection will be closed. (I'll try to find some concrete evidence of this, I'm saying this from faint recall).

CommandBehavior.CloseConnection由于这个事实，通常不鼓励 - 您无法确定连接将关闭。（我会试着找到一些具体的证据，我是从模糊的回忆中说出来的）。

Dispose()is the surestway because it implicitly calls Close().

Dispose()是最可靠的方法，因为它隐式调用Close().

The usingconstruct demonstrated by @Alex is just another (programmer friendly) way of writing the try-finallyconstruct with the added implicit Disposal of objects.

using@Alex 演示的构造只是另一种（程序员友好的）try-finally使用添加的隐式对象处置来编写构造的方式。

Edit: (after edit to question)

编辑：（编辑问题后）

Your concern over the connections actuallyclosing seems unwarranted to me. The connection would simply return to the pool so that it can be reused easily without having to go through all the initialization. This does not mean that the Connection is still actively connected to the DB.

您对实际关闭的连接的担忧在我看来是没有根据的。该连接将简单地返回到池中，以便可以轻松地重用它，而无需进行所有初始化。这并不意味着 Connection 仍然主动连接到 DB。

回答by ZippyV

If you don't want to use the connection pool you have to specify it in your SqlConnection.ConnectionStringproperty. For example

如果您不想使用连接池，则必须在您的SqlConnection.ConnectionString属性中指定它。例如

"Data Source=MSSQL1;Database=AdventureWorks;Integrated Security=true;Pooling=false;"

Disposing or closing the SqlConnectionobject is just going to close the connection and return it to the connection pool.

处理或关闭SqlConnection对象只是关闭连接并将其返回到连接池。

回答by Eamon Nerbonne

Generally, you want the connection pool to do its job - you don't want the connection to truly close.

通常，您希望连接池完成它的工作 - 您不希望连接真正关闭。

Why specifically do you want the connection not to return to the pool?

为什么特别希望连接不返回到池中？

回答by Robert Calhoun

Moe Sisko's answer (Call SqlConnection.ClearPool) is correct.

Moe Sisko 的回答 (Call SqlConnection.ClearPool) 是正确的。

Sometimes you need a connection to really close rather than return to the pool. As an example, I have a unit test that creates a scratch database, builds the schema, tests some stuff, then drops the scratch database if the tests all pass.

有时您需要一个连接来真正关闭而不是返回到池中。例如，我有一个单元测试，它创建一个临时数据库，构建模式，测试一些东西，然后如果测试全部通过则删除临时数据库。

When connection pooling is active, the drop database command fails because there are still active connections. From the point of view of programmer all SQLConnections are closed, but as the pool still holds one open, SQL Server won't allow the drop.

当连接池处于活动状态时，drop database 命令将失败，因为仍有活动连接。从程序员的角度来看，所有 SQLConnections 都是关闭的，但由于池中仍然保持一个打开状态，SQL Server 不允许删除。

The best documentation for how connection pooling is handled is this page on SQL Server Connection Poolingon MSDN. One doesn't want to turn connection pooling off entirely because it improves performance with repeated opens and closes, but sometimes you need to call a "force close" on an SQLConnection so that it will let go of the database.

有关如何处理连接池的最佳文档是MSDN上 SQL Server 连接池上的此页面。人们不想完全关闭连接池，因为它通过重复打开和关闭来提高性能，但有时您需要对 SQLConnection 调用“强制关闭”，以便它释放数据库。

This is done with ClearPool. If you call SqlConnection.ClearPool(connection)before closing/disposing, when you do close/dispose it will really go away.

这是通过 ClearPool 完成的。如果您SqlConnection.ClearPool(connection)在关闭/处理之前调用，当您关闭/处理它时，它真的会消失。

回答by David Rowley

Robert's answer of SqlConnection.ClearPool(TheSqlConn)did exactly what I wanted. It's nice to know the pool CAN be interacted with when necessary.

罗伯特的回答SqlConnection.ClearPool(TheSqlConn)正是我想要的。很高兴知道在必要时可以与池进行交互。

My use case was: We have ruined a connection and let it go back into the pool, how to we detect that it's ruined and refresh it, so the next user won't have problems.

我的用例是：我们破坏了一个连接并让它回到池中，我们如何检测它已破坏并刷新它，这样下一个用户就不会遇到问题。

The solution was: Detect that we have just ruined the connection, and clear it from the pool entirely, letting the pool fill back up with fresh connections.

解决方案是：检测我们刚刚破坏了连接，并将其从池中完全清除，让池充满新的连接。

A decade of writing SqlClient.SqlConnection and I never even thought of interacting with the pool till today.

编写 SqlClient.SqlConnection 十年，直到今天我什至从未想过与池交互。

回答by user939857

I see that you are using .net but as this showed up in a google query allow me to give a java response...

我看到您正在使用 .net 但正如这在 google 查询中显示的那样，允许我给出 java 响应...

Use a DataSource that implements Closeable() and call close on the DataSource. Hikari supports Closeable.

使用实现 Closeable() 的 DataSource 并在 DataSource 上调用 close。Hikari 支持 Closeable。