DataTable Enhancements in ADO.NET 2.0

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

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

Copy an exact copy of a DataTable into another

The Copy() method of the DataTable class in ADO.NET 2.0 makes an exact copy of a DataTable into another; retaining the entire schema and data. This is illustrated in the code snippet below:

string connectionString = ...; //Some connection string
DataTable dataTable = new DataTable("Employee");
SqlConnection sqlConnection = new SqlConnection(connectionString)) SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandText = "SELECT empCode, empName from employee"; sqlConnection.Open();
dataTable.Load(sqlCommand.ExecuteReader(CommandBehavior.CloseConnection));
DataTable cloneTable = dataTable.Copy();

Merging multiple DataTables into one DataTable

Earlier, if we wanted to merge the contents of one DataTable with another, we had to use the Merge () method of the DataSet class as the DataTable class in ADO.NET 1.1 did not expose any Merge() method. However, with ADO.NET 2.0, things have changed; more flexibility has been added to the DataTable class with a whole lot of new features that we have been discussing so far.

The Merge () method of the DataTable class in ADO.NET 2.0 allows us to merge the contents of one DataTable with another. As an example, let there be two DataTable instances, i.e., dt1 and dt2. We can merge the contents of the DataTable dt2 with that of dt1 as shown in the code snippet below:

dt1.Merge(dt2);

In the earlier version of ADO.NET (ADO.NET 1.1) if we had to merge two DataTable instances, they had to be wrapped inside a DataSet instance to facilitate the above.

Seamless Support for XML

Unlike its previous counterpart, the DataTable class in ADO.NET 2.0 contains the ReadXml(), WriteXml() , ReadXmlSchema() and the WriteXmlSchema() methods for performing basic XML operations such as reading or writing to XML, etc. These methods were absent in the DataTable class in ADO.NET 1.1.

References

Please refer to the following links for further reference on this topic:

New Features in ADO.NET 2.0

What's New in ADO.NET (MSDN)

DataView Sorting Filtering and DataBinding in ADO.NET 2.0 - Converting DataView to Table - ADO.NET Tutorials

DataSet and DataTable in ADO.NET 2.0 (MSDN)