Opening and Closing Connections (Semi-) Automatically

Once you have the ConnectionString configured, you would think that you're ready to open the Connectionyou aren't. Consider that ADO.NET manages the SqlConnection for you automatically if you use the SqlDataAdapter Fill or Update methods. In this case (as I'll discuss in detail in Chapter 10), if the SqlConnection is not yet open, the Fill or Update methods open the SqlConnection (associated with the SqlDataAdapter), run the query, return the rowset(s), and close the SqlConnectionall in one atomic step. If the SqlConnection is already open when Fill or Update is executed, the SqlConnection is left open after the methods are done. If something goes wrong during the Fill or Update operations, ADO.NET ensures that the SqlConnection is returned to its initial state.

If you intend to build a SqlCommand and create a SqlDataReader, you'll need to execute the SqlConnection Open method yourself. Once you take on this responsibility, you also carry the burden of making sure the SqlConnection is closed once you've completed reading all of the resultsets from the SqlDataReader. As discussed in Chapter 10, you can get the SqlCommand.ExecuteReader method to close the SqlConnection for you when (and only when) the SqlDataReader is closedeither with the SqlDataReader Close method or when binding to a complex bound control like a DataGridView.