Avoid Connection Leak from SqlDataReader

Many developer are using SqlDataReader object to return SQL Data to client. Since SqlDataReader is connection based data class, the SQL connection actually is open under the hood.

Below is the typical example of using SqlDataReader. In DBClass, GetData() method returns SqlDataReader, and the caller (client) uses the DataReader object.

class Program
{
   static void Main(string[] args)
   {
      Program p = new Program();
      p.Run();                  
      Console.ReadLine(); //wait
   }

   void Run()
   {
      DBClass db = new DBClass();
      SqlDataReader rdr = db.GetData("SELECT * FROM Tab1");
      rdr.Read();         
      db.PrintConnectionState();

      rdr.Close();
      db.PrintConnectionState();
   }
}

class DBClass
{
   private string connStr = "Data Source=.;Initial Catalog=TestDB;Integrated Security=SSPI;";
   private SqlConnection conn;

   public SqlDataReader GetData(string sql)
   {
      conn = new SqlConnection(connStr);
      conn.Open();
      SqlCommand cmd = new SqlCommand(sql, conn);
      //SqlDataReader rdr = cmd.ExecuteReader(); // <== #1
      SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);  // <== #2
         
      return rdr;
   }

   public void PrintConnectionState()
   {
      Console.WriteLine(conn.State.ToString());
   }
}

If we use cmd.ExecuteReader() as in #1 above, SQL connection is open regardless of closing SqlDataReader object. The console output of using #1 method is "Open Open." So the connection will be open until your application is closed, which is typical example of connection leak.

If we use CommandBehavior.CloseConnection in ExecuteReader() method as in #2, the output will be "Open Close." That means the connection will be closed when SqlDataReader is closed. This is a good way to avoid connection leak when using SqlDataReader & when you do not have any way of accessing connection object.