ADO.NET DataTableReader

In this article i will explain about ADO.NET DataTableReader.

what is DataTableReader ?
A DataTableReader can contain more than one DataTable(s), in a disconnected mode, as a read-only and forward-only record sets.

Advantages of 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.

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.

Single DataTableReader syntax using ASP.Net and C#

private void FetchDataTableReader() 
{ 
  string sql = "Select * from Customer"; 
  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() + ""); 
    } 
  } 
  else 
    Response.Write("No Data"); 
}  
           
       
 

Two DataTableReader syntax using ASP.Net and C#

private void FetchTwoDataTablesInDataTableReader() 
{ 
  string sql = "Select * from Customer"; 
  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() + ""); 
      } 
    } while (dtr.NextResult()); 
  } 
  else 
  Response.Write("No Data"); 
}