Don't forget to Close that ADO.NET Connection

Here’s a quick tip in response to some questions I recently have been asked by several readers & bloggers: When using the Command.ExecuteReader method to get a DataReader, pass it the System.Data.CommandBehavior.CloseConnection argument. This argument closes the Connection when the DataReader is closed. Thus, this can save you a line of code to close your Connection. OK, 1 measely line isn’t much to talk about, but then again it can solve another problem that we all run into from time to time: cleaning up our room. No, wait, I mean cleaning up our resources. It is easy to forget to close the connection when you are done with it.

If you run the following code in debug mode, you will see the state of the connection at various points. The key here is that as soon as the DataReader is closed, the connection is also closed. (The code sample assumes that you have a button named Button1 and a TextBox named Text1 in an ASP.NET Web Form.) This is not rocket science, but then again, it can help you code better!

<fieldset><legend>CommandBehavior.CloseConnection</legend>
private void Button1_Click(object sender, System.EventArgs e)
{
    string sConnString = "Server=localhost;Database=Northwind;Integrated Security=True";
    string sSql = "SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName";
    SqlConnection cn = new SqlConnection(sConnString);
    SqlCommand cmd = new SqlCommand(sSql, cn);
    cn.Open();
    System.Diagnostics.Debug.WriteLine("[Pre Loop] - Connection State is " + cn.State.ToString());
    SqlDataReader rdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    while (rdr.Read())
    {
        System.Diagnostics.Debug.WriteLine("[Looping] - Connection State is " + cn.State.ToString());
        TextBox1.Text += rdr["CategoryName"].ToString() + Environment.NewLine;
    }
    rdr.Close();
    System.Diagnostics.Debug.WriteLine("[Post Loop] - Connection State is " + cn.State.ToString());
}
</fieldset>