Multi-layer design issues while using ADO.NET

In my multi-layer application, a data access layer reads the database and return开发者_开发知识库s SqlDataReader for collections of objects and SqlDataRecord for a single object to a business layer. A business layer has constructors that initialize either a SqlDataReader or SqlDataRecord. That seemed a natural design to follow, however I run into multiple problems with readers going out of scope and multiple concurrent readers. What is a better way to tackle this design ? Is it better to make a data layer return a dictionary of fields and dispose readers immediately? And is it better to use DataSets in place where streaming of data is not required?

Some examples of exceptions that I encounter in my application:

- Invalid attempt to call MetaData when reader is closed

- There is already an open DataReader associated with this Command which must be closed first

EDIT: found an excellent article Contrasting the ADO.NET DataReader and DataSet. Going to use DataSets whenever appropriate (disconnected, buffered approach).

You definitely want to close the SqlDataReader connections as soon as possible. The SqlDataReader is very fast but when they remain open you are using up one of you connection pool connections. This could easily put you in a condition where you have resource issues, as you mentioned.

I would not, as you suggested, pass a SqlDataReader to the business tier. I would either fill up a DataSet and pass that or construct a custom data entity object and return that to the business tier.

You're polluting your business layer by allowing it to work with ADO.NET classes. Encapsulate your "Sql*" code entirely in the database layer, and use CRUD operations to manage update/inserts.

class BusinessObject
{
   public BusinessData GetBusinessData()
   {
        //...
        DataSet ds = DatabaseLayer.GetData(businessObjParams);
        return new BusinessData(ds);
   }
}

static class DatabaseLayer 
{
   public DataSet GetData(BusinessParams params)
   {
        //... open your connection, get the data, fill DataSet.
        //... close connection
        return dataSet;
   }
}

Why not return classes from your DAL? These can either be business objects, or classes that you map data onto directly into the DAL, then onto the business objects, in which case AutoMapper might be useful.

The exceptions really look like design issues to me. The 2nd can be solved using MARS, though I recommend you consider other options first- you should be closing DataReaders/connections etc as soon as possible.

Mapping onto POCOs has the added the option that you easily switch to other data access technologies like LINQ to SQL, NHibernate, Entity Framework etc.

A popular approach thesedays is to use the repository pattern.