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

Page 2 / 2

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)