Do I Have to Close() a SQLconnection Before It Gets Disposed

Do I have to Close() a SQLConnection before it gets disposed?

Since you have a using block, the Dispose method of the SQLCommand will be called and it will close the connection:

// System.Data.SqlClient.SqlConnection.Dispose disassemble
protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

Do I need to close and dispose the SQLConnection explicitly?

You should Dispose every temporary IDisposable instance you create manually, i.e. wrap them into using:

   // Connecton is IDisposable; we create it 
   //   1. manually - new ...
   //   2. for temporary usage (just for the query)
   using (SqlConnection con = new SqlConnection(objUtilityDAL.ConnectionString)) {
     // Check is redundant here: new instance will be closed 
     con.Open();

     // Command is IDisposable
     using (SqlCommand cmd = con.CreateCommand()) {
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add(Parameter);
       cmd.CommandText = _query;

       // Finally, Reader - yes - is IDisposable 
       using (SqlDataReader rdr = cmd.ExecuteReader()) {
         // while (rdr.Read()) {...}
       }
     }   
   }

Please, notice that

   try {
     ...
   }
   catch (Exception ex) {
     throw ex;
  } 

is at least redundant (it does nothing but rethrow the exception, while loosing stack trace) and that's why can be dropped out

Close and Dispose - which to call?

I want to clarify this situation.

According to Microsoft guidelines, it's a good practice to provide Close method where suitable. Here is a citation from Framework design guidelines

Consider providing method Close(), in addition to the Dispose(), if close is standard terminology in the area. When doing so, it is important that you make the Close implementation identical to Dispose ...

In most of cases Close and Dispose methods are equivalent. The main difference between Close and Dispose in the case of SqlConnectionObject is:

An application can call Close more
than one time. No exception is
generated.

If you called Dispose method
SqlConnection object state will be
reset. If you try to call any
method on disposed SqlConnection
object, you will receive exception.

That said:

• If you use connection object one
  time, use Dispose. A using block will ensure this is called even in the event of an exception.
• If connection object must be reused,
  use Close method.

Does End Using close an open SQL Connection

Exiting a using block calls .Dispose() on the object in question (cn in your example) which for a SqlConnection will close the connection and any open resources.

Is it necessary to manually close and dispose of SqlDataReader?

Try to avoid using readers like this:

SqlConnection connection = new SqlConnection("connection string");
SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection);
SqlDataReader reader = cmd.ExecuteReader();
connection.Open();
if (reader != null)
{
      while (reader.Read())
      {
              //do something
      }
}
reader.Close(); // <- too easy to forget
reader.Dispose(); // <- too easy to forget
connection.Close(); // <- too easy to forget

Instead, wrap them in using statements:

using(SqlConnection connection = new SqlConnection("connection string"))
{

    connection.Open();

    using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    //do something
                }
            }
        } // reader closed and disposed up here

    } // command disposed here

} //connection closed and disposed here

The using statement will ensure correct disposal of the object and freeing of resources.

If you forget then you are leaving the cleaning up to the garbage collector, which could take a while.

If SqlDataReader is going to be disposed, do I need to call close()?

If it's in a using block, then it is automatically closed. You do not need to explicitly close it.

BTW the SqlCommand in your example is disposable. You should create it in a using block too, otherwise any resources it controls won't be let go until the garbage collector collects.

Your undisposed SqlCommand is actually a good example of why C#'s emulation of RAII is not "real" RAII. You must take an explicit action (making blocks) for the RAII to kick in which is equivalent to an explicit close albeit with different syntax.

Is it safe to have a SqlConnection close in a classes destructor upon program completion in C#?

It looks like you want your class to encapsulate the out-of-the-box SqlConnection class, since it holds an instance of SqlConnection in dbConnection which I assume is a field on the class instance.

The thing is that SqlConnection is IDisposable. So if your class wants to hold an instance of that in a field, that means your class should also be IDisposable. From Microsoft's CodeAnalysis rules (literally, it is rule 1001), see Types that own disposable fields should be disposable.

You might also like to note the line on that page saying:

If the class does not directly own any unmanaged resources, it should not implement a finalizer.

---

Related Topics