SQL Connections: Opening and Closing Properly

Bad:

SqlConnection cnn = new SqlConnection();

cnn.Open();

ds = new System.Data.DataSet();

da.Fill(ds);

cnn.Close();

This does not ensure that cnn will get closed if da.Fill(ds) fails.

Good:
      
       SqlConnection cnn = new SqlConnection();

       try

       {

            cnn.Open();

            ds = new System.Data.DataSet();

            da.Fill(ds);

        }

        catch

        {

            throw new Exception("Failed");

        }

        finally

        {

            if (cnn != null)

                cnn.Close();

        }

This example shows that even if things go bad, the connection will always close when exiting.

Better:


       using (SqlConnection cnn = new SqlConnection())


       {

            cnn.Open();

            ds = new System.Data.DataSet();

            da.Fill(ds);

        }

       

Since SqlConnection inherits from IDisposable, the using notataion self-disposes when it is not needed so connection will always be closed at the end.