Reincarnation of DataTable in ADO.NET 2.0

In previous versions of ADO.NET, if you used a DataSet (Who haven't), you most likely are familiar with slow performance of the DataSet when loading and serializing a large amount of data. Now ADO.NET team has done a fantastic job by taking care of these problems in ADO.NET 2.0 by extending the DataSet and the DataTable classes. 

In previous versions of ADO.NET (1.0 and 1.1), it was all about the DataSet and the DataTable was a slave of the DataSet. In ADO.NET 2.0, the DataTable object celeberates independence from the DataSet and brings much more to the table for developers.

Basic DataTable Operations

Let me start this article by listing new basic features added to the DataTable class in ADO.NET 2.0.

Load Method

In previous version of ADO.NET, we use DataAdapter.Fill method to load data in a DataTable. In ADO.NET 2.0, we can use DataTable.Load method to load data from any DataReader, which implements IDataReader interface. For example, SqlDataReader or even new object called DataTableReader. I discuss DataTableReader later in this article.

Now here important thing to notice is second parameter of DataTable.Load method, which is a LoadOption enumeration listed in Table 1.

Table 1. LoadOption Enumeration

Member	Description
OverwriteChanges	The incoming values for this row will be written to both the current value and the original value versions of the data for each column.
PreserveChanges	The incoming values for this row will be written to the original value version of each column. The current version of the data in each column will not be changed.
Upset	The incoming values for this row will be written to the current version of each column. The original version of each column's data will not be changed.

The code listed in Listing 1 loads data from a DataReader into a DataTable and displays data in a DataGridView control. One thing you may have noticed in this below code, neither I have called DataReader.Read method, nore I am looping through the reader to read the records.

// Create a Connection

using (SqlConnection connection = new SqlConnection(connectionString))

{

     // Open connection

   connection.Open();

   // Create a Command

     using (SqlCommand command = new SqlCommand(Sql, connection))

     {

         // Call ExecuteReader to return a DataReader

         using (SqlDataReader reader = command.ExecuteReader())

         {

             // Create a DataTable

             DataTable table = new DataTable();

             // Fill DataTable

             table.Load(reader, LoadOption.OverwriteChanges);

             // Display data in GridView

             dataGridView1.DataSource = table;                     

          }

       }

}

 

Listing 1. Loading data in a DataTable

Listing 1 generates Figure 1, which loads data in a DataTable object from a DataReader and displays in a DataGridView. If you have not used a DataGridView yet, you would love it. As you can see from Figure 1, the DataGridView control is able to display images and boolean columns without adding any additional code. I will be writing seperate articles on DataGridView control in my forthcoming articles.

Figure 1. Displaying a DataTable in a DataGridView

Merging Multiple DataTables

If you remember the Merge method of the DataSet, it merges two DataSets. In previous version of ADO.NET, the DataTable had no merge capability unless we use DataSet. Now in ADO.NET 2.0, the DataTable supports the Merge method, which merges two DataTables, which makes more sense.

The code listed in Listing 2 merges two DataTables.

using (SqlConnection connection = new SqlConnection(connectionString))

{

    // Create a SqlCommand

    SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", connection);

    // Create a SqlDataAdapter

    SqlDataAdapter adapter = new SqlDataAdapter(cmd);

      // Create a DataTable

    DataTable dtTable1 = new DataTable("Customers");

    // Fill DataTable

    adapter.Fill(dtTable1);

    // Set Primary KEy on DataTable.

      dtTable1.PrimaryKey = new DataColumn[] { dtTable1.Columns["CustomerID"] };

    // Clone DataTable1

    DataTable dtTable2 = dtTable1.Clone();

    // Create and add a row

    DataRow row = dtTable2.NewRow();

    row["CustomerID"] = "NEWCUST1";

    row["CompanyName"] = "Mindcracker Inc";

      dtTable2.Rows.Add(row);

      // Add second row

      row = dtTable2.NewRow();

      row["CustomerID"] = "NEWCUST2";

      row["CompanyName"] = "HiTech Solutions";

      dtTable2.Rows.Add(row);

      // Merge DataTables

      dtTable1.Merge(dtTable2);

      // Display Data

      dataGridView1.DataSource = dtTable1;

}

 

Listing 2. Merging two DataTables

RemotingFormat Property

In previous versions of ADO.NET, the DataSet object serializes as XML even if you specify binary format. However, this behavior is changed in ADO.NET 2.0. Now both DataSet and DataTable objects support true binary format through the RemotingFormat property. The RemotingFormat property of DataSet and DataTable allows us to specify either Binary or XML formats as following:

As you can see from the above code, RemotingFormat propery is SerializaionFormat enumeration, which has Binary and Xml options.

The code listed in Listing 3 reads a DataTable contents in a BinaryFormatter and saves as a text file. However, the detault format of the stream is XML.

// Create a DataTable

DataTable table = new DataTable();

// Fill DataTable

table.Load(reader, LoadOption.OverwriteChanges); 

// Create a BinaryFormatter

BinaryFormatter bf = new BinaryFormatter();

FileStream fs = new FileStream("Data.txt", FileMode.OpenOrCreate);

bf.Serialize(fs, table);

Listing 3. Serialization of a DataTable

The output generates Figure 2.

Figure 2. Serialized DataTable

Now let's change the format of DataTable by setting RemotingFormat property to SerializationFormat.Binary as shown in Listing 4.

// Create a DataTable

DataTable table = new DataTable();

// Fill DataTable

table.Load(reader, LoadOption.OverwriteChanges);

table.RemotingFormat = SerializationFormat.Binary; 

// Create a BinaryFormatter

BinaryFormatter bf = new BinaryFormatter();

FileStream fs = new FileStream("Data.txt", FileMode.OpenOrCreate);

bf.Serialize(fs, table);

Listing 4. Serialize DataTable in Binary Format

New text file generated looks like Figure 3. This file is smaller in size than the previous one (197 KB). If you do not have images in the database, you will notice better size difference.

Figure 3. DataTable serialization in binary

Reading and Writing XML

The DataSet and DataTable classes had no exposure to XML serialization in previous versions of ADO.NET. The way you would read and write XML documents was through XmlDocument and XmlDataDocument classes by using their ReadXml and WriteXml methods. Guess what? Now both, the DataSet and the DataTable classes supports the following methods:

1. ReadXml
2. ReadXmlSchema
3. WriteXml
4. WriteXmlSchema  

The above listed methods allows us to read and write from and to XML documents. Using these methods is pretty similar to the Read and Write methods of XmlDocument and XmlDataDocument methods.

DataTableReader and DataTable.CreateDataReader Method

One of the biggest concern in using a DataReader object in previous versions of ADO.NET was the connected state, which means as long as data is being streamed, the database connection was open and we had to explicitly close the connection when done reading the data.

To solve this problem, ADO.NET 2.0 introduces the DataTableReader object, which is a similar object like other DataReaders such as SqlDataReader and OleDbDataReader but keeps data in disconnected state.

Creating a DataTableReader

The code listed in Listing 5 creates a DataTableReader by using DataTable.CreateDataReader method. 

// Create a DataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(Sql, connection);
// Create a DataTable
DataTable table = new DataTable("Employees");
// Fill a DataTable
adapter.Fill(table);
// Create a DataTableReader
DataTableReader dtReader = table.CreateDataReader();

Listing 5. Creating a DataTableReader

The DataTableReader object is a light weight object in compare to a DataTable or DataSet. Even though the DataTableReader is a light weight object, it still contains all the same row structure as a DataTable. Similar to the DataReader, the DataTableReader supports forward-only navigation. Which means, we can read the rows one by one from first row and loop through them.

Reading Data

The code listed in Listing 6 loops through a DataTableReader rows, similar to a DataReader object.

while (dtReader.Read())
{
// Get data and do something with it
str = dtReader.GetValue(0).ToString();
}

Listing 6. Looping through a DataTableReader

Reading Data from Multiple Tables

If we fill data from a DataSet with multiple tables in it, the DataTableReader will also have multiple resultsets. The code listed in Listing fills data from a DataSet with two Tables and loops through both of the tables' resultsets. The order of the resultsets will be same as order of the tables in the DataSet. The DataTableReader.NextResult method gets the next resultset.

Listing 7 shows how to read and loop through multiple tables using the DataTableReader.

using (SqlConnection connection = new SqlConnection(connectionString))

{

   string str = string.Empty;

  DataSet ds = new DataSet(); 

   // Create the Command and Adapter

   SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", connection);

  SqlDataAdapter adapter = new SqlDataAdapter(cmd); 

   // Create a DataTable and fill it

   DataTable dtCustomers = new DataTable("Customers");

   adapter.Fill(dtCustomers);

   // Add table to the DataSet

   ds.Tables.Add(dtCustomers);

   // Select another table

   adapter.SelectCommand = new SqlCommand("SELECT * FROM Orders", connection);

   // Fill the DataSet

  adapter.Fill(ds, "Orders"); 

    // Create the DataTableReader (it is disconnected)

    using (DataTableReader dtReader = ds.CreateDataReader())

    {

        do

        {

           while (dtReader.Read())

           {

                  // Get data and do something with it

                  str = dtReader.GetValue(0).ToString();

            }

          }

    while (dtReader.NextResult());

      }

}

Listing 7. Looping through multiple resultsets in a DataTableReader 

Summary

In this article, I discussed the new DataTable class and the new features added to this class. I started discussing with various methods and properties added to DataTable and how developers can take advantage of these new features. I also discussed new DataTableReader class and how and why to use it.