title: Multi-layer design issues while using ADO.NET
body: <p>In my multi-layer application, a data access layer reads the database and returns <code>SqlDataReader</code> for collections of objects and <code>SqlDataRecord</code> 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?</p>
  
  <p><strong>Some examples of exceptions that I encounter in my application:</strong><br>
  - <code>Invalid attempt to call MetaData when reader is closed</code><br>
  - <code>There is already an open DataReader associated with this Command which must be closed first</code>  </p>
  
  <p><strong>EDIT</strong>: found an excellent article <a href="http://msdn.microsoft.com/en-us/magazine/cc188717.aspx" rel="nofollow">Contrasting the ADO.NET DataReader and DataSet</a>. Going to use DataSets whenever appropriate (disconnected, buffered approach).</p>