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");
Difference :
When the query is executed, the first row is returned to the DataReader via the stream and is stored on the client. The DataReader's Read() method goes to database and reads the next row, stores it on client.
The stream then remains connected to the database, poised to retrieve the next record. So, For the Read() method to retrieve the next row, your connection to database must exist. So, using DataReader you can only handle one row at a time.
It does not store all records at client on query execution as in case of DataSet.
Now When to use a DataReader?
When populating a list or retrieving 10,000 records. When a huge amount of data must be retrieved to a business process, it can take a while to load a DataSet, pass the data to it from the database, and then store it in memory. However, in a Web application where hundreds of users may be connected, scalability would become a problem. If this data is intended to be retrieved and then traversed for business rule processing, the DataReader could speed up the process as it retrieves one row at a time and does not require the memory resources that the DataSet requires.
