Does SqlDataAdapter close the SqlConnection after Fill() function?

Question

Does SqlDataAdapter close the SqlConnection after the Fill() function or do I need close it myself?

string cnStr = @"Data Source=TEST;Initial Catalog=Suite;Persist Security Info=True;User ID=app;Password=Immmmmm";
cn = new SqlConnection(cnStr);
SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM Date", cn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();
adapter.Fill(ds);

cn.Close() // ????????

Console.WriteLine(ds.Tables[0].Rows.Count);
Console.WriteLine(cn.State);

Solution 1

In your current usage, it will close for you:

If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

http://msdn.microsoft.com/en-us/library/zxkb3c3d.aspx

I think it's always better to explicitly cater for it yourself with a using statement:

using (SqlConnection conn = new SqlConnection(""))
{
    conn.Open();

    // Do Stuff.

} // Closes here on dispose.

This is often more readable and doesn't rely on people understanding the inner workings of SqlDataAdapter.Fill, just the using statement and connections.

However, if you know the connection is closed before the adapter uses it (as in, you've just created the connection) and it's not used for anything else, your code is perfectly safe and valid.

Personally, I'd write something like this:

    string cnStr = "Data Source=TEST;Initial Catalog=Suite;Persist Security Info=True;User ID=app;Password=Immmmmm";
    DataSet ds = new DataSet();

    using (SqlConnection cn = new SqlConnection(cnStr))
    using (SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM Date", cn))
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    { 
        conn.Open();
        adapter.Fill(ds);       
    }

Solution 2

As I know you need to close the connection by you own

Best way to do is

using(SqlConnection con = new SqlConnection())
{
   // you code 
}

this will close you connection automatically

using block in C# comes very handly while dealing with disposable objects. Disposable objects are those objects that can explicitly release the resources they use when called to dispose. As we know .Net garbage collection is non-deterministic so you can’t predict when exactly the object will be garbage collected.

Read this post for more in details : understanding ‘using’ block in C#