DataTableReader Class in ADO.NET
Eventhough DataTableReader
is an excellent concept and got lot of advantages when compared to
SqlDataReader, it is not yet widely used in web application development. Even I
have never used the DataTableReader Class in any of my application before. But,
the combination of DataTable + DataReader (I.e. connected + disconnected
architecture) is the most impressive thing about DataTableReader.
Connected
and disconnected architecture:
ADO.NET supports two
different programming environments: connected and disconnected.
The connected environment
provides forward-only, read-only access to data in the data source and the
ability to execute commands against the data source. The connected classes
provide a common way to work with connected data regardless of the underlying
data source. They include Connection, Command, DataReader, Transaction,
ParameterCollection, and Parameter classes.
The disconnected
environment allows data retrieved from the data source to be manipulated and
later reconciled with the data source. The disconnected classes provide a common
way to work with disconnected data regardless of the underlying data source.
They include the DataSet, DataTable, DataColumn, DataRow, Constraint,
DataRelationship, and DataView classes.
DataReader:
As we know; DataReader
supports connected architecture and provides connected forward-only, read-only
access to the data source. It is optimized for speed. The DataReader is
instantiated through a Command object.
DataTable:
DataTable allows
disconnected data to be examined and modified through a collection of DataColumn
and DataRow classes. The DataTable allows constraints such as foreign keys and
unique constraints to be defined using the Constraint class.
DataTableReader:
SqlDataReader are much
faster than DataSet and consume less memory. But the major drawback of using
SqlDataReader is that it always required an open connection to operate, that is,
it is connection oriented. Hence we needed to explicitly close the database
connections when we were done using it.
DataTableReader class has
been developed similar to it but with one exception – it works in a disconnected
mode. Clearly opening and closing of database server connection is taken care by
the DataTableReader itself. The iteration of rows is done from the cache. The
cached data can be modified while the DataTableReader is active, and the reader
automatically maintains its position.
A simple
DataTableReader:
DataTableReader can be
created from any DataTable’s CreateDataReader method. Check out the Example
below to create a DataTableReader.
private void CreateDataTableReader()
{
string sql = "Select *
from Product";
SqlDataAdapter da = new
SqlDataAdapter(sql,”ConnectionString”);
DataTable dt = new DataTable();
da.Fill(dt);
DataTableReader dtr = dt.CreateDataReader();
if
(dtr.HasRows)
{
while (dtr.Read())
{
Response.Write(dtr[“ProductName”].ToString() + "<br/>");
}
}
else
Response.Write("No Records");
}
A
DataTableReader with more than one DataTables:
One of the nice features of
DataTableReader is that it can contain more than one DataTables, as read-only
and forward-only recordsets. When you load more than one DataTables in a
DataTableReader, it is really faster to iterate and it will automatically deals
with the unwanted records during the iteration. You can load bunches of
DataTables by creating an object of DataTableReader to contain an array of
DataTables.
private void CreateDataTableReader()
{
string sql = "Select *
from Product";
SqlDataAdapter da = new
SqlDataAdapter(sql,”ConnectionString”);
DataTable dtProduct = new
DataTable();
da.Fill(dtProduct);
string sql1 = "Select * from Category";
SqlDataAdapter da1 = new
SqlDataAdapter(sql1,”ConnectionString”);
DataTable dtCat = new DataTable();
da1.Fill(dtCat);
DataTableReader dtr = new DataTableReader(new DataTable[] {dtProduct,
dtCat});
if (dtr.HasRows)
{
do
{
while (dtr.Read())
{
Response.Write(dtr[1].ToString() + "<br/>");
}
} while (dtr.NextResult());
}
else
Response.Write("No
Records");
}
Summary:
The DataTableReader works
much like any other data reader, such as the SqlDataReader, except that the
DataTableReader provides for iterating over rows in a DataTable. In other words,
it provides for iterating over rows in a cache. The cached data can be modified
while the DataTableReader is active, and the reader automatically maintains its
position.
When you create a
DataTableReader from a DataTable, the resulting DataTableReader object contains
one result set with the same data as the DataTable from which it was created,
except for any rows that have been marked as deleted. The columns appear in the
same order as in the original DataTable. The structure of the returned result is
identical in schema and data to the original DataTable. A DataTableReader that
was created by calling the GetDataReader() method of a DataSet object contains
multiple result sets if the DataSetcontains more than one table. The results are
in the same sequence as the DataTableobjects in the DataTableCollection of the
DataSet object.
Reference:
DataTableReader Class (System.Data)