DataReader vs. DataAdapter

Recently I’ve had discussions with a few people about the pros and cons of using DataAdapter.Fill() vs. using the DataReader. First let’s take a look at how DataAdapter.Fill() works. Here is the basic code:

try
{
        DbDataAdapter.QuietOpen(connection1, out state1);
        using (IDataReader reader1 = command.ExecuteReader(behavior | CommandBehavior.SequentialAccess))
        {
            if (data is DataTable)
            {
                    return this.Fill((DataTable) data, reader1);
            }
            return this.Fill((DataSet) data, srcTable, reader1, startRecord, maxRecords);
        }
}
finally
{
        DbDataAdapter.QuietClose(connection1, state1);
}

With the guts looking something like this:

this.dataReader.GetValues(this._readerDataValues);
object[] objArray1 = this.GetMappedValues();
DataRow row1 = this.dataTable.LoadDataRow(objArray1, acceptChanges);

private void MappedValues()
{
      int num1 = this._mappedLength;
      for (int num2 = 0; num2 < num1; num2++)
      {
            this._mappedDataValues[num2] = this._readerDataValues[num2];
      }
}
 

So what is Fill() doing? It’s using a DataReader to deserialize the data stream into a DataSet. How is this different than using a DataReader? The point is, it’s not. There are just more steps to get the data deserialized into the DataSet and the DataSet consumes memory. If all you want is a discrete value from the database, i.e. a single column from a single row, then using a DataReader is going to give you better performance. What if you want the flexibility of holding a collection of rows, being able to sort and filter the rows and serialize the data? You have 2 choices at this point, use a DataSet or write set of custom objects to handle this. If you plan on giving your objects the capability of using INSERT,SELECT, UPDATE and DELETES (CRUD) on the database, you’ve got your work cut out for you. Sean McCormack does a nice job of demonstrating how much work is required in his Codus tool. A fellow blogger Sahil Malik, does a nice job comparing and contrasting the use of the two. Here is a quote from his entry that somes it up nicely:

Even if you built an app that is built on datareaders only, you WILL EVENTUALLY find yourself create your own home grown business object implementation, that has the ability to store data in a disconnected form. And chances are – it will be not as feature rich as a dataset, it will not gain all the free enhancements future versions of .NET bring over, and it will probably be buggy and your developers won’t be willing to learn it even.

One thought on “DataReader vs. DataAdapter

Leave a Reply

Your email address will not be published. Required fields are marked *