DataReader Vs DataSet?

The ADO.NET DataReader is used to retrieve "read-only" / "forward-only" data from a database. Using the DataReader increases application performance and reduces system overheads. This is due to one row at a time is stored in memory. You create a DataReader by calling Command.ExecuteReader after creating an instance of the Command object.

The following line of code is used to retrieve rows from a data source.

SqlDataReader myReader = myCommand.ExecuteReader();

The Read method of the DataReader object is used to obtain a row from the results of the query, like so.

(myReader.Read())  Console.WriteLine("\t{0}\t{1}", 
myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();

The DataSet is a in-memory representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple data sources. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables. The methods and objects in a DataSet are consistent with those in the relational database model. The DataSet can also persist and reload its contents as XML and its schema as XML Schema definition language (XSD) schema.

The DataAdapter acts as a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter provides this bridge by "mapping Fill". Which changes the data in the DataSet to match the data in the data source. Upon this an Update occurs, which changes the data in the data source to match the data in the DataSet.

On connecting to a Microsoft ?sqlserver database, an increase in overall performance can be obtained by using the SqlDataAdapter along with its associated SqlCommand and SqlConnection. For other OLE DB-supported databases, use the DataAdapter with its associated OleDbCommand and OleDbConnection.

Written by Sandeep Mogulla, Webmaster at www.startvbdotnet.com

Index