title: SqlCommand (Using Statement / Disposing issue)
body: <p>Take the following example...</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>From my research today is sounds as though this is basically okay but the SqlCommand is not being disposed of.  </p>
  
  <p>Question -> Which of the following examples is the best way to deal with this?</p>
  
  <p>Example 2 - Dispose manually</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>Example 3 - Automatic disposing with the Using statement</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>Example 4 - The same as example 3 but the Try/Catch is within the Using - does this make a difference?</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>Example 5 - The same as example 4 but the CommandText and cn are specified in the Using Statement - What advantage does this have?</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>        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
  </code></pre>