ADO.NET Data Reader Vs DataSet

ADO.NET Data Reader Vs DataSet

For a long time I was thinking of maintaining my own blog, but unfortunately I couldn’t find time for it since I’m always busy with my office work.

Though I’m enjoying my daily life style very much, I always feel I’m not sharing my thoughts and knowledge with others. So here is my first writing in the area of ADO.NET.

Let’s start

When I first heard the name ADO.NET I thought this is the next version of classic ADO. But I was wrong it’s totally new set of components that are optimized to the internet world.

ADO.NET provides two options to retrieve data from an underlying data source. Those are: DataSet and

DataReader

The above are exposed through different providers in ADO.NET. Data providers in .NET framework are used to connect to a database execute commands and retrieve data. This resides as a thin layer between the code base and the data source which optimizes the connectivity.

Lets start with DataReader

There are number of Data Providers in ADO.NET which has its own implementations of DataReader. Commonly they all have implemented the IDataReader interface. If you are sending out the DataReader from a method you can send it out as IDataReader which wraps the actual implementation which solves the problem of many actual implementations when passing the DataReader around different layers of your design.

DataReader is a one way forward only, read only method to read data from a result set. Once you get data into a DataReader it’s very important to remember that the pointer is just before the first record in the result set. You have to call .Read() which moves the pointer to the next location in the result set and returns “true” if it is a valid row.

Common way of reading data from a DaraReader is as follows:

SqlDataReader reader = command.ExecuteReader();

If(reader.HasRows)
{

while(reader.Read())

{
…..
}
}

DataSets

DataSets are database independent in memory data store which can be used to access one or many tables. It can contain one or many DataTables and these tables can contain rows and columns of data.

Data within DataSets can be updated by the user and can persist to the underplaying data store through DataAdapters. DataAdapters are database specific implementations in DataProiders.

Which to be used?

If you need one way read only access to data that we often need in ASP.NET DataReader makes sense. ASP.NET is a stateless technology and you will not hold any data or database connections between requests. ASP.NET allows holding data using session state but it’s not the ideal model to hold custom DataSets between requests and will not make your solution scalable.

The bad side of DataReader is it keeps the database connection open till you finish entire processing of your data. So keep in mind if you have any long processing requirements DataSets may be ideal.

For windows and Smart client applications DataSets are ideal since they maintain their state whole through its life time. The dark side of using DataSet is it holds the entire data store in memory. This may cause high memory requirements if you are loading thousands and millions of data into these DataSets. Data readers are ideal in such cases.

Some Key Points

If you are passing a DataReader around your logical layers there is an inherent problem of closing the Database connection. This is one of the mistakes developers do in their developments which causes application performance and scalable issues. This issue can be solved by using the CommandBehavior.CloseConnection parameter with the ExecureReader which automatically close the database connection when closing the DataReader.

command.ExecuteReader(CommandBehavior.CloseConnection)

DataSets can be easily converted into XML and vise versa, this can be very useful if you want to serialize data across the wire (.ReadXML, .WriteXML methods can be used).

DataSets are accepted by ASP.NET web services and can return DataSets as well. You must be thinking this is strange, though you think it’s limits usability of you web service, it might be a good reason to use those features if you know all your web service consumers are .NET.