.net SqlCommand.Dispose 是否关闭连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/60919/
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
Does SqlCommand.Dispose close the connection?
提问by Andrei R?nea
Can I use this approach efficiently?
我可以有效地使用这种方法吗？
using(SqlCommand cmd = new SqlCommand("GetSomething", new SqlConnection(Config.ConnectionString))
{
cmd.Connection.Open();
// set up parameters and CommandType to StoredProcedure etc. etc.
cmd.ExecuteNonQuery();
}
My concern is : Will the Dispose method of the SqlCommand (which is called when exiting the using block) close the underlying SqlConnection object or not?
我担心的是：SqlCommand 的 Dispose 方法（在退出 using 块时调用）是否会关闭底层 SqlConnection 对象？
回答by Ryan Farley
No, Disposing of the SqlCommandwill not effect the Connection. A better approach would be to also wrap the SqlConnectionin a using block as well:
不，处理SqlCommand不会影响连接。更好的方法是也将 包装SqlConnection在 using 块中：
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(cmdstring, conn))
{
cmd.ExecuteNonQuery();
}
}
Otherwise, the Connection is unchanged by the fact that a Command that was using it was disposed (maybe that is what you want?). But keep in mind, that a Connection should be disposed of as well, and likely more important to dispose of than a command.
否则，连接不会因为使用它的命令被处理而改变（也许这就是你想要的？）。但请记住，连接也应该被处理，并且处理可能比命令更重要。
EDIT:
编辑：
I just tested this:
我刚刚测试了这个：
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
using (SqlCommand cmd = new SqlCommand("select field from table where fieldid = 1", conn))
{
Console.WriteLine(cmd.ExecuteScalar().ToString());
}
using (SqlCommand cmd = new SqlCommand("select field from table where fieldid = 2", conn))
{
Console.WriteLine(cmd.ExecuteScalar().ToString());
}
conn.Dispose();
The first command was disposed when the using block was exited. The connection was still open and good for the second command.
第一个命令是在退出 using 块时处理的。连接仍然打开并且对第二个命令很好。
So, disposing of the command definitely does not dispose of the connection it was using.
因此，处理命令绝对不会处理它正在使用的连接。
回答by user26320
SqlCommand.Dispose will not be sufficient because many SqlCommand(s) can (re)use the same SqlConnection. Center your focus on the SqlConnection.
SqlCommand.Dispose 是不够的，因为许多 SqlCommand(s) 可以（重新）使用相同的 SqlConnection。将您的注意力集中在 SqlConnection 上。
回答by Keith Patrick
Soooo many places get this wrong, even MS' own documentation. Just remember - in DB world, almost everythingis backed by an unmanaged resource, so almost everything implements IDisposable. Assume a class does unless the compiler tells you otherwise. Wrap your command in a using. Wrap your connectionin a using. Create your connection off a DbProvider (get that from DbProviderFactories.GetFactory), and your command off your connection so that if you change your underlying DB, you only need to change the call to DBPF.GetFactory. So your code should end up looking nice and symmetrical:
很多地方都弄错了，甚至是 MS 自己的文档。请记住 - 在数据库世界中，几乎所有内容都由非托管资源支持，因此几乎所有内容都实现了 IDisposable。假设一个类可以，除非编译器另有说明。将您的命令包装在 using 中。将您的连接包装在 using 中。从 DbProvider 创建连接（从 DbProviderFactories.GetFactory 获取），并从连接创建命令，这样如果更改底层数据库，只需更改对 DBPF.GetFactory 的调用。所以你的代码最终应该看起来漂亮且对称：
var provider = DbProviderFactories.GetFactory("System.Data.SqlClient");// Or MS.Data.SqlClient
using (var connection = provider.CreateConnection())
{
connection.ConnectionString = "...";
using (var command = connection.CreateCommand())
{
command.CommandText = "...";
connection.Open();
using (var reader = command.ExecuteReader())
{
...
}
}
}
回答by Chuck Bevitt
I use this pattern. I have this private method somewhere in my app:
我用这个模式。我在我的应用程序中的某个地方有这个私有方法：
private void DisposeCommand(SqlCommand cmd)
{
try
{
if (cmd != null)
{
if (cmd.Connection != null)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
cmd.Dispose();
}
}
catch { } //don't blow up
}
Then I always create SQL commands and connections in a try block (but without being wrapped in a using block) and always have a finally block as:
然后我总是在 try 块中创建 SQL 命令和连接（但没有被包装在 using 块中）并且总是有一个 finally 块：
finally
{
DisposeCommand(cmd);
}
The connection object being a property of the command object makes a using block awkward in this situation - but this pattern gets the job done without cluttering up your code.
连接对象是命令对象的一个属性，在这种情况下使 using 块变得笨拙 - 但这种模式可以在不使代码混乱的情况下完成工作。
