SqlCommand（使用语句/处置问题） [英] SqlCommand (Using Statement / Disposing issue)

问题描述

以下面的示例为例...

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

今天从我的研究看来，这似乎基本可以，但是SqlCommand并未得到处理。

From my research today is sounds as though this is basically okay but the SqlCommand is not being disposed of.

问题->以下哪个示例是解决此问题的最佳方法？

Question -> Which of the following examples is the best way to deal with this?

示例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

示例3-使用Using语句自动处理

Example 3 - Automatic disposing with the Using statement

        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

示例4-与示例3相同，但是Try / Catch在Using-doe中

Example 4 - The same as example 3 but the Try/Catch is within the Using - does this make a difference?

        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

示例5-与示例4相同，但在Using语句中指定了CommandText和cn -这有什么好处？

Example 5 - The same as example 4 but the CommandText and cn are specified in the Using Statement - What advantage does this have?

        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

示例6-与示例5相同，但是连接是在cn而不是cmd上打开的。如果只执行一个存储过程，最好在cmd上打开连接吗？

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?

        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

推荐答案

DataAdapter.Fill命令将打开和关闭连接本身，因此您不需要 cmd.Connection.Open（）。 （请参阅： http://msdn.microsoft.com/en -us / library / 377a8x4t.aspx 。）

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 .)

对SQLConnection使用使用具有效果为您调用 .Close 的提示。

Using Using for the SqlConnection has the effect of calling .Close on it for you.

变量 cmd 就有资格进行垃圾收集。）

The variable cmd becomes eligible for garbage collection once it is out of scope (or earlier if .NET determines it isn't going to be used again).

在您的示例中2，我不确定在DataAdapter使用cmd之前处置cmd是不是一个好主意。

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.

[我应该在SQLCommand对象上调用Dispose吗？]在撰写本文时，调用 .Dispose 在 SqlCommand 上无效，因为其构造函数中的代码：

[Information from user "JefBar Software Services" in Should I call Dispose on a SQLCommand object? ] At the time of writing, calling .Dispose on an SqlCommand has no effect because of the code in its constructor:

public SqlCommand() : base() {
    GC.SuppressFinalize(this);
}

这篇关于SqlCommand（使用语句/处置问题）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！