articles >> asp.net / ado.net >> DataTable Enhancements in ADO.NET 2.0

DataTable Enhancements in ADO.NET 2.0

By : Joydip Kanjilal Feb 08, 2007

ADO.NET 2.0 has added many new powerful features to the DataTable class. Some of these features include the ability to load a DataReader instance into a DataTable instance by using the Load method of the DataTable class, the DataTableReader, built-in serialization support, etc. This article highlights these new features added to the DataTable class in ADO.NET 2.0.

Support for Serialization

In ADO.NET 2.0 a DataTable instance can be serialized by itself. Hence, we do not need to wrap a Datatable instance within a DataSet instance to expose it through Web services or other technologies that require serialized data.
The RemotingFormat property of the DataTable class can be used to specify the Serialization format. The available Serialization Format options are Binary and Xml. Refer to the code example given below:

DataTable dataTable = new DataTable();
//Some code
datatable.RemotingFormat = SerializationFormat.Binary;
//Some code

Why a DataTableReader?

DataReaders are much faster than DataSets and consume less memory. 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 database 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. According to MSDN, “The DataTableReader obtains the contents of one or more DataTable objects in the form of one or more read-only, forward-only result sets. 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”.

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());
}