How to Dispose SqlConnection in C#

Question

_{Thursday, October 14, 2010 1:23 AM}

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.

All replies (7)

_{Thursday, October 14, 2010 5:52 AM ✅Answered}

SqlConnection class implements IDisposable interface. So you use Dispose( ) method directly. The efficent way of doing is with 'Using' statement

using(SqlConnection connection = new SqlConnection())
{

  // Use connection object here.

}//->> Here it will automatically calls Dispose( )

_{Thursday, October 14, 2010 6:18 AM ✅Answered}

hi,

i agree with Mike_999, use using statement. besides... to communicate with database implement a class with:

- internal using of sqlconnection objects only (hide sqlconnection inside a class)

- public methods like: object ExecuteScalar(string query, //params - parameters), int ExecuteNonQuery(string query, // parameters), etc)

- inside public methods open and close connection/transactions

- do not keep/share open connection between classes/methods

BR, Karol. mark as answer/vote as helpful if it helped you

_{Thursday, October 14, 2010 7:34 AM ✅Answered}

I strongly agree with those who are suggest use the 'using ' keyword, because (so far) it was the best practices of closing SqlConnection since it was implement IDisposable interface.

for further reading you can read this articles:

Best Practices for using ADO.NET

Best Regards,

_Chris_

Regards, Christofel

_{Thursday, October 14, 2010 8:22 AM ✅Answered}

I also always advice using the "using" keyword.

However, probably because of the many confusions of the method dispose. Which is a method from components which is inherited by almost every classic net class, has the System.Data team incorporated the dispose method in the close event of that class (and the close in the dispose method). So what we see currently at the end of your code (both close and dispose) is in fact completely senseless.

The unmanaged resources of the connection is only the connectionstring and as far as my information goes is that also the only unmanaged resources from system.Data.

However, try "using", it does nothing more but gives cleaner code.

Success
Cor

_{Thursday, October 14, 2010 9:08 AM ✅Answered}

Sachintha,

Be aware that the dispose method does not dispose objects, that mistake is often made (not only by you also inside Microsoft).

The dispose method is to release unmanaged resources. Some teams have done that very good, like the AdoNet team. However for some others Net was in past just a wrapper around Com elements. Think about Sharepoint.

Disposing of a Net object is done by the Garbage Collector (GC) and nothing else, that is a process which has stages. You can make that process quicker by first calling the dispose method, so the unmanaged resources are released before that (be aware that is in the time other processes should be done then in your application, but that goes quick).

The GC releases objects as soon as it sees that there is not any reference any more too it. So you can call the dispose method 10000 times. As long as there is something in your program like in a shared class created.

MySharedClass.Connection = new MyConnectionClass();

That last new created object will not be released. For that you have to set

MySharedClass.Connection = null;

And it will be released by the GC at a moment nothing else is running (or there is to less memory).

Be aware that to few handles do not start the GC (I don't know yet if that is changed in Framework 4) that is why there is probably also much confusion around the dispose. (For instance it is better to release GDI objects as soon as they are ready for releasing because those eat very much handles).

Success
Cor

_{Thursday, October 14, 2010 6:52 AM}

Please read Remarks section in the following page. You can get some useful tips for SQLConnection

SqlConnection.Close Method

Thanks and Regards, Bharath S.

_{Thursday, October 14, 2010 8:47 AM}

Hi Cor, thanks for the reply. It has been helpful.

If you can answer these questions I think I'll get a clear picture.

1. Since SqlConnection class implements IDisposable interface, does that mean I do not have to inherit it and write Dispose(bool) method inside MySqlConn class?

2. I use MySqlConn class' object creation in another class where I encapsulate it in a using statement. I haven't shown that code, but that is how I get the Dispose() of MySqlConn to be called. Is it not needed? But this class which uses MySqlConn is not able to close the Connection within it. That is why I used Dispose(). What am I supposed to do here?

3. So, I do not have to worry about unmanaged resources when I work with SqlConnection objects as long as I call Close() or Dispose() ?

4. As belial has suggested, I use DBConnection class to hide my SqlConnection. Perhaps you guys haven't understood that part clearly. It is used by other classes of my program so I am not at liberty to change it.

Last updated on 2010-10-14

Share via

How to Dispose SqlConnection in C#

Question

All replies (7)

Additional resources