Will dbCommand.Close() close the connection as well?

Question

I have the following ado.net code, if I already use using to wrap my DBCommand, do I have to close the underneath connection explicitly?

Thanks,

       public static void ExecuteSQL(int Id)
        {
            Database db;
            const string sqlCommand = "Stored Procedure Name";
            try
            {
                db = DatabaseFactory.CreateDatabase();
                using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
                {
                    db.AddInParameter(dbCommand, "p_Id", DbType.Int32, Id);
                    db.ExecuteNonQuery(dbCommand);
**//do I have to close connection explicitely here??**
                    if (dbCommand.Connection.State != ConnectionState.Closed)
                    {
                        dbCommand.Connection.Close();
                    }
                    dbCommand.Connection.Dispose();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error(ex.StackTrace, ex);
                throw;
            }
        }

Answer 1

No, you do not.

Once the end of the using block is hit, dispose is called for you.

Answer 2

Yes, if all you have wrapped in the using block is the DbCommand, then you will have to explicitly close the DbConnection, as you currently do in your code. It is sufficient, however, simply to call Dispose. You do not need to call both Close and Dispose.

Answer 3

I think this is an excellent question. And one that I would like to know more about. Can people please contribute more to this story?

I agree you need the using for the DbCommand and think that the underlying connection must be closed and disposed of. But the way its done above doesn't appear to be a robust solution to me. In the event an exception occurs the connection is not disposed or closed.

Obviously it would be ideal if we could wrap the DbConnection in a using statement as well but the architecture of the Enterprise Library Data Access Application Block appears to encapsulate the connection so we can't do that.

Note sure an 'answer' was the best way to post this comment as I'm fairly new to SO let me know if there is a better way to add content like this.

Thanks, Justin

Answer 4

http://www.willydev.net/descargas/WillyDev_EntLib_TestGuide.pdf

The application block closes the database connections after it is finished with them. For example, the implementation of the ExecuteNonQuery method includes a using statement. The using statement obtains resources, executes a statement, and disposes of the resources. In this case, the resource is the database connection. In the case of the ExecuteReader method, the application block uses the Command-Behavior.CloseConnection method to close the connection after the reader closes.