vb.net SqlCommand（使用语句/处理问题）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12498599/
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
SqlCommand (Using Statement / Disposing issue)
提问by cw_dev
Take the following example...
看下面的例子...
Using cn As New SqlConnection(ConnectionString)
Try
Dim cmd As SqlCommand = New SqlCommand
With cmd
.Connection = cn
.Connection.Open()
.CommandText = "dbo.GetCustomerByID"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CustomerID", SqlDbType.Int, 4)
.Parameters("@CustomerID").Value = CustomerID
End With
da = New SqlDataAdapter(cmd)
da.Fill(ds, "Customer")
Catch ex As Exception
End Try
End Using
From my research today is sounds as though this is basically okay but the SqlCommand is not being disposed of.
从我今天的研究来看，这听起来基本没问题，但 SqlCommand 没有被处理掉。
Question -> Which of the following examples is the best way to deal with this?
问题 -> 以下哪个示例是解决此问题的最佳方法？
Example 2 - Dispose manually
示例 2 - 手动处理
Using cn As New SqlConnection(ConnectionString)
Try
Dim cmd As SqlCommand = New SqlCommand
With cmd
.Connection = cn
.Connection.Open()
.CommandText = "dbo.GetCustomerByID"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CustomerID", SqlDbType.Int, 4)
.Parameters("@CustomerID").Value = CustomerID
End With
da = New SqlDataAdapter(cmd)
cmd.Dispose()
da.Fill(ds, "Customer")
Catch ex As Exception
End Try
End Using
Example 3 - Automatic disposing with the Using statement
示例 3 - 使用 Using 语句自动处理
Using cn As New SqlConnection(ConnectionString)
Try
Using cmd As New SqlCommand
With cmd
.Connection = cn
.Connection.Open()
.CommandText = "dbo.GetCustomerByID"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CustomerID", SqlDbType.Int, 4)
.Parameters("@CustomerID").Value = CustomerID
End With
da = New SqlDataAdapter(cmd)
da.Fill(ds, "Customer")
End Using
Catch ex As Exception
End Try
End Using
Example 4 - The same as example 3 but the Try/Catch is within the Using - does this make a difference?
示例 4 - 与示例 3 相同，但 Try/Catch 位于 Using 中 - 这有什么不同吗？
Using cn As New SqlConnection(ConnectionString)
Using cmd As New SqlCommand
Try
With cmd
.Connection = cn
.Connection.Open()
.CommandText = "dbo.GetCustomerByID"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CustomerID", SqlDbType.Int, 4)
.Parameters("@CustomerID").Value = CustomerID
End With
da = New SqlDataAdapter(cmd)
da.Fill(ds, "Customer")
Catch ex As Exception
End Try
End Using
End Using
Example 5 - The same as example 4 but the CommandText and cn are specified in the Using Statement - What advantage does this have?
示例 5 - 与示例 4 相同，但在 Using 语句中指定了 CommandText 和 cn - 这有什么优势？
Using cn As New SqlConnection(ConnectionString)
Using cmd As New SqlCommand("GetCustomerByID", cn)
Try
With cmd
.Connection.Open()
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CustomerID", SqlDbType.Int, 4)
.Parameters("@CustomerID").Value = CustomerID
End With
da = New SqlDataAdapter(cmd)
da.Fill(ds, "Customer")
Catch ex As Exception
End Try
End Using
End Using
Example 6 - The same as example 5 but the connection is opened on cn instead of cmd. Is it better to open the connection on cmd if only one stored procedure is to be executed?
示例 6 - 与示例 5 相同，但连接是在 cn 而不是 cmd 上打开的。如果只执行一个存储过程，在cmd上打开连接是否更好？
Using cn As New SqlConnection(ConnectionString)
cn.Open()
Using cmd As New SqlCommand("GetCustomerByID", cn)
Try
With cmd
.Connection = cn
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CustomerID", SqlDbType.Int, 4)
.Parameters("@CustomerID").Value = CustomerID
End With
da = New SqlDataAdapter(cmd)
da.Fill(ds, "Customer")
Catch ex As Exception
End Try
End Using
End Using
采纳答案by Andrew Morton
The DataAdapter.Fill command will open and close the connection itself, so you don't need the cmd.Connection.Open(). (Ref: the remarks section in http://msdn.microsoft.com/en-us/library/377a8x4t.aspx.)
DataAdapter.Fill 命令将打开和关闭连接本身，因此您不需要cmd.Connection.Open(). （参考：http: //msdn.microsoft.com/en-us/library/377a8x4t.aspx 中的备注部分。）
Using Usingfor the SqlConnection has the effect of calling .Closeon it for you.
使用UsingSqlConnection 具有.Close为您调用它的效果。
The variable cmdbecomes eligible for garbage collection once it is out of scope (or earlier if .NET determines it isn't going to be used again).
cmd一旦超出范围（或更早，如果 .NET 确定不会再次使用它），该变量就有资格进行垃圾回收。
In your example 2, I'm not sure it's such a good idea to dispose of the cmd before the DataAdapter has used it.
在您的示例 2 中，我不确定在 DataAdapter 使用它之前处理 cmd 是不是一个好主意。
[Information from user "JefBar Software Services" in Should I call Dispose on a SQLCommand object?] At the time of writing, calling .Disposeon an SqlCommandhas no effect because of the code in its constructor:
[来自用户“JefBar 软件服务”的信息，我应该在 SQLCommand 对象上调用 Dispose 吗？] 在撰写本文时，由于其构造函数中的代码，调用.DisposeanSqlCommand无效：
public SqlCommand() : base() {
GC.SuppressFinalize(this);
}
