This article contain difference between Dataset and DataReader and also Detailed explaination of Functionality & Features provided by DataReader/DataSet.
I have found lots of article who talk about Dataset and DataReader but the article written by John Papa in MSDN Magazine contains very useful explaination on this topic. So I have used that information to write this article & tried to give you the best.
Here Points to ponder contain the difference between Dataset and DataReader.
Points to ponder:
- DataSet Object works under Disconnected mode, While DataReader Object has Connected mode.
- DataSet Object has Read/Write access, While DataReader Object has Read-only access.
- DataSet Object Supports multiple tables from different databases, While DataReader Object Supports a single table based on a single SQL query of one database.
- DataSet Object has Greater overhead to enable additional features, While DataReader Object being Lightweight object with very little overhead.
- DataSet Object is Bind to multiple controls, While DataReader Object is Bind to a single control.
- DataSet Object supports Forward and backward scanning of data, While DataReader Object supports Forward-only scanning of data.
- DataSet Object has Slower access to data, While DataReader Object has Faster access to data.
- DataSet Object is Supported by Visual Studio .NET tools, While DataReader Object Must be manually coded.
Connected Mode:
A DataReader is a stream of data that is returned from a database query. When the query is executed, the first row is returned to the DataReader via the stream. The stream then remains connected to the database, poised to retrieve the next record. The DataReader reads one row at a time from the database and can only move forward, one record at a time.
As the DataReader reads the rows from the database, the values of the columns in each row can be read and evaluated, but they cannot be edited.
Unlike the DataSet, the DataReader is not implemented directly in the System.Data namespace.
Rather, the DataReader is implemented through a specific managed provider’s namespace such as System.Data.SqlClient.SqlDataReader.
Because all DataReaders, including the OleDbDataReader, the SqlDataReader, and other managed provider’s DataReaders implement the same IDataReader interface, they should all provide the same base set of functionality.
Each DataReader is optimized for a specific data provider. If the database you are developing against has a managed provider for ADO.NET, then you should take advantage of it. Otherwise, you can use the System.Data.OleDb or the System.Data.Odbc namespaces, which expose more generic managed providers that can access a variety of data sources.
If you are developing against SQL Server™ or Oracle, it would be more efficient to use the provider that was made specifically for these databases. In this column, I will query the SQL Server Northwind database using the System.Data.SqlClient namespace.
The SqlDataReader can only retrieve one row at a time from the data source and in order for it to get the next record, it has to maintain its connection to the data source.
The DataSet, however, doesn’t need to know about where it gets its data.
The DataReader can only get its data from a data source through a managed provider.
The DataSet can also get its data from a data source via a managed provider, but the data source can also be loaded manually, even from an XML file on a hard drive.
In ASP.NET, DataReader objects can be used for more robust situations such as binding themselves to an ASP.NET DataGrid or a DropDownList server control.
The following code demonstrates how to retrieve a list of products from the Northwind database using a SqlDataReader object:
1: string sSQL = "SELECT * FROM Products";
2: string sConnString =
3: "Server=(local);Database=Northwind;Integrated Security=SSPI;";
4: using (SqlConnection oCn = new SqlConnection(sConnString))
5: {
6: SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);
7: oSelCmd.CommandType = CommandType.Text;
8: oCn.Open();
9: SqlDataReader oDr = oSelCmd.ExecuteReader();
10: DataGrid1.DataSource = oDr;
11: DataGrid1.DataBind();
12: }
Now Read the Data using
1: SqlDataReader oDr = oCmd.ExecuteReader();
2: while(oDr.Read()) {
3: Console.WriteLine(oDr[0]);
4: }
Supporting Multiple Resultsets
The DataReader supports access to multiple resultsets, one at a time, in the order they are retrieved. This code is easily modified to handle multiple resultsets. The following code retrieves a SqlDataReader and loops through its rows, again writing the first column’s value for each row to the console:
1: SqlDataReader oDr = oCmd.ExecuteReader();
2: do {
3: while(oDr.Read())
4: {
5: Console.WriteLine(oDr[0]);
6: }
7: Console.WriteLine(oDr[0]);
8: }
9: while(oDr.NextResult());
Once all of the rows from the first resultset are traversed, the rowset from the second query is retrieved and its rows are traversed and written. This process can continue for several resultsets using a single SqlDataReader.
The Read method of the SqlDataReader loads the next record so that it can be accessed and moves the position of the cursor ahead. It returns a Boolean value indicating the existence of more records. This feature can help circumvent a common problem in classic ADO development: the endless loop.
The DataReader in the previous code sample shows how to get the value for a column from the DataReader using its ordinal index position.
Disconnected Mode:
The DataSet is the main data storage tool in the ADO.NET disconnected architecture. Unlike the DataReader, the DataSet is not connected directly to a database through a Connection object when you populate it.
Instead, to fill a DataSet from a database you first create a DataAdapter object for the provider and associate it with a SqlConnection object. Then the SqlDataAdapter can broker the data retrieval for the DataSet by issuing a SqlCommand against the database through the SqlConnection, retrieving the data, and filling the DataSet.
You can think of the SqlDataAdapter as a bridge between the connected and disconnected objects.
One of its purposes is to serve as the route for a rowset to get from the database to the DataSet.
For example, when the SqlDataAdapter’s Fill method is executed it opens its associated SqlConnection object (if not already open) and issues its associated SqlCommand object against the SqlConnection. Behind the scenes, a SqlDataReader is created implicitly and the rowset is retrieved one row at a time in succession and sent to the DataSet.
Once all of the data is in the DataSet, the implicit SqlDataReader is destroyed and the SqlConnection is closed.
The following code shows how a DataSet can be filled from the Products table of the Northwind database:
1: string sSQL = "SELECT * FROM Products";
2: string sConnString =
3: "Server=(local);Database=Northwind;Integrated Security=SSPI;";
4: SqlDataAdapter oDa = new SqlDataAdapter();
5: DataSet oDs = new DataSet();
6: using(SqlConnection oCn = new SqlConnection(sConnString))
7: {
8: SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);
9: oSelCmd.CommandType = CommandType.Text;
10: oDa.SelectCommand = oSelCmd;
11: oDa.Fill(oDs, "Products");
12: }
The DataSet can read and load itself from an XML document as well as export its rowset to an XML document. Because the DataSet can be represented in XML, it can be easily transported across processes, a network, or even the Internet via HTTP.
Unlike the DataReader, the DataSet is not read-only.
A DataSet can be modified, and rows can be added or deleted. Changes to a DataSet can be sent to the database via a managed provider’s objects.
Another key difference between the DataSet and the DataReader is that the DataSet is fully navigable. Its rows can be traversed forward or backward. The DataReader can be traversed forward only.
In addition, a DataSet is highly flexible in that its DataTable objects can be filtered vertically or horizontally and they can be sorted or even searched.
The DataSet is independent of any one data provider as it relies on a DataAdapter specific to each provider to broker the data between the DataSet and the database.
Not only can the DataSet be loaded from XML, it can also be loaded manually.
1: //-- Create the table
2: DataTable oDt = new DataTable("Employees");
3: DataRow oRow;
4: oDt.Columns.Add("EmployeeID", System.Type.GetType("System.Int32"));
5: oDt.Columns.Add("FirstName", System.Type.GetType("System.String"));
6: oDt.Columns.Add("LastName", System.Type.GetType("System.String"));
7: oDt.Constraints.Add("PK_Employees", oDt.Columns["EmployeeID"], true);
8: oDt.Columns["EmployeeID"].AutoIncrement = true;
9: oDt.Columns["EmployeeID"].AutoIncrementSeed = -1000;
10: oDt.Columns["EmployeeID"].AutoIncrementStep = -1;
11: oDs.Tables.Add(oDt);
12:
13: //-- Add the rows
14: oRow = oDs.Tables["Employees"].NewRow();
15: oRow["FirstName"] = "Haley";
16: oRow["LastName"] = "Smith";
17: oDs.Tables["Employees"].Rows.Add(oRow);
18: oRow = oDs.Tables["Employees"].NewRow();
19: oRow["FirstName"] = "Madelyn";
20: oRow["LastName"] = "Jones";
21: oDs.Tables["Employees"].Rows.Add(oRow);
22:
23: //-- Bind it to a DataGrid
24: grdEmployees.DataSource = oDs.Tables["Employees"];
Because the DataSet is disconnected, its use can reduce the demand on database servers. It does, however, increase the memory footprint in the tier where it is stored, so be sure to account for that when designing around a DataSet as your data store.
Scaling up on the middle tier using parallel servers and load balancing is a common way to handle the increased load so that session-based information can be stored in objects such as the DataSet.
When to Use the DataSet
The DataSet’s disconnected nature allows it to be transformed into XML and sent over the wire via HTTP if appropriate. This makes it ideal as the return vehicle from business-tier objects and Web services.
DataSet objects are a good choice when the data must be edited or rows added or deleted from the database.
Since the DataSet can be traversed in any direction, all of these features are available. This flexibility also makes the DataSet an easy choice when the situation calls for multiple iterations of the rowset.
If a rowset is intended to be bound to more than one read-only ASP.NET server control, you should consider using a DataSet instead.
The DataSet also works well when a rowset must be persisted between page calls to the Session or Cache objects.
When to Use the DataReader
Binding a DataReader to a DropDownList or even a read-only DataGrid in ASP.NET works well as the data can be retrieved and displayed in the list but does not need to be persisted for editing.
When populating a list or retrieving 10,000 records for a business rule.
When a huge amount of data must be retrieved to a business process, even on a middle tier, it can take a while to load a DataSet, pass the data to it on the business tier from the database, and then store it in memory. The footprint could be quite large and with numerous instances of it running (such as 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.
When output or return parameters need to be retrieved, a DataReader will not allow you to get them until the DataReader and its connection are closed. If data must be bound to a read-only list in a Web Form, a DataReader is a very good choice.
Reference:
Ramani Sandeep 