Datareader vs dataset

Confused how to use DataSet and How to use DataReader?






I am trying to give some of basic understanding about and differences between DataReader and DataSet of ADO.NET.





DataReader

The ADO.NET DataReader is used to retrieve read-only(cannot update data back to datasource) and forward-only(cannot read backward/random) 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.

This is a connected architecture: The data is available as long as the connection with database exists.

You need to open and close the connecton manually in code.

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





//opening connection is must

conn.open();

string SQLquery = "SELECT CustomerID, CompanyName FROM dbo.Customers";

SqlCommand cmd = new SqlCommand(SQLquery, conn);

// Call ExecuteReader to return a DataReader

SqlDataReader myReader = cmd.ExecuteReader();

//The Read method of the DataReader object is used to obtain a row from the results of the executed query.

while(myReader.Read())

{

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

}

//Once you're done with the data reader, call the Close method to close a data reader:

myReader.Close();

//close the connection

conn.close();





DataSet

The DataSet is a in-memory representation of data.

It can be used with multiple data sources. That is A single DataSet can hold the data from different data sources holdng data from different databases/tables.

The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables.

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 helps mapping the data in the DataSet to match the data in the data source.

Also, Upon an update of dataset, it allows changing the data in the data source to match the data in the DataSet.

No need to manually open and close connection in code.

Hence, point (8) says that it is a disconnected architecture. Fill the data in DataSet and that's it. No connection existence required

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





string SQLquery = "SELECT CustomerID, CompanyName FROM dbo.Customers";

// create DataSet that will hold your Tables/data

DataSet ds = new DataSet("CustomerDataSet");

//Create SqlDataAdapter which acts as bridge to put the data in DataSet,(data is table available by executing your SQL query)

SqlDataAdapter myAdapter = new SqlDataAdapter(SQLquery, conn);

//fill the dataset with the data by some name say "CustomersTable"

myAdapter.Fill(ds,"CustomersTable");