SqlCommand (Using Statement / Disposing issue)

Question

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.

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

Example 2 - Dispose manually

        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

        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?

        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?

        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?

        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

Yeomanly

The using statement is safer, but a try/catch that ignores all exceptions is discouraged in most situations. So how and where you put your try catch all depends on what you are trying to accomplish. What's your question? – Approval

this question appears to me to be better suited to codereview.stackexchange.com – Interdigitate

The empty try/catch was just an example, but in this case it was just there for safety. External to the procedure this is in I was going to check is the dataset contained any tables. If it didn't I would deal with appropriately. Regarding the question, it was at the top - I was asking what is the best way to deal with SqlCommand's disposal. I personally think Example 5 is the correct one but wanted to know feedback from others. – Yeomanly

@Dan-o - Sorry I did not know of codereview.stackexchange.com. I can remove this question if preferred. – Yeomanly

I'm not a moderator. To me it just seems more suited to the codereview format than the SO format. Leave it until somebody in charge makes a decision. In the mean time you may get a useful answer. – Interdigitate

this also appears to be a homework assignment? – Interdigitate

No, this is a serious question for our business needs. We've had all sorts of issues trying to figure out connection pooling issues today and have reverted to adding Using on our SqlConnection's. In doing this I wondered if the same needed to be done on SqlCommand's. If you look at my previous questions you will see these are not "homework" assignments. – Yeomanly

Answer 1

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

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

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

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.

---

[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);
}

Isopropanol

Thanks. I didn't know that about data adapters so will modify accordingly. So if the item becomes elegible for garbage collection are you saying the Using statement is not really necessary? – Yeomanly

@Yeomanly I edited my answer to add some some information specifically about disposing of an SqlCommand. – Isopropanol