Previously I have posted an article: What You Should Dispose When Using ADO.NET. There I describe the common pattern for working with ADO.NET objects. Lets go a step further. I think that it is a very bad practice to return a DataReader object from a data layer to some layer above. The reason for this is that in my opinion you should not return move IDisposable objects between layers. Why? Ask yourself a question: who should dispose of such an object? The client layer where the object is used? Or maybe the data layer where the object originates from?

Given the best practices of object oriented programming (OOP) and encapsulation, you should not have to know the inner workings of the objects that you use. In this case you should not have to know if the DataReader is disposed for you or not etc.

But you have to return some kind of data! So what to return? Some time ago I have created a following pattern for running my queries on the database which in a simple form looks more or less like this:

public IEnumerable<DbDataRecord> ExecuteSelect(string commandText)
{
   using (DbConnection connection = CreateConnection())
   {
      using (DbCommand cmd = CreateCommand(commandText, connection))
      {
         connection.Open();
         using (DbDataReader reader = cmd.ExecuteReader())
         {
            foreach (DbDataRecord record in reader)
            {
               yield return record;
            }
         }
         connection.Close();
      }
   }
}

This way the client for this method ends up with a IEnumerable object that looks and acts like a DataReader but is not IDisposable. In fact when you work with DataReader you actually work with the DbDataRecord objects which the DataReader encapsulates.

Notice also the use of base classes such as DbConnection and DbCommand and not concrete implementations such as SqlCommand. This way you can plug any provider you want to this pattern and in fact this is what I do in the helper methods CreateConnection and CreateCommand:

private DbProviderFactory factory;
private DbProviderFactory Factory
{
   get
   {
      if (factory == null)
      {
         factory = DbProviderFactories.GetFactory(providerInvariantName);
      }
      return factory;
   }
}
private DbConnection CreateConnection()
{
   DbConnection connection = Factory.CreateConnection();
   connection.ConnectionString = connectionString;
   return connection;
}
private DbCommand CreateCommand(string commandText, DbConnection connection)
{
   DbCommand command = Factory.CreateCommand();
   command.Connection = connection;
   command.CommandText = commandText;
   return command;
}

The DbProviderFactories and DbProviderFactory are new feature of ADO.NET 2.0 which are useful for making things more generic not that you couldn't do connection.CreateCommand() before .

Add to del.icio.us