AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 DataTableReader in ADO.NET 2.0
Posted by Moderator1 on 10/26/2007 10:10:02 AM Category: ADO.NET
Total Views : 55336
Add to my favorites
Email to friend
  
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 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.

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 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. 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.
 
Viewer's Comments
Posted by zvika on 11/8/2007 8:12:44 PM
Excellent!!!
 
Posted by karthi on 11/13/2007 12:42:20 AM
excellent
 
Posted by Faheem Ahmad on 12/5/2007 11:08:38 PM
This article give me idea about using the data table reader instead of data reader in asp .net. This is an excellent article for any one who want to take start. Keep it up.
 
Posted by Arun Thomas on 1/21/2008 12:26:26 AM
Thanks 4 sharing... Xclnt Work!!! Keep it up
 
Posted by VENU on 3/6/2008 5:38:21 AM
GOOD ARTICAL
 
Posted by Akhil on 3/20/2008 1:22:36 AM
nice one dude
 
Posted by yogesh on 3/25/2008 1:23:50 AM
Just wanted to say that this is extremely good . Good one keep it up mate.
 
Posted by Apple on 4/25/2008 5:38:45 AM
nice one
 
Posted by Pavan kumar on 6/3/2008 4:24:07 AM
Many Thanks.......
 
Posted by scandan on 6/21/2008 5:44:13 AM
Thanks man...its a very good information.... Thanks again...
 
Posted by Nanban on 7/21/2008 10:04:10 AM
All the articles in this site are simply superb!. Continue your good work. Many thanks
 
Posted by XYZ on 9/30/2008 5:45:05 AM
Very Informative Article!
 
Posted by Charles Byrne on 6/24/2009 7:39:01 AM
Looks great except there doesn't appear to be a way to select 1 of the 2 tables and iterate through the rows and then select the other and itnerate through the rows, i.e. binding to Grids or other ASP.net controls. I guess you have to check the column count. But if you happen to have 2 tables with different data in them with the same column count you're screwed.
 
Posted by Cool on 9/12/2009 7:54:46 AM
Its nice useful information... Thanks
 
Posted by Samir Moussa on 4/18/2010 12:17:43 PM
first,Thanks very much for your article,i really want to know alot about datatable reader. i Want to write article compare between Ado.net2 objects from performance perspective?
 
Posted by pascal allan on 9/6/2010 8:10:14 AM
was of great help, thnk's
 
Posted by Geo on 10/15/2010 3:58:13 AM
I find this very useful to my projects. Thank for this wonderful article of yours. Kudos!
 
 Rating & Comments
A word 'Excellent' means lot to the author of this article. You can give comments about this article but not the author.
Rate this Article:
Name:
Email Id:  
We never display your email id anywhere.
Comment/Question: Max. 500 letters
FortyThree plus ThirtyNine Equals To
 
Sponsored by
Everything You Know About CSS Is Wrong! - Free 37 Page Preview!
When Good HTML Goes Bad: How You Can Protect Your Web Applications
Introduction to Software Development