DataTableReader in ADO.NET

Introduction
This article explores the concept of using DataTableReader in the place of SqlDataReader and DataTable, also explains the methods of creating, reading and using the DataTableReader in the web applications.

In this fast and furious world, everyone needs everything to be faster. Once upon a time Pentium MMX was considered as faster system, but now we don’t accept even Pentium Dual core as the fastest system. If this is the situation for a computer system, then what will be the situation for the data we managed in our web application? Readers need data to be load faster into their webpage irrespective of the size of data that page contains. So someone in someway needs a solution to overcome this problem. Let us go straight to the concept.

What is DataTableReader? As briefly explained in MSDN, a DataTableReader obtains the contents of one or more DataTable objects in the form of one or more read-only, forward-only result sets. As the name suggests, it is a combination of both DataTable and SqlDataReader. In a DataTable, we can store a single database table records, with all constraints, in a disconnected mode from the database server. And we can perform all sorts of database manipulations in it. A SqlDataReader can contain single database table records, with read-only and forward-only record sets, for which we need an active connection with the database server. And we cannot perform other database manipulations in a SqlDataReader. A DataTableReader can contain more than one DataTable(s), in a disconnected mode, as a read-only and forward-only record sets.

Advantages of using DataTableReaderSqlDataReader 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.In ADO.NET 2.0, 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.

Creating a simple DataTableReader

DataTableReader can be created from any DataTable’s CreateDataReader method. Let us see the syntax to create a simple DataTableReader and iterate the records in it.

private void FetchDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dt = new DataTable(); da.Fill(dt);
DataTableReader dtr = dt.CreateDataReader();
if (dtr.HasRows)
{
while (dtr.Read())
{
Response.Write(dtr[“Cus_Name”].ToString() + "<br/>");
}
}
else
Response.Write("No Data");
}

From the above block of statement, you are familiar with the first 4 lines of code. The next line, we are creating a DataTableReader object, by using CreateDataReader method. Then we can check if rows exists in the DataTableReader with HasRows and the Read method will advances the DataTableReader to the next record. The output is as follows

Alex
Flintoff
Mark
Jeniffer
Christiana
Leverlock

Creating a DataTableReader with more than one DataTablesOne 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. Let us see the sample code to create it.

private void FetchTwoDataTablesInDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus); string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry); DataTableReader dtr = new DataTableReader(new DataTable[] {dtCus, dtCountry});
if (dtr.HasRows)
{
do
{
while (dtr.Read())
{
Response.Write(dtr[1].ToString() + "<br/>");
}
} while (dtr.NextResult());
}
else
Response.Write("No Data");
}

In the above block of code, we are loading two datatables to the DataTableReader. So we are creating two datatables from a customer and country tables. Then we are creating an object of the DataTableReader from an array of DataTables. The method NextResult helps to advances between the datatables in the DataTableReader. One thing to be noted is that we are using the column index to retrieve the data instead of using column name, since it contains different tables with different field names. The result for the above code will be as follows

Alex
Flintoff
Mark
Jeniffer
Christiana
Leverlock
Andorra
United Arab Emirates
Afghanistan
Antigua and Barbuda
Anguilla
Albania
Armenia
Netherlands Antilles
Angola
Antarctica

Iterating DataTableReader with Multiple DataTables and display all Columns In the above section, we pick only a single column from the DataTableReader. We can also iterate through all the columns in the DataTableReader with the help of FieldCount property. The code is as follows

private void FetchTwoDataTablesInDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus); string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry); DataTableReader dtr = new DataTableReader(new DataTable[] {dtCus, dtCountry});
if (dtr.HasRows)
{
do
{
while (dtr.Read())
{
for (int i = 0; i < dtr.FieldCount; i++)
{
Response.Write(dtr[i]);
}
Response.Write("<br/>");
}
} while (dtr.NextResult());
}
else
Response.Write("No Data");
}

Run the application will result the following output.

1 Alex M Oxford Alabama Retailer
2 Flintoff M Southside Alabama Retailer
3 Mark M Avon Park Florida Retailer
4 Jeniffer F Destin Florida Wholesale
5 Christiana F Chester New York Retailer
6 Leverlock M Baytown Texas Wholesale


1 Andorra AD
2 United Arab Emirates AE
3 Afghanistan AF
4 Antigua and Barbuda AG
5 Anguilla AI
6 Albania AL
7 Armenia AM
8 Netherlands Antilles AN
9 Angola AO
10 Antarctica AQ

Binding DataTableReader with GridView control

Another great feature of the DataTableReader class is that you can use it as the data source to populate Dataset or DataTable information into an Asp.Net 2.0 GridView control in a very easy way. This is done by using the Load method of the DataTableReader. Let us see the sample code for this.

private void LoadGridViewWithDataTableReader()
{
DataSet ds = new DataSet(); string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus);
ds.Tables.Add(dtCus); string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry);
ds.Tables.Add(dtCountry); DataTable dtGrid = new DataTable();
DataTableReader dtr = new DataTableReader(ds.Tables[0]);
dtGrid.Load(dtr); GridView1.DataSource = dtGrid;
GridView1.DataBind();
}

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. Sure this article will bring and give the knowledge of DataTableReader to the readers and encourage them to use it.