Working with the New Improved DataTable

Working with the New Improved DataTable

The DataSet has always been at the core of previous versions of ADO.NET, providing an in-memory representation of relational data including keys, constraints, data relations, and even limited querying capability. The problem many developers had with the DataSet is that quite often they only needed to work with a single table at a time. In these situations, the DataSet provided too much functionality. The problem with this was that quite often, developers couldn't get around the fact that they needed to use the DataSet; the DataTable class didn't work very well outside the context of a DataSet.

The new version of ADO.NET takes this into consideration by promoting the DataTable to a first-class citizen. The DataTable can read and write its own data to files without having to reside within a DataSet. In addition, you can create a DataReader on top of the data contained within a single table.

Loading and Saving DataTables Using XML

With ADO.NET 2.0, you can now read and write the contents of a DataTable using XML. In previous versions of ADO.NET, you had to place a table in a DataSet and then call the ReadXml() and WriteXml() methods on the DataSet to get XML persistence.

Now the DataTable class has its own ReadXml() and WriteXml() methods. One thing you might have to watch out for is that although the current documentation on MSDN indicates that schema inference is possible on a DataTable from XML data, attempting to infer schema at runtime from XML throws an exception indicating that it is not supported on the DataTable class. To get around this, the sample shown in Listing 18.1 uses the XmlWriteMode.WriteSchema option to include the schema in the top of the document.

Listing 18.1. Reading and Writing XML DataTable Contents

using System; using System.Data; using System.IO; using System.Collections.Generic; using System.Text; namespace TableXml { class Program { static DataTable dt; static void Main(string[] args) {     dt = new DataTable("Customers");     if (File.Exists("Customers.xml"))     {         dt.ReadXml("Customers.xml");         Console.WriteLine("{0} Customers Found", dt.Rows.Count);         foreach (DataRow row in dt.Rows)         {             Console.WriteLine("[{0}] {1} {2}", row["CustomerID"], row["FirstName"], row["LastName"]);         }     }     else         CreateCustomers();     Console.Write("New Customer:");     string custName = Console.ReadLine();     if (custName == string.Empty) return;     string[] cust = custName.Split(' ');     dt.Rows.Add(new object[] { dt.Rows.Count + 1, cust[0], cust[1] });     dt.WriteXml("Customers.xml", XmlWriteMode.WriteSchema);     Console.ReadLine(); } public static void CreateCustomers() {     dt.Columns.Add("CustomerID", typeof(int));     dt.Columns.Add("FirstName", typeof(string));     dt.Columns.Add("LastName", typeof(string));     dt.Rows.Add(new object[] { 1, "Kevin", "Hoffman" });     dt.Rows.Add(new object[] { 2, "John", "Doe" }); } } } 

The preceding code looks for the Customers.xml file. If it finds the file, it loads the contents into a DataTable; otherwise, it creates a new file with a couple of sample rows. Then the code prompts the user for a new customer, adds that customer as a row, and saves the data back out to the Customers.xml file with the schema information included. If you have any experience using the WriteXml() and ReadXml() methods on the DataSet class, the preceding code should seem pretty straightforward. It produces XML in the following format:

<?xml version="1.0" standalone="yes"?> <NewDataSet>   <xs:schema  xmlns=""     xmlns:xs="http://www.w3.org/2001/XMLSchema"     xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">     <xs:element name="NewDataSet" msdata:IsDataSet="true"        msdata:MainDataTable="Customers" msdata:UseCurrentLocale="true">       <xs:complexType>         <xs:choice minOccurs="0" maxOccurs="unbounded">           <xs:element name="Customers">             <xs:complexType>               <xs:sequence>                 <xs:element name="CustomerID" type="xs:int" minOccurs="0" />                 <xs:element name="FirstName" type="xs:string" minOccurs="0" />                 <xs:element name="LastName" type="xs:string" minOccurs="0" />               </xs:sequence>             </xs:complexType>           </xs:element>         </xs:choice>       </xs:complexType>     </xs:element>   </xs:schema>   <Customers>     <CustomerID>1</CustomerID>     <FirstName>Kevin</FirstName>     <LastName>Hoffman</LastName>   </Customers>   <Customers>     <CustomerID>2</CustomerID>     <FirstName>John</FirstName>     <LastName>Doe</LastName>   </Customers>   <Customers>     <CustomerID>3</CustomerID>     <FirstName>Bobby</FirstName>     <LastName>John</LastName>   </Customers> </NewDataSet> 

Using the New DataTableReader Class

The new DataTableReader class works just like any of the other DataReader classes that are available in ADO.NET. Like a SqlDataReader, the DataTableReader exposes fast, forward-only, read-only access to the underlying data. Whereas the SqlDataReader exposes data from a SQL Server database, the DataTableReader simply exposes data from an underlying DataTable.

The code in Listing 18.2 is a quick illustration of how to use a DataTableReader. Fortunately, if you know how to use any of the data readers provided by ADO.NET, you will be familiar with how to use the DataTableReader class.

Listing 18.2. Using the DataTableReader

using System; using System.Data; using System.Collections.Generic; using System.Text; namespace TableReaderDemo { class Program { static void Main(string[] args) {     DataTable dt = new DataTable("Customers");     // load data from previous demo     dt.ReadXml(@"..\..\..\..\TableXml\TableXml\bin\debug\Customers.xml");     DataTableReader dtr = dt.CreateDataReader();     while (dtr.Read())     {         Console.WriteLine("[{0}] {1} {2}",             (int)dtr["CustomerID"], (string)dtr["FirstName"], (String)dtr["LastName"]);     }     Console.ReadLine(); } } }