即使在 using { } 中,.net SqlConnection 也不会关闭
请帮忙!
背景信息
我有一个访问 SQL Server 2005 数据库的 WPF 应用程序。 数据库在运行应用程序的计算机上本地运行。
在我使用 Linq DataContext 的任何地方,我都使用 using { } 语句,并传入一个函数的结果,该函数返回一个 SqlConnection 对象,该对象已打开,并在返回 DataContext 构造函数之前使用它执行了 SqlCommand 。即
// In the application code
using (DataContext db = new DataContext(GetConnection()))
{
... Code
}
getConnection 的外观像这样(我已经从函数中删除了“绒毛”,以使其更具可读性,但没有缺少任何其他功能)。
// Function which gets an opened connection which is given back to the DataContext constructor
public static System.Data.SqlClient.SqlConnection GetConnection()
{
System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection(/* The connection string */);
if ( Conn != null )
{
try
{
Conn.Open();
}
catch (System.Data.SqlClient.SqlException SDSCSEx)
{
/* Error Handling */
}
using (System.Data.SqlClient.SqlCommand SetCmd = new System.Data.SqlClient.SqlCommand())
{
SetCmd.Connection = Conn;
SetCmd.CommandType = System.Data.CommandType.Text;
string CurrentUserID = System.String.Empty;
SetCmd.CommandText = "DECLARE @B VARBINARY(36); SET @B = CAST('" + CurrentUserID + "' AS VARBINARY(36)); SET CONTEXT_INFO @B";
try
{
SetCmd.ExecuteNonQuery();
}
catch (System.Exception)
{
/* Error Handling */
}
}
return Conn;
}
我不认为应用程序是 WPF 应用程序对我遇到的问题有任何影响。
我遇到的问题
尽管 SqlConnection 与 DataContext 一起被处理Sql Server Management studio 我仍然可以看到大量打开的连接:
status : 'Sleeping'
command : 'AWAITING COMMAND'
last SQL Transact Command Batch : DECLARE @B VARBINARY(36); SET @B = CAST('GUID' AS VARBINARY(36)); SET CONTEXT_INFO @B
最终连接池用完,应用程序无法继续。
所以我只能得出结论,以某种方式运行 SQLCommand 来设置 Context_Info 意味着当 DataContext 被释放时,连接不会被释放。
当连接所使用的 DataContext 被处置时,任何人都可以发现任何明显的东西会阻止连接被关闭和处置吗?
Please help!
Background info
I have a WPF application which accesses a SQL Server 2005 database. The database is running locally on the machine the application is running on.
Everywhere I use the Linq DataContext I use a using { } statement, and pass in a result of a function which returns a SqlConnection object which has been opened and had an SqlCommand executed using it before returning to the DataContext constructor.. I.e.
// In the application code
using (DataContext db = new DataContext(GetConnection()))
{
... Code
}
where getConnection looks like this (I've stripped out the 'fluff' from the function to make it more readable, but there is no additional functionality that is missing).
// Function which gets an opened connection which is given back to the DataContext constructor
public static System.Data.SqlClient.SqlConnection GetConnection()
{
System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection(/* The connection string */);
if ( Conn != null )
{
try
{
Conn.Open();
}
catch (System.Data.SqlClient.SqlException SDSCSEx)
{
/* Error Handling */
}
using (System.Data.SqlClient.SqlCommand SetCmd = new System.Data.SqlClient.SqlCommand())
{
SetCmd.Connection = Conn;
SetCmd.CommandType = System.Data.CommandType.Text;
string CurrentUserID = System.String.Empty;
SetCmd.CommandText = "DECLARE @B VARBINARY(36); SET @B = CAST('" + CurrentUserID + "' AS VARBINARY(36)); SET CONTEXT_INFO @B";
try
{
SetCmd.ExecuteNonQuery();
}
catch (System.Exception)
{
/* Error Handling */
}
}
return Conn;
}
I do not think that the application being a WPF one has any bearing on the issue I am having.
The issue I am having
Despite the SqlConnection being disposed along with the DataContext in Sql Server Management studio I can still see loads of open connections with :
status : 'Sleeping'
command : 'AWAITING COMMAND'
last SQL Transact Command Batch : DECLARE @B VARBINARY(36); SET @B = CAST('GUID' AS VARBINARY(36)); SET CONTEXT_INFO @B
Eventually the connection pool gets used up and the application can't continue.
So I can only conclude that somehow running the SQLCommand to set the Context_Info is meaning that the connection doesn't get disposed of when the DataContext gets disposed.
Can anyone spot anything obvious that would be stopping the connections from being closed and disposed of when the DataContext they are used by are disposed?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
来自 MSDN (
DataContext 构造函数 (IDbConnection)
):所以基本上,看起来你的连接正在等待 GC 完成它们,然后才会被释放。 如果您有大量执行此操作的代码,一种方法可能是覆盖数据上下文的分部类中的
Dispose()
,并关闭连接 - 只需确保记录数据上下文假设连接的所有权!就我个人而言,只要我“使用”连接(允许我执行多个操作),我很乐意给它(常规数据上下文,没有上面的黑客)一个开放的连接 - 即
From MSDN (
DataContext Constructor (IDbConnection)
):So basically, it looks like your connections are waiting for GC to finalize them before they will be released. If you have lots of code that does this, one approach might be to overide
Dispose()
in the data-context's partial class, and close the connection - just be sure to document that the data-context assumes ownership of the connection!Personally, I would happily give it (regular data-context, w/o the hack above) an open connection as long as I was "using" the connection (allowing me to perform multiple operations) - i.e.
LINQ
DataContext
使用的SqlProvider
仅在打开 SQL 连接时关闭 SQL 连接(通过SqlConnectionManager.DisposeConnection
)。 如果您将已经打开的SqlConnection
对象提供给DataContext
构造函数,它不会为您关闭它。 因此,你应该写:The
SqlProvider
used by the LINQDataContext
only closes the SQL connection (throughSqlConnectionManager.DisposeConnection
) if it was the one to open it. If you give an already-openSqlConnection
object to theDataContext
constructor, it will not close it for you. Thus, you should write:我使用实体框架遇到了同样的问题。 我的
ObjectContext
包裹在using
块中。当我调用
SaveChanges()
时建立了连接,但在using
语句超出范围后,我注意到 SQL Management Studio 仍然有一个“AWAITING COMMAND” “
用于 .NET SQL 客户端。看起来这与默认情况下打开连接池的 ADO.NET 提供程序的行为有关。
来自 MSDN(强调我的):
此外
ClearAllPools
和ClearPool
似乎很有用如果需要,显式关闭所有池连接。I experienced the same issue using the Entity Framework. My
ObjectContext
was wrapped around ausing
block.A connection was established when I called
SaveChanges()
, but after theusing
statement was out of scope, I noticed that SQL Management Studio still had a"AWAITING COMMAND"
for the .NET SQL Client.It looks like this has to do with the behavior of the ADO.NET provider which has connection pooling turned on by default.
From "Using Connection Pooling with SQL Server" on MSDN (emphasis mine):
Also
ClearAllPools
andClearPool
seems useful to explicitly close all pooled connections if needed.我认为该连接虽然不再被引用,但正在等待 GC 完全处理它。
解决方案:
创建您自己的 DataContext 类,该类派生自自动生成的 DataContext 类。 (重命名基本代码,这样您就不必更改任何其他代码)。
在派生的 DataContext 中 - 添加 Dispose() 函数。 在那里 - 处理内部连接。
I think the connection, while no longer referenced, is waiting for the GC to dispose of it fully.
Solution:
Create your own DataContext class which derives from the auto-generated one. (rename the base one so you don't have to change any other code).
In your derived DataContext - add a Dispose() function. In that - dispose the inner connection.
好吧,感谢您的帮助,现在已经解决了。
本质上,我采用了上面大部分答案的元素,并实现了上面的 DataContext 构造函数(我已经重载了构造函数,所以这不是一个大的变化)。
这样做而不是上面的一些建议的原因是在 dispose 方法中访问
this.Connection
会抛出 ObjectDispositedException。上面的效果正如我所希望的那样!
Well thanks for the help chaps, it has been solved now..
Essentially I took elements of most of the answers above and implemented the DataContext constructor as above (I already had overloaded the constructors so it wasn't a big change).
The reason for doing this rather than some of the suggestions above is that accessing
this.Connection
in the dispose method throws a ObjectDisposedException.And the above works as well as I was hoping!
Dispose
应关闭连接,如 MSDN指出:我的猜测是您的问题与
GetContext()
有关。The
Dispose
should close the connections, as MSDN points out:My guess would be that your problem has something to do with
GetContext()
.