[RESOLVED] SqlCommand and SqlConnection dispose

I am using code below to open and dispose connection.

     -----------------------

       Dim mySQLCommand As New SqlCommand("sp_addorder", myConn)

        mySQLCommand.CommandType = CommandType.StoredProcedure

        -----

         Using myConn

            Try

                myConn.Open()

                mySQLCommand.ExecuteNonQuery()

                myConn.Close()

              Catch ex As SqlException

           Finally

                myConn.Dispose()

            End Try

        End Using

---------------------------------

Do I need to do more before disposing, like

1. Check to see if the object exists before calling the dispose method
2. The mySQLCommand should also be disposed of

       

This may help you determine how to best handle that:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

 

I would presume just check that myConn and mySQLCommand are not null and if they aren't, dispose

You could check it isn't nothing first for completeness, but there is no way it can be nothing in the finally block.  You don't need to dispose the sqlcommand.

Exactly why I provided the op with that link :)

I was replying at the same time, I didn't see your reply until I posted :)

Ah that's true :)  That is what I sometimes will do is provide a link so the poster can see it in action written a bit more eloquently than I can.

You might try wrapping both your Connection and your Command within Using statements :

' Establish your connection '
Using myConn
     Try
          ' Define your command to execute '
          Using mySQLCommand As New SqlCommand("sp_addorder", myConn)
                ' Open your connection '
                myConn.Open()  

                ' Indicate this command is a stored procedure '
                mySQLCommand.CommandType = CommandType.StoredProcedure
                
                ' Execute your query '
                mySQLCommand.ExecuteNonQuery()
          End Using
     Catch ex As SqlException
          ' Do something here if an error occurs '
     End Try
End Using

The Using statement will take care of closing and disposing any open connections that you have so you don't need to worry about it manually (via a Finally statement). If you take a look at the
documentation for the using statement, you'll see that it actually compiles into a try-catch-finally statement.

I like that Rion, very clean and efficient.  I trust this also would apply to c# correct?

bbcompent1

I like that Rion, very clean and efficient.  I trust this also would apply to c# correct?

Correct.

A C# equivalent would look something like :

//Establish your connection
using(var sqlConnection = new SqlConnection("Your Connection String"))
{
     try
     {
          // Define your command to execute
          using(var sqlCommand = new SqlCommand("sp_addorder", sqlConnection))
          {
                // Open your connection 
                sqlConnection.Open();

                // Indicate this command is a stored procedure 
                sqlCommand.CommandType = CommandType.StoredProcedure;
                
                // Execute your query 
                sqlCommand.ExecuteNonQuery();
          }
     }
     catch(SqlException ex)
     {
          // Do something here if an error occurs
     }
}

Rion Williams

You might try wrapping both your Connection and your Command within Using statements :

' Establish your connection '
Using myConn
     Try
          ' Define your command to execute '
          Using mySQLCommand As New SqlCommand("sp_addorder", myConn)
                ' Open your connection '
                myConn.Open()  

                ' Indicate this command is a stored procedure '
                mySQLCommand.CommandType = CommandType.StoredProcedure
                
                ' Execute your query '
                mySQLCommand.ExecuteNonQuery()
          End Using
     Catch ex As SqlException
          ' Do something here if an error occurs '
     End Try
End Using

The Using statement will take care of closing and disposing any open connections that you have so you don't need to worry about it manually (via a Finally statement). If you take a look at the
documentation for the using statement, you'll see that it actually compiles into a try-catch-finally statement.

If you're doing your own try catch there isn't much point in using "using", you might as well implement your own finally.

AidyF

If you're doing your own try catch there isn't much point in using "using", you might as well implement your own finally.

That's a good point AidyF. I was just demonstrating that a using could be used here with regards to worry about resource disposal in particular. It all depends on how OP plans to use the catch section.