Best practices of disposing of SqlConnection, SqlTransaction, SqlCommand, SqlDataAdapter objects

Question

Are ALL the sqlCommand, sqlTransaction and sqlDataAdapter objects automatically disposed on exit from the "using" block (in which sqlConnection disposed) or all these objects to be disposed should be in separated nested "using" blocks, otherwise these objects will not be disposed when sqlConnection object disposed? What are the best practices?

public static void ExecuteDataset(string connectionString, string storedProcedure, SqlParameter[] sqlParameters = null)
{
    SqlTransaction sqlTransaction = null;

    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
    {
        try
        {
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand(commandText, connection);
            command.Parameters.Add("@ID", SqlDbType.Int);

            sqlCommand.Connection = sqlConnection;
            SqlTransaction = sqlConnection.BeginTransaction();
            sqlCommand.Transaction = sqlTransaction;
            .....................................................
            .....................................................    

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                ................................
                ................................


            sqlTransaction.Commit();
        }
        catch (Exception)
        {
            sqlTransaction.Rollback();
        }
    }        
}

General rule: if it's IDisposable, then Dispose of it when you're done — lc.
– lc., Commented Nov 15, 2017 at 8:01
@ic. Ok, but my question is whether disposing of sqlConnection automatically disposes all the rest objects (slqTransaction, etc) or they should be in nested "using" blocks? — Ilan
– Ilan, Commented Nov 15, 2017 at 8:11
Which is why I wrote as a comment instead of an answer :). In general though I don't worry about the inner workings of things, especially since they're prone to change with each version. If something I create is disposable it means I should dispose it. — lc.
– lc., Commented Nov 15, 2017 at 8:16
@Ilan That is an implementation detail that you should not need to know. Dispose anything your create that is Disposable, preferably with a using block. Anything else is a shortcut that you can do... but it's simply sloppy programming and will fall apart if you change something (like for example your database provider). — nvoigt
– nvoigt, Commented Nov 15, 2017 at 11:17
Maybe. Maybe not. That's the point. You don't know. I don't know. Even if we looked up the implementation to know, the database vendor could change it with the next patch. What we do know is that each of the classes implement IDisposable so you should put them in a using block. That way, they will be handled properly, whatever the database vendor considers "properly". — nvoigt
– nvoigt, Commented Nov 15, 2017 at 11:26

user2321864 · Accepted Answer · 2017-11-15 10:33:14Z

3

If the object implements IDisposable then use using. The underlying implementation details of SqlConnection or SqlCommand or any other object should not matter.

For your scenario the best-practice would be to

using(var conn = new SqlConnection(...)) {
  ...
  using(var cmd = conn.CreateCommand(...)) {
    ...
  }
  ...
}

edited Nov 15, 2017 at 10:33

answered Nov 15, 2017 at 10:10

user2321864

2,3075 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Best practices of disposing of SqlConnection, SqlTransaction, SqlCommand, SqlDataAdapter objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related