Always Close Database Connections With Finally

Most developers know it's a good practice to always close database connections when you're done using them, in order to release the memory and other resources used by the connection. But most developers also write code that is not guaranteed to do so in every case. Consider the following C# code:

SqlConnection sqlConn = new SqlConnection(/* connection string */);
SqlDataReader sqlReader = null;
try
{
  sqlConn.Open();
  String sqlString = "SELECT first_name FROM PERSON WHERE person_id = 1";
  SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConn);
  sqlReader = sqlCommand.ExecuteReader();
  sqlReader.Read();
  if (sqlReader.GetString(0).Equals("John"))
  {
    return;
  }
}
catch (Exception e)
{
  /* handle the exception */
}
if (sqlReader != null)
{
  sqlReader.Close();
}
sqlConn.Close();

What's wrong with the above code? Since the try block contains a return statement, the reader and connection may never be closed. We can ensure that they always get closed using a finally block:

SqlConnection sqlConn = new SqlConnection(/* connection string */);
SqlDataReader sqlReader = null;
try
{
  sqlConn.Open();
  String sqlString = "SELECT first_name FROM PERSON WHERE person_id = 1";
  SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConn);
  sqlReader = sqlCommand.ExecuteReader();
  sqlReader.Read();
  if (sqlReader.GetString(0).Equals("John"))
  {
    return;
  }
}
catch (Exception e)
{
  /* handle the exception */
}
finally
{
  if (sqlReader != null)
  {
    sqlReader.Close();
  }
  sqlConn.Close();
}

Of course, it's unusual to have a return in the middle of a try block. But there are other situations where the same applies, such as when the try block calls a method that halts program execution. Even if some code doesn't currently run the risk of not closing a connection, it might be changed in the future. It's best to play it safe and get in the habit of always using finally to close connections, and knowing that you're covered no matter what.