use DataTableReader
DataReaders are much faster than DataSets and consume less http://www.sql-server-performance.com/jk_ado_2_datable.asp#. However, the major drawback of using DataReaders in the earlier versions of ADO.NET was that it always required an open connection to operate, i.e., it was connection oriented. Hence we needed to explicitly close the http://www.sql-server-performance.com/jk_ado_2_datable.asp# connections when we were done using it. With ADO.NET 2.0, a DataTableReader class has been introduced that is similar to other data readers but with one exception – it works in a disconnected mode.
Creating a DataTableReader
The CreateDataReader method of the DataTableReader class can be used to create a DataTableReader. The following listing shows how this is done.
public DataTableReader GetDataTableReader (string connectionString)
{
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection .Open()
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(“Select * from States”, sqlConnection);
DataTable dataTable = new DataTable ("States");
sqlDataAdapter.Fill(dataTable);
DataTableReader datatableReader = dataTable.CreateDataReader();
sqlConnection.Close();
return datatableReader;
}
The DataTableReader is a light-weight, forward-only set of data that maintains the same structure as a DataTable, i.e., it exposes the same rows and columns as the DataTable. The following listing illustrates how we can read data using a DataTableReader instance.
String connectionString = …; //Some connection string to connect to the database
DataTableReader dataTableReader = GetDataTableReader(connectionString);
Console.WriteLine(“Displaying the codes for all the states:--“);
while (dataTableReader.Read())
{
Console.WriteLine(DataTableReader[“codeID”].ToString());
}
Ability to work with multiple tables seamlessly
When we create a DataTableReader from a DataSet that contains multiple tables, the DataTableReader instance would also contain multiple resultsets, one per each DataTable. We can iterate through all these resultsets using the NextResult method. This is shown in the code example that follows.
String connectionString = …; //Some connection string to connect to the database
DataTableReader dataTableReader = GetDataTableReader(connectionString);
while(dataTableReader.NextResult()) // Iterate through all the resultsets
{
while(dataTableReader.Read()) //Iterate through all the records in a resultset
{
//Some code
}
}
Loading Data to a DataTable from a DataReader
The Load method of the DataTable class of ADO.NET 2.0 can be used to load a DataReader instance directly into a DataTable instance. The following listing shows how it can be accomplished.
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
sqlConnection.Open();
SqlCommand command = new SqlCommand(“Select name, population, area from States”, sqlConnection);
SqlDataReader sqlDataReader = command.ExecuteReader();
DataTable dataTable = new DataTable ();
dataTable.Load(sqlDataReader, LoadOption.OverwriteChanges);
reference
http://www.sql-server-performance.com/jk_ado_2_datable.asp