I got the following code from a student, who wanted to know why it works to have Close() BEFORE the creation of the SqlDataAdapter.

// The connection seems to be usable regardless of .Open() and .Close()
sqlConnection1.Open();
sqlConnection1.Close();
sqlConnection1.Close();

//SQL connection is used in the following statement
System.Data.SqlClient.SqlDataAdapter adaptSQL
new System.Data.SqlClient.SqlDataAdapter("Select * from Blogs", sqlConnection1);

System.Data.DataSet dataPubs = new DataSet("Blogs");
adaptSQL.Fill(dataPubs, "Blogs");
dataGrid1.SetDataBinding(dataPubs, "Blogs");

It turns out that SqlDataAdapter simply opens and closes a connection for you--it's not necessary to call Open or Close explicitly. This object implements what's called a "forward only, read only" cursor that scans quickly through a database. It's also sometimes called a "fire hose". After this initial scan, the object lives in memory, but disconnected from the database.

From http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.aspx:

"It is the SqlDataAdapter that manages connections with the data source and gives us disconnected behavior. The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task. For example, the SqlDataAdapter performs the following tasks when filling a DataSet with data:

1. Open connection
2. Retrieve data into DataSet
3. Close connection

and performs the following actions when updating data source with DataSet changes:

1. Open connection
2. Write changes from DataSet to data source
3. Close connection"