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:
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.