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.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax

show all menu





ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton
Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net