SqlCommand (using statement / deletion)

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 it sounds as if it is basically normal, but SqlCommand is not recyclable.

Question → Which of the following examples is the best way to handle this?

Example 2 - Dispose of 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 deletion using an operator

  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 - Same as example 3, but Try / Catch is in use - does that 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 - Same as in example 4, but CommandText and cn are specified in the Operation of use - 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 - Same as in 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 should 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 

Answer 1

The DataAdapter.Fill command will open and close the connection, so you do not need cmd.Connection.Open() . (Link: comments section at http://msdn.microsoft.com/en-us/library/377a8x4t.aspx .)

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

The cmd variable becomes available for garbage collection when it goes out of scope (or earlier if .NET determines that it will no longer be used).

In your example 2, I'm not sure if it is a good idea to get rid of cmd before the DataAdapter has used it.