Do I need to explicitly dispose SqlDataAdapter?

Question

In this thread, there's a suggestion that after the operation, the instance of SqlDataAdapter is disposed of explicitly like so.

String connString = @"your connection string here";
String query = "select * from table";

SqlConnection conn = new SqlConnection(connString);        
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
conn.Close();
da.Dispose();

Is it really necessary? What about GC?

Answer 1

It is highly recommended to Dispose IDisposable objects manually. There is a nice syntax shortcut for this:

using SqlConnection con = new SqlConnection(connstring);
using SqlCommand com = new SqlCommand();
using SqlDataAdapter da = new SqlDataAdapter();
com.Connection = con;
//etc..

This way the compiler will make sure Dispose() gets called on all objects created with using after they get out of scope (it uses try..finally to achieve this).

GC is not responsible for calling Dispose() on your objects, its main responsibility is to collect objects from heap that are no longer referenced. One exception to this is if your class is Finalizable. In this case GC will make sure your object's finalizer gets called first, and then it gets collected. You can call Dispose() in your Finalizer and there is a nice pattern for this called "Dispose Method": http://msdn.microsoft.com/en-us/library/fs2xkftw.aspx

But the general rule is (with a couple of exceptions): If you're instantiating an object that implements IDisposable, it's your responsibility to call Dispose on it.

Answer 2

From the code example in the MSDN article for SqlDataAdapter Class:

private static DataSet SelectRows(DataSet dataset, string connectionString,string queryString) 
{
    using (SqlConnection connection = 
        new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(
            queryString, connection);
        adapter.Fill(dataset);
        return dataset;
    }
}

The SqlConnection is wrapped in a using statement, but not the SqlDataAdapter.

So I would say it is not required.

That said, some lean towards If it implements IDisposable, dispose of it.

In that case, you could also wrap the SqlDataAdapter in a using statement. From that linked article:

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement.

Answer 3

As a rule... yes, you do.
There are some cases, when call to Dispose isn't required, but these cases are based on an implementation details, and shouldn't be considered as general approach.

Disposable isn't about garbage collection. It is about deterministic resource cleanup. These things are parallel universes.

GC can collect object, which implements IDisposable without a call to Dispose. But if that object holds an OS handle, for example, you have two ways: either release handle immediately (call Dispose), or wait for finalizer (but when the finalizer will run, knows only GC).

When your resource is managed, the things are even worse.
Managed resources shouldn't be released in finalizers. Hence, all resource cleanup is a responsibility of Dispose method. If you don't call Dispose, managed resources will be never cleaned up (the most popular sample is an event unsubscription), which brings you to memory leaks.

Answer 4

String connString = @"your connection string here";
String query = "select * from table";

SqlConnection conn = new SqlConnection(connString);        
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
**Using(SqlDataAdapter da = new SqlDataAdapter(cmd))
{
    da.Fill(dataTable);
    conn.Close();
}**

Using will dispose of it for you once youve reached the end of the block.