Saving and Loading a DataSet from XML

Problem

You need to save a DataSet as an XML file and create a DataSet from an XML file.

Solution

Use the XmlTextWriter and XmlTextReader classes.

The sample code contains three event handlers:

Write Button.Click

Creates a DataSet containing the Orders and Order Details tables from Northwind and a relation between the two tables. The XML schema and data for the DataSet is written both to a file and to a text box on the form.

Read Button.Click

Creates a DataSet and reads in schema and data from a file containing a previously serialized XML. The XML is written from the DataSet to a stream and displayed.

Clear Button.Click

Clears the data grid and the result text box.

The C# code is shown in Example 8-3.

Example 8-3. File: XmlFileForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Text;
using System.IO;
using System.Xml;
using System.Xml.Schema;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String ORDERS_TABLE = "Orders";
private const String ORDERDETAILS_TABLE = "OrderDetails";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
 "Orders_OrderDetails_Relation";

// Field name constants
private const String ORDERID_FIELD = "OrderID";

// . . . 

private void writeXmlButton_Click(object sender, System.EventArgs e)
{
 DataSet ds = new DataSet();
 
 SqlDataAdapter da;

 // Fill the Order table and add it to the DataSet.
 da = new SqlDataAdapter("SELECT * FROM Orders",
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 DataTable orderTable = new DataTable(ORDERS_TABLE);
 da.FillSchema(orderTable, SchemaType.Source);
 da.Fill(orderTable);
 ds.Tables.Add(orderTable);

 // Fill the OrderDetails table and add it to the DataSet.
 da = new SqlDataAdapter("SELECT * FROM [Order Details]",
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 DataTable orderDetailTable = new DataTable(ORDERDETAILS_TABLE);
 da.FillSchema(orderDetailTable, SchemaType.Source);
 da.Fill(orderDetailTable);
 ds.Tables.Add(orderDetailTable);

 // Create a relation between the tables.
 ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
 ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
 ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
 true);

 // Bind the default view of the Orders table to the grid.
 resultDataGrid.DataSource = ds.Tables[ORDERS_TABLE].DefaultView;

 // Write the XSD schema and data to a file.
 // Display file dialog to select XML file to write.
 SaveFileDialog sfd = new SaveFileDialog();
 sfd.InitialDirectory = System.IO.Path.GetTempPath();
 sfd.Filter = "XML Files (*.xml)*.xmlAll files (*.*)*.*";
 sfd.FilterIndex = 1;

 if (sfd.ShowDialog() == DialogResult.OK)
 {
 FileStream fs = new FileStream(sfd.FileName, FileMode.Create,
 FileAccess.Write);
 // Create an XmlTextWriter using the file stream.
 XmlTextWriter xtw = new XmlTextWriter(fs, Encoding.Unicode);

 try
 {
 // Write the XML to the file.
 ds.WriteXml(xtw, XmlWriteMode.WriteSchema);

 resultTextBox.Text = "XML file written.";
 }
 catch(Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 finally
 {
 xtw.Close();
 }
 }
}

private void readXmlButton_Click(object sender, System.EventArgs e)
{
 // Write the XML schema from a file.
 // Display file dialog to select XML file to read.
 OpenFileDialog ofd = new OpenFileDialog();
 ofd.InitialDirectory = System.IO.Path.GetTempPath();
 ofd.Filter = "XML Files (*.xml)*.xmlAll files (*.*)*.*";
 ofd.FilterIndex = 1;

 if (ofd.ShowDialog() == DialogResult.OK)
 {
 FileStream fs = new FileStream(ofd.FileName, FileMode.Open,
 FileAccess.Read);
 // Create an XmlTextReader using the file stream.
 XmlTextReader xtr = new XmlTextReader(fs);
 
 try
 {
 // Read the schema into the DataSet.
 DataSet ds = new DataSet();
 ds.ReadXml(xtr, XmlReadMode.ReadSchema);

 // Bind the default view of the Orders table to the grid.
 resultDataGrid.DataSource =
 ds.Tables[ORDERS_TABLE].DefaultView;

 // Write the XML to a memory stream and display it.
 MemoryStream ms = new MemoryStream();
 ds.WriteXml(ms, XmlWriteMode.WriteSchema);
 byte[] result = ms.ToArray();
 ms.Close();

 resultTextBox.Text = Encoding.UTF8.GetString(result, 0,
 result.Length);
 }
 catch(Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 finally
 {
 xtr.Close();
 }
 }
}

private void clearButton_Click(object sender, System.EventArgs e)
{
 // Clear the data grid and the result text box.
 resultDataGrid.DataSource = null;
 resultTextBox.Clear();
}

Discussion

The solution uses the XmlTextWriter and XmlTextReader classes to write and read the XML data for the DataSet . For more information about these classes, see the Discussion for Recipe 8.1 and the MSDN Library.

The WriteXml() and ReadXml() methods of the DataSet are used to write and read the XML for the DataSet . The WriteXml() method takes an optional argument that specifies a value from the XmlWriteMode enumeration described in Table 8-2.

Table 8-2. XmlWriteMode enumeration

Value	Description
DiffGram	The DataSet is written as a DiffGram , which is an XML format used by .NET to persist and serialize a DataSet . A DiffGram includes all information required to recreate the DataSet including original and current values, row errors, and row order. A DiffGram does not include information about the DataSet schema.
IgnoreSchema	The DataSet is written without inline schema information. This is the default.
WriteSchema	The DataSet is written with an inline XSD schema for the relational structure of the DataSet .

If an in-line schema is not written, the ReadXml() method can still be used to read the data into a DataSet , but the method will not be able to completely recreate the schema for the DataSet .

The XmlRead() method takes an optional argument that specifies a value from the XmlReadMode enumeration described in Table 8-3.

Table 8-3. XmlReadMode enumeration

Value	Description
Auto	Uses the most appropriate of the following settings: DiffGram if the data is a DiffGram ReadSchema if the DataSet already has a schema or the XML document contains an inline schema InferSchema if the DataSet does not already have a schema and the XML document does not contain an inline schema Auto is the default.
DiffGram	Reads a DiffGram applying the changes to the DataSet . The target DataSet must have the same schema as the DataSet from which the WriteXml() method created the DiffGram . Otherwise, an exception is raised.
Fragment	Reads an XML document such as one generated by queries with the FOR XML clause.
IgnoreSchema	Ignores any inline schema and reads data from the XML document into the existing DataSet schema. Data not matching the existing schema is discarded.
InferSchema	Ignores any inline schema, infers the schema from the data, and loads the data into the DataSet . The DataSet schema is extended by adding new tables and columns as required.
ReadSchema	Reads any inline schema and loads the data into the DataSet . If the DataSet already contains tables, new tables will be added but an exception will be raised if any tables defined by the inline schema already exist in the DataSet .

Example 8-4 shows the XML file with inline schema written by this solution.

Example 8-4. Orders with order details XML file, with schema

 <
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 10248
 VINET
 5
 1996-07-04T00:00:00.0000000-04:00
 1996-08-01T00:00:00.0000000-04:00
 1996-07-16T00:00:00.0000000-04:00
 3
 32.38
 Vins et alcools Chevalier
 59 rue de l'Abbaye
 Reims
 51100
 France
 
 
 10249
 TOMSP
 6
 1996-07-05T00:00:00.0000000-04:00
 1996-08-16T00:00:00.0000000-04:00
 1996-07-10T00:00:00.0000000-04:00
 1
 11.61
 Toms Spezialitten
 Luisenstr. 48
 Mnster
 44087
 Germany
 



 
 11076
 BONAP
 4
 1998-05-06T00:00:00.0000000-04:00
 1998-06-03T00:00:00.0000000-04:00
 2
 38.28
 Bon app'
 12, rue des Bouchers
 Marseille
 13008
 France
 
 
 11077
 RATTC
 1
 1998-05-06T00:00:00.0000000-04:00
 1998-06-03T00:00:00.0000000-04:00
 2
 8.53
 Rattlesnake Canyon Grocery
 2817 Milton Dr.
 Albuquerque
 NM
 87110
 USA
 
 
 10248
 11
 14
 12
 0
 
 
 10248
 42
 9.8
 10
 0
 



 
 11077
 75
 7.75
 4
 0
 
 
 11077
 77
 13
 2
 0
 

Use the WriteXmlSchema() and ReadXmlSchema() methods of the DataSet to write and read just the XSD schema information.