Does the SqlDataAdapter close the SqlConnection function after Fill ()?

Question

Does the SqlDataAdapter SqlConnection close after the Fill() function, or do I need to 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); 

Answer 1

In your current use it will close for you:

If the IDbConnection closes before calling Fill, it opens to retrieve the data and then closes. If the connection is open before calling Fill, it remains open.

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

I think it is always better to serve it explicitly with the using statement:

 using (SqlConnection conn = new SqlConnection("")) { conn.Open(); // Do Stuff. } // Closes here on dispose. 

It is often readable and does not rely on people who understand the internal workings of SqlDataAdapter.Fill , only the using statement and connections.

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

Personally, I would 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); } 

Answer 2

As I know, you need to close the connection yourself

Best way to do it

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

this will automatically close your connection.

Using a block in C # is very convenient when working with disposable objects. Disposable objects are those objects that can explicitly release the resources that they use when called for deletion. As you know, NetNet garbage collection is not deterministic, so you cannot predict exactly when an object will be collected by garbage.

Read this post for more details: understanding block usage in C #