How to Dispose SqlConnection in C#

[sachintha81]

I am a bit (well, actually a lot) confused with how exactly I should use the Dispose() method on SqlConnection object in C#. First let me tell you what I have learned so far (I've been reading a LOT on the subject for the past 24 hours, and I'm still confused) from what I read.

SqlConnection is a managed object withing the .NET framework. HOWEVER, it uses unmanaged resources to implement the connection.

Now, my program uses an SqlConnection, and I also am concerned about thread safety. It is a program that runs in collaboration with a service and the service constantly accesses my program, using more than one thread. Therefore, it is extremely important for me to dispose of the object without waiting for the GC to do it for myself.

Here is the basic code that I am thinking of using.

public class MySqlConn : IDisposable
{
  private static readonly object objectLock = new object();
  private bool _disposed;
  
  private DBConnection dbConn;
  private SqlConnection hConnection;

  public MySqlConn()
  {
    dbConn = new DBConnection();
    hConnection = dbConn.Open();
    _disposed = false;
  }

  public void DoSomeWork()
  {
    //Use SqlConnection
  }

  public void Dispose()
  {
    Dispose(true);

    GC.SuppressFinalize(this);
  }

  protected virtual void Dispose(bool disposing)
  {
    lock (objectLock)
    {
      if (_disposed == false)
      {
        if (disposing == true)
        {
          //Dispose managed resources
          if (dbConn != null)
          {
            dbConn.Dispose();
            dbConn = null;
          }
          //Dispose unmanaged resources
          //Here is my problem -> 
          //How should I dispose of the unmanaged resouces that SqlConnection uses?

          _disposed = true;
        }
      }
    }
  }
}

The DBConnection class is a separate class in my program. It basically creates an SqlConnection, and returns it after opening it inside it's DBConnection.Open() method. If you need it to understand better, here it is.

public class DBConnection
{
  private SqlConnection hConnection;

  public DBConnection()
  {
    stConnectionString += "Data Source     = MyDataSource;";
    stConnectionString += "Initial Catalog   = MyDataBaseName;";
    stConnectionString += "Integrated Security = SSPI;";

    hConnection = (new SqlConnection(stConnectionString));
  }  

  public SqlConnection Open()
  {
    hConnection.Open();
    return hConnection;
  }

  public void Dispose()
  {    
    hConnection.Close();
    hConnection.Dispose();
  }
}

Now, here is my problem. In MySqlConn class' Dispose() method, I understand that I need to call the DBConnection.Dispose() method, so it will close and dispose of the SqlConnection it created. OK, fine. However, I know that the SqlConnection uses some unmanaged resources as well. Therefore, in the place where you have the comment //Dispose unmanaged resources, should I dispose those unmanaged resources of SqlConnection? If so, how? I don't even know what those unmanaged resources are in the first place.

[Momerath]

Don't worry about it, the SQLConnection object will handle the disposing of the unmanaged resources all by itself.

[hyperion]

Hello,

If you are worried about unmanaged resources, then rest assured its just as Momerath said :).

Though you can use the following for closing the connection cleanly.

System.Data.SqlClient.SqlConnection sqlConn = null;
try
{
    sqlConn = new System.Data.SqlClient.SqlConnection("connection string");
    sqlConn.Open();
    // Some other code goes here using sqlConn
    //...
}
finally
{
    if (sqlConn != null && sqlConn.State == System.Data.ConnectionState.Open)
    {
        sqlConn.Close();
    }
}

[Momerath]

And to simplify Hyperion's code, you can do this:

using(SqlConnection sqlConn = new SqlConnection("connection string")) {
    sqlConn.Open();
    // Some other code goes here using sqlConn
    // ...
}

You can read more about it here.