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.
![Datatable1.jpg]()
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:
![DataTableImg2.gif]()
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.
![DataTableImg3.gif]()
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.
![DataTableImg4.gif]()
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:
- ReadXml
- ReadXmlSchema
- WriteXml
- 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.