以下面的例子为例……
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
根据我今天的研究,听起来好像这基本上还可以,但是sqlCommand没有被处理掉.
问题 – >以下哪个例子是处理这个问题的最佳方法?
示例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",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
示例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",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
示例4 – 与示例3相同,但Try / Catch在使用中 – 这是否有所不同?
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","Customer") Catch ex As Exception End Try End Using End Using
示例5 – 与示例4相同,但CommandText和cn在Using语句中指定 – 这有什么优势?
Using cn As New sqlConnection(ConnectionString) Using cmd As New sqlCommand("GetCustomerByID",cn) Try With cmd .Connection.Open() .CommandType = CommandType.StoredProcedure .Parameters.Add("@CustomerID","Customer") Catch ex As Exception End Try End Using End Using
示例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","Customer") Catch ex As Exception End Try End Using End Using
DataAdapter.Fill命令将打开和关闭连接本身,因此您不需要cmd.Connection.Open(). (参考:
http://msdn.microsoft.com/en-us/library/377a8x4t.aspx的备注部分.)
使用Using for sqlConnection可以为你调用.Close.
变量cmd一旦超出范围就变得有资格进行垃圾收集(如果.NET确定它不会再次使用,则更早).
在你的例子2中,我不确定在DataAdapter使用它之前处理cmd是个好主意.