How is Dispose called on ADO.NET objects?

Question

The database access classes that implement IDbConnection, IDbCommand and IDataReader all implement IDisposable, but obviously the Command and the Reader are dependent on the Connection. My question is, do I have to Dispose() of each these objects individually or will disposing of the Connection object dispose of the others too ?

That is, can I do this and guarantee I'm not risking leaving any unmanaged resources not being freed:

using (IDbConnection conn = GetConnection())
{
      IDbCommand cmd = conn.CreateCommand();
      cmd.CommandText = " ..... ";
      IDataReader rdr = cmd.ExecuteReader();
      while (rdr.Read())
      {

      }
}

Or do I have to do this instead:

using (IDbConnection conn = GetConnection())
{
    using (IDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = " ..... ";
        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {

            }
        }
    }
}

Or is this implementation dependent, so it would potentially work using one database's provider but not for another's ?

score 4 · Accepted Answer · answered Apr 12 '11 at 22:56

Best policy is to use all ADO.NET objects in using blocks- full stop.

A little Reflector-ing on various ADO.NET objects shows that things will more or less be dropped on the floor if not Closed/Disposed. The effect of that will depend a lot on which providers you're using- if you're using something with unmanaged handles underneath (ODBC, OleDb, etc), you're probably going to leak memory, as I didn't see anything in the way of finalizers. If it's an all-managed provider (eg, SqlClient), it'll eventually get cleaned up, but depending on which objects you're holding onto, you could end up keeping resources in use on the DB server a lot longer than you want.

score 1 · Answer 2 · edited May 23 '17 at 09:58

1

You should dispose them individually or you could also use the solution provide here.

edited May 23 '17 at 09:58

Community

1
1

answered Apr 12 '11 at 22:51

sarvesh

2,743
1
20
30

score 1 · Answer 3 · answered Apr 12 '11 at 22:56

Dispose of them individually. It's entirely possible that the Reader and Command objects do more in their dispose routines than just destroy the connection (either now or in a future version of ADO.NET). So to (a) be explicit and (b) be safe you should dispose of them all individually.

The fact that they implement IDisposable suggests that you should always run the Dispose routine when you're finished with the object, so it's best not to try and second guess the interface, it won't give you any benefit, and you'll probably end up leaking memory and handles.

There are things you can do though to make your code look cleaner when nesting using statements. For example:

(using SqlConnection conn = new SqlConnection())
(using SqlCommand comm = new SqlCommand())
{
    //Do stuff
}

That looks neater (in my opinion) than nesting braces.

score 0 · Answer 4 · answered Apr 12 '11 at 22:59

You should dispose them individually. Even if disposing the IDbConnection would dispose everything under the covers (I don't know if it actually does, nor do I care, for the reason that follow), you should program in the future tense. Tomorrow somebody will edit that code and issue a new command on the same connection and then it will wonder why is he getting an error that the connection is still in use (because the IDataReader was no disposed).

score 0 · Answer 5 · answered Apr 12 '11 at 23:06

The main advantage of the using statement is resources releasing management. Usually, the .NET Framework Garbage Collector controls resources allocation and release. With the using statement, we can take the control of it. So a using statement implements an IDisposible interface which contains a public Dispose() method.

Also you can always call a Dispose() method manually to release a resource or an object from memory.

hope this helps.

How is Dispose called on ADO.NET objects?

5 Answers5

Linked