Writing and Reading XML Using a DataSet Object

Writing and Reading XML Using a DataSet Object

XML is a convenient format for moving information around. You can write out the contents of the DataTable objects contained in a DataSet to an XML file using the WriteXml() method. The XML file written by this method contains the DataTable column names and values.

You can write out the schema of a DataSet object to an XML file using the WriteXmlSchema() method. The XML file written by this method contains the structure of the DataTable objects contained in the DataSet. You can also get the XML in a DataSet using the GetXml() method, which returns the XML in a string.

You can read the contents of the DataTable objects in an XML file into a DataSet using the ReadXml() method. You can also read the schema contained in an XML file using the ReadXmlSchema() method.

Note 

SQL Server also contains extensive built-in XML functionality, which you'll learn about in Chapter 16, "Using SQL Server's XML Support."

Using the WriteXml() Method

Let's say you have a DataSet object named myDataSet. Assume that myDataSet has a DataTable that contains the CustomerID, CompanyName, ContactName, and Address columns for the top two rows from the Customers table. The following code shows this:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "SELECT TOP 2 CustomerID, CompanyName, ContactName, Address " +   "FROM Customers " +   "ORDER BY CustomerID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); Console.WriteLine("Retrieving rows from the Customers table"); mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlConnection.Close(); 

You can write out the contents of myDataSet to an XML file using the WriteXml() method. For example:

 myDataSet.WriteXml("myXmlFile.xml"); 

This writes an XML file named myXmlFile.xml, as shown in Listing 10.9.

Listing 10.9: MYXMLFILE.XML

start example
 <?xml version="1.0" standalone="yes"?> <NewDataSet>   <Customers>     <CustomerID>ALFKI</CustomerID>     <CompanyName>Alfreds Futterkiste</CompanyName>     <ContactName>Maria Anders</ContactName>     <Address>Obere Str. 57</Address>   </Customers>   <Customers>     <CustomerID>ANATR</CustomerID>     <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>     <ContactName>Ana Trujillo</ContactName>     <Address>Avda. de la Constitución 2222</Address>   </Customers> </NewDataSet> 
end example

As you can see, this file contains the columns for the rows retrieved from the Customers table.

The WriteXml() method is overloaded as follows:

 void WriteXml(Stream myStream); void WriteXml(string fileName); void WriteXml(TextWriter myTextWriter); void WriteXml(XmlWriter myXmlWriter); void WriteXml(stream myStream, XmlWriteMode myXmlWriteMode); void WriteXml(string fileName, XmlWriteMode myXmlWriteMode); void WriteXml(TextWriter myTextWriter, XmlWriteMode myXmlWriteMode); void WriteXml(XmlWriter myXmlWriter, XmlWriteMode myXmlWriteMode); 

where myXmlWriteMode is a constant from the System.Data.XmlWriteMode enumeration that specifies how to write XML data and the schema. Table 10.8 shows the constants defined in the XmlWriteMode enumeration.

Table 10.8: XmlWriteMode ENUMERATION MEMBERS

CONSTANT

DESCRIPTION

DiffGram

Writes out the DataSet as a DiffGram, which contains the original values and the changes to those values to make them current. You can generate a DiffGram that contains only the changes by calling the GetChanges() method of your DataSet, and then call WriteXml().

IgnoreSchema

Writes out only the data in the DataSet, without writing the schema. IgnoreSchema is the default.

WriteSchema

Writes out the schema in the DataSet.

The following example shows the use of the XmlWriteMode.WriteSchema constant:

 myDataSet.WriteXml("myXmlFile2.xml", XmlWriteMode.WriteSchema); 

This writes an XML file named myXmlFile2.xml, as shown in Listing 10.10.

Listing 10.10: MYXMLFILE2.XML

start example
 <?xml version="1.0" standalone="yes"?> <NewDataSet>   <xsd:schema  targetNamespace="" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft- com:xml-msdata">     <xsd:element name="NewDataSet" msdata:IsDataSet="true">       <xsd:complexType>         <xsd:choice maxOccurs="unbounded">           <xsd:element name="Customers">             <xsd:complexType>               <xsd:sequence>                 <xsd:element name="CustomerID" type="xsd:string" minOccurs="0" />                 <xsd:element name="CompanyName" type="xsd:string" minOccurs="0" />                 <xsd:element name="ContactName" type="xsd:string" minOccurs="0" />                 <xsd:element name="Address" type="xsd:string" minOccurs="0" />               </xsd:sequence>             </xsd:complexType>           </xsd:element>         </xsd:choice>       </xsd:complexType>     </xsd:element>   </xsd:schema>   <Customers>     <CustomerID>ALFKI</CustomerID>     <CompanyName>Alfreds Futterkiste</CompanyName>     <ContactName>Maria Anders</ContactName>     <Address>Obere Str. 57</Address>   </Customers>   <Customers>     <CustomerID>ANATR</CustomerID>     <CompanyName>Ana Trujillo3 Emparedados y helados</CompanyName>     <ContactName>Ana Trujillo</ContactName>     <Address>Avda. de la Constitución 2222</Address>   </Customers> </NewDataSet> 
end example

As you can see, this file contains the schema definition for the columns used in the original SELECT statement, as well as the column values for the rows retrieved.

Using the WriteXmlSchema() Method

You can write out the schema of myDataSet to an XML file using the WriteXmlSchema() method. For example:

 myDataSet.WriteXmlSchema("myXmlSchemaFile.xml"); 

This writes an XML file named myXmlSchemaFile.xml, as shown in Listing 10.11.

Listing 10.11: MYXMLSCHEMAFILE.XML

start example
 <?xml version="1.0" standalone="yes"?> <xsd:schema  targetNamespace="" xmlns=""  xmlns:xsd="http://www.w3.org/2001/XMLSchema"  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xsd:element name="NewDataSet" msdata:IsDataSet="true">     <xsd:complexType>       <xsd:choice maxOccurs="unbounded">         <xsd:element name="Customers">           <xsd:complexType>             <xsd:sequence>               <xsd:element name="CustomerID" type="xsd:string" minOccurs="0" />               <xsd:element name="CompanyName" type="xsd:string" minOccurs="0" />               <xsd:element name="ContactName" type="xsd:string" minOccurs="0" />               <xsd:element name="Address" type="xsd:string" minOccurs="0" />             </xsd:sequence>           </xsd:complexType>         </xsd:element>       </xsd:choice>     </xsd:complexType>   </xsd:element> </xsd:schema> 
end example

As you can see, this file contains the schema definition for the columns retrieved from the Customers table by the original SELECT statement.

Using the ReadXml() Method

You can read the contents of an XML file into a DataSet object using the ReadXml() method. This method reads the rows and columns from the XML file into DataTable objects of the DataSet. For example, the following statement uses the ReadXml() method to read the XML file myXmlFile.xml previously written by the WriteXml() method:

 myDataSet.ReadXml("myXmlFile.xml"); 

The ReadXml() method is overloaded as follows:

 void ReadXml(Stream myStream); void ReadXml(string fileName); void ReadXml(TextReader myTextReader); void ReadXml(XmlReader myXmlReader); void ReadXml(stream myStream, XmlReadMode myXmlReadMode); void ReadXml(string fileName, XmlReadMode myXmlReadMode); void ReadXml(TextReader myTextReader, XmlReadMode myXmlReadMode); void ReadXml(XmlReader myXmlReader, XmlReadMode myXmlReadMode); 

where myXmlReadMode is a constant from the System.Data.XmlReadMode enumeration that specifies how to read XML data and the schema. Table 10.9 shows the constants defined in the XmlReadMode enumeration.

Table 10.9: XmlReadMode ENUMERATION MEMBERS

CONSTANT

DESCRIPTION

Auto

Reads the XML file in an appropriate manner:

  • If the XML file contains a DiffGram, then XmlReadMode is set to DiffGram.

  • If the DataSet already contains a schema or the XML file contains a schema, then XmlReadMode is set to ReadSchema.

  • If the DataSet doesn't contain a schema and the XML file doesn't contain a schema, then XmlReadMode is set to InferSchema.

Auto is the default.

DiffGram

Reads the XML file as a DiffGram, which contains the original values and the changes to those values to make them current. The changes are then applied to your DataSet. This is similar to calling the Merge() method of a DataSet in that changes from one DataSet are merged with another.

Fragment

Reads an XML file that contains inline XDR schema fragments such as those generated by executing SELECT statements containing FOR XML clauses.

IgnoreSchema

Reads out only the data in the DataSet, without reading the schema.

InferSchema

Infers the schema of the XML file by examining the data stored in it.

ReadSchema

Reads the schema from the XML file into the DataSet.

The following example shows the use of the XmlReadMode.ReadSchema constant:

 myDataSet.ReadXml("myXmlFile2.xml", XmlReadMode.ReadSchema); 

Listing 10.12 illustrates how to write and read XML files using ADO.NET.

Listing 10.12: WRITEANDREADXML.CS

start example
 /*   WriteAndReadXml.cs illustrates how to write and read XML files */ using System; using System.Data; using System.Data.SqlClient; class WriteAndReadXML {   public static void Main() {   SqlConnection mySqlConnection =     new SqlConnection(       "server=localhost;database=Northwind;uid=sa;pwd=sa"     );   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();   mySqlCommand.CommandText =     "SELECT TOP 2 CustomerID, CompanyName, ContactName, Address " +     "FROM Customers " +     "ORDER BY CustomerID";   SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();   mySqlDataAdapter.SelectCommand = mySqlCommand;   DataSet myDataSet = new DataSet();   mySqlConnection.Open();   Console.WriteLine("Retrieving rows from the Customers table");   mySqlDataAdapter.Fill(myDataSet, "Customers");   mySqlConnection.Close();   // use the WriteXml() method to write the DataSet out to an   // XML file   Console.WriteLine("Writing rows out to an XML file named " +     "myXmlFile.xml using the WriteXml() method");   myDataSet.WriteXml("myXmlFile.xml");   Console.WriteLine("Writing schema out to an XML file named " +     "myXmlFile2.xml using the WriteXml() method");   myDataSet.WriteXml("myXmlFile2.xml", XmlWriteMode.WriteSchema);   // use the WriteXmlSchema() method to write the schema of the   // DataSet out to an XML file   Console.WriteLine("Writing schema out to an XML file named " +     "myXmlSchemaFile.xml using the WriteXmlSchema() method");   myDataSet.WriteXmlSchema("myXmlSchemaFile.xml");   // use the Clear() method to clear the current rows in the DataSet   myDataSet.Clear();   // use the ReadXml() method to read the contents of the XML file   // into the DataSet   Console.WriteLine("Reading rows from myXmlFile.xml " +     "using the ReadXml() method");   myDataSet.ReadXml("myXmlFile.xml");   DataTable myDataTable = myDataSet.Tables["Customers"];   foreach (DataRow myDataRow in myDataTable.Rows)     {       Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]);       Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]);       Console.WriteLine("ContactName = " + myDataRow["ContactName"]);       Console.WriteLine("Address = " + myDataRow["Address"]);     }   } } 
end example

The output from this program is as follows:

 Retrieving rows from the Customers table Writing rows out to an XML file named myXmlFile.xml using  the WriteXml() method Writing schema out to an XML file named myXmlFile2.xml  using the WriteXml() method Writing schema out to an XML file named myXmlSchemaFile.xml  using the WriteXmlSchema() method Reading rows from myXmlFile.xml using the ReadXml() method CustomerID = ALFKI CompanyName = Alfreds Futterkiste ContactName = Maria Anders Address = Obere Str. 57 CustomerID = ANATR CompanyName = Ana Trujillo3 Emparedados y helados ContactName = Ana Trujillo Address = Avda. de la Constitución 2222 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

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