Do I need to explicitly dispose SqlDataAdapter? [duplicate]

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.