ADO.NET's DataAdapters and Opened Connections

I have been reviewing a bunch of code that utilizes ADO.NET’s DataAdapters. This code has been some samples that are on the Internet, some questions directly to http://wildermuth.com and others from DevelopMentor’s .NET Mailing Lists. One thing I have noticed is that much of that code is opening the database connection before using the DataAdapter to fill a DataSet.

This does work, but there seems to be some confusion about how this should work. In fact, under the covers all DataAdapters open and close the database if it has not already been done. This is because the DataAdapter knows when to open and close the connection to the database to minimize the length of time that the precious resource (the connection) is actually open. This is what the code looks like that I’ve been reviewing:

// Create the DataSet
DataSet dataSet = new DataSet();

// Create the Connection
SqlConnection conn = new SqlConnection("...");

// Create the Command
SqlDataAdapter adapter = new SqlDataAdapter("...", conn);

// Open the Connection
conn.Open();

// Fill the DataSet
adapter.Fill(dataSet);

// Close the Connection
conn.Close();

In reality, you don’t need to open the close the connection before using it with an adapter. If the connection is closed, it will open it and close it as soon as it is done with the connection. I would expected to see more code that looked like this:

// Create the DataSet
DataSet dataSet = new DataSet();

// Create the Connection
SqlConnection conn = new SqlConnection("...");

// Create the Command
SqlDataAdapter adapter = new SqlDataAdapter("...", conn);

// Fill the DataSet
adapter.Fill(dataSet);
```csharp

There are times when you would want to open the connection first, but usually  only if you are doing many small Fill()'s or Update()'s. In most cases, letting  the DataAdapter handle the connection object's state is the right thing to  do.