Friday, July 20, 2007

Load a DataSet with XML using C#.NET

Load a DataSet with XML using C#.NET

First loading XML data into an XmlDataDocument and then accessing this data from the DataSet. The DataSet has previously loaded a schema in order to create the internal mappings.

Rerence XSD:

books.xsd
<?xml:namespace prefix = xsd /><xsd:schema xsd="http://www.w3.org/2001/XMLSchema" elementformdefault="qualified"><xsd:element type="bookstoreType" name="bookstore">
<xsd:complextype name="bookstoreType">
<xsd:sequence maxoccurs="unbounded">
<xsd:element type="bookType" name="book">
</xsd:sequence></xsd:complextype>
<xsd:complextype name="bookType">
<xsd:sequence><xsd:element type="xsd:string" name="title">
<xsd:element type="authorName" name="author">
<xsd:element type="xsd:decimal" name="price">
</xsd:sequence><xsd:attribute type="xsd:string" name="genre">
</xsd:complextype>
<xsd:complextype name="authorName"><xsd:sequence>
<xsd:element type="xsd:string" name="first-name">
<xsd:element type="xsd:string" name="last-name"></xsd:sequence>
</xsd:complextype>
</xsd:schema>
</xsd:element>
</xsd:element>
</xsd:attribute>
</xsd:element>
</xsd:element>
</xsd:element>
</xsd:element>
</xsd:element>

books.xml

<?xml version='1.0'?>
<!-- This file represents a fragment of a book store inventory database --><bookstore>
<book genre="autobiography" publicationdate="1981" ISBN="1-861003-11-0"> <title>The Autobiography of Benjamin Franklin</title>
<author> <first-name>Benjamin</first-name>
<last-name>Franklin</last-name>
</author> <price>8.99</price>
</book> <book genre="novel" publicationdate="1967" ISBN="0-201-63361-2"> <title>The Confidence Man</title>
<author> <first-name>Herman</first-name>
<last-name>Melville</last-name>
</author> <price>11.99</price>
</book> <book genre="philosophy" publicationdate="1991" ISBN="1-861001-57-6">
<title>The Gorgias</title>
<author>
<first-name>Sidas</first-name>
<last-name>Plato</last-name>
</author>
<price>9.99</price>
</book></bookstore>


private const String document = "books.xml";
private const String myLoadSchema = "books.xsd";
private XmlDataDocument myXmlDataDocument;

public static void Main()
{
String[] args = {document, myLoadSchema};

LoadDataSetXMLDataSample myLoadDataSetXMLDataSample = new LoadDataSetXMLDataSample();
myLoadDataSetXMLDataSample.Run(args);
}

public void Run(String[] args)
{
try
{
Console.WriteLine("Creating an XmlDataDocument ...");
myXmlDataDocument = new XmlDataDocument();
ParseSchema(args[1]);
DisplayTableStructure();
myXmlDataDocument.Load(args[0]);
DisplayTables(myXmlDataDocument.DataSet);
}
catch (Exception e)
{
Console.WriteLine ("Exception: {0}", e.ToString());
}
}
// Loads a specified schema into the DataSetpublic
void ParseSchema(String schema)
{
StreamReader myStreamReader = null;
try
{
Console.WriteLine("Reading Schema file ...");
myStreamReader = new StreamReader(schema); myXmlDataDocument.DataSet.ReadXmlSchema(myStreamReader);
}
catch (Exception e)
{
Console.WriteLine ("Exception: {0}", e.ToString());
}
finally
{
if (myStreamReader != null)
myStreamReader.Close();
}
}

Enables the sample to display the internal table structure by simply iterating over the collections of Tables, Columns, and Rows, and then formatting the output.

// Displays the contents of the DataSet tablesprivate
void DisplayTables(DataSet dataset)
{
// Navigate Dataset
Console.WriteLine("Content of Tables ...\r\n");
foreach(DataTable table in dataset.Tables)
{
Console.WriteLine("TableName = " + table.TableName);
Console.WriteLine ("{0}", "---------");
Console.WriteLine("Columns ...\r\n");
foreach(DataColumn column in table.Columns)
{
Console.Write("{0,-22}",column.ColumnName);
}
Console.WriteLine();
Console.WriteLine("\r\nNumber of rows = {0}", table.Rows.Count.ToString()); Console.WriteLine("Rows ...\r\n");
foreach(DataRow row in table.Rows)
{
foreach(Object value in row.ItemArray)
{
Console.Write("{0,-22}",value.ToString());
}
Console.WriteLine();
}
Console.WriteLine();
}
}

This will display the output in Console window



4 comments:

Unknown said...

But what do you do if there is no XSD File associated, just an XML file. How can you create the mappings using Framework 3.5?

Unknown said...

But what do you do if all you have is an XML file and no XSD? How can you create / save the mappings if you're using Framework 3.5 and VS 2k8 Pro?

Unknown said...

Thank you.
This code helped me read several huge XML files which caused exceptions when using dataset.ReadXml().
Best Regards

elbie said...

I can't retrieve the values of the columns!!! Datarow is empty.
XML:

INSERT
0101
6
Live horses, asses, mules and hinnies:
N
2006-12-18T00:00:00
9999-12-31T00:00:00
VATABLE

ASP.NET MVC - Sport Facility Booking system

  The project relies on some external service providers. Thus accessing them via their API requires authentication. An API Key need to be su...