Mapping DataSet to XML and backwards in .Net

in #utopian-io7 years ago (edited)

What Will I Learn?

  • You will learn Mapping DataSet to XML and backwards in .Net

Requirements

  • .Net Framework

Difficulty

  • Intermediate

Tutorial Contents

  • A brief history of XML
  • Relational Database and XML data model
  • Load a DataSet from XML
  • Loading Schema Information from XML
  • DataSet with a single table to XML
  • DataSet with two tables in master-detail relationship
  • DataSet with three or more tables to XML

Mapping DataSet to XML and backwards in .Net

This tutorial illustrates the translation to and from a .NET DataSet and XML.

Since the arrival of the Internet, people have become more and more dependent on it for daily operations. The Web's (as well as computing applications') insatiable appetite for data has grown even more intense. Most websites have seen the interplay of relational database and XML. Websites rely on relational databases for storing a vast amount of data and performing fast retrieval and manipulation. For exchanging data, they opt for protocols such as SOAP or REST (what strange names!). In these cases, XML serves both as the foundation and the ultimate representation form. Let's briefly look at the origin of XML and make a skim comparison between a relational database and the XML data model.

A brief history of XML

Relational database was born out of the ideas of an IBM mathematician and computer scientist, E. B. Codd, in the early 1970s. Since then it has become the "IT" player in the database industry. In comparison, XML is the new kid in town and it is the result of a never-ending quest for a simpler yet more powerful language for data storage and communication.In 1986, SGML or "Standard Generalized Markup Language" was issued as an international standard. It is a complex and somewhat rigid language that requires expensive authoring tools. SGML was used extensively in large government, legal and publishing industries. In 1990, a simpler and more flexible language called HTML was created and it soon became enormously popular. However, its shortcomings also became increasingly visible. In 1996, the World Wide Web Consortium (W3C) (underwritten by Sun and other outside organizations) formed a committee to explore ways of harnessing the strength of HTML and SGML while sidestepping its limitations. The result is the creation of XML.

Relational Database and XML data model

A relational database consists of a collection of two-dimensional tables of rows and columns, interconnected by keys shared across tables. Through relations (shared keys), tables can be joined, queried and rearranged in different ways. There is no hierarchy or sequence to speak of.

XML on the other hand is all about hierarchy and sequence, as it is mirrored in such terms, parents, children and siblings (it might as well be superiors, subordinates and colleagues).

Since its debut, XML has wooed techies and laymen alike with its easiness and flexibility; and the fact that it is human-readable. However, as much as XML has gained enormous momentum and widespread usage, relational database has long been the backbone infrastructure of enterprise applications; and proven to be effective and efficient. In the foreseeable future, relational database technology will remain the dominant technology, with major adoptions with respect to XML.

Many relational database vendors have provided extended database features to incorporate XML technology. For instance, SQL server has XQuery support and has a new XML data type. Likewise, middleware vendors have also provided native support for XML, like .NET.

In ADO.NET, the central data object - the DataSet - represents a hierarchical, disconnected data cache. Its design is based on XML, which makes the translation between relational data and XML easy. The following discussion will use some examples to illustrate the ways to map a DataSet from and to a XML file.

Load a DataSet from XML

To load a DataSet from an XML file, simply call the DataSet.ReadXml method, like so: 

//load an xml file into a dataset and bind it to a GridView 
DataSet myDS = new DataSet();

myDS.ReadXml(Server.MapPath("books.xml"))

//bind with a GridView for display
GridView1.DataSource = myDS;

GridView1.DataBind();

Figure 1 shows a screenshot of the output:

Figure 1: Screenshot of the output

In this example, a flat XML file with no hierarchy or nested element is selected. As a result, the DataSet contains only one table and it can be conveniently bound to a GridView.

Loading Schema Information from XML

The ReadXmlSchema or the InferXmlSchema methods of the DataSet allow you to load DataSet schema information from an XML document. Alternatively, you may use the overloaded ReadXml method with XmlReadMode.InferSchema as the second argument. 

DataSet myDS = new DataSet();
myDS.ReadXmlSchema("books.xsd"); 

Schema information can be written to an .xsd file using the WriteXmlSchema method: 

private void WriteSchema()
{
   DataSet myDS = new DataSet();

   myDS.ReadXml(Server.MapPath("books2.xml"),XmlReadMode.InferSchema);

   myDS.WriteXmlSchema(Server.MapPath("books2.xsd"));
}

The following is the input XML file, books2.xml: 

<pre>
 <bookstore>
 <book>
 <title>The Great Journey of Benjamin Franklin</title>
 <authors>
    <author>
     <first-name>Joe</first-name>
     <last-name>John</last-name>
     </author>
   </authors>
 <price>8.99</price>
 </book>
   <!--More ...-->
 </bookstore>
</pre>

And here's the output file, books2.xsd: 

<?xml version="1.0" standalone="yes"?>
<xs:schema id="bookstore" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"

xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
 <xs:element name="author">
   <xs:complexType>
     <xs:sequence>
       <xs:element name="first-name" type="xs:string" minOccurs="0" />
       <xs:element name="last-name" type="xs:string" minOccurs="0" />
     </xs:sequence>
   </xs:complexType>
 </xs:element>
 <xs:element name="bookstore" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
   <xs:complexType>
     <xs:choice minOccurs="0" maxOccurs="unbounded">
       <xs:element ref="author" />
       <xs:element name="book">
         <xs:complexType>
           <xs:sequence>
             <xs:element name="title" type="xs:string" minOccurs="0" />
             <xs:element name="price" type="xs:string" minOccurs="0" />
             <xs:element ref="author" minOccurs="0" maxOccurs="unbounded" />
             <xs:element name="authors" minOccurs="0" maxOccurs="unbounded">
               <xs:complexType>
                 <xs:sequence>
                   <xs:element ref="author" minOccurs="0" maxOccurs="unbounded" />
                 </xs:sequence>
               </xs:complexType>
             </xs:element>
           </xs:sequence>
         </xs:complexType>
       </xs:element>
     </xs:choice>
   </xs:complexType>
 </xs:element>
</xs:schema>

The following figure shows a screenshot of the output table relationship:

Figure 2: Output table relationship

A somewhat more complicated XML file with a hierarchy of 3 tiers has been chosen for the example. It is worth noting how the relationship is constructed from the XML to DataSet transformation. The hierarchies are automatically translated into three tables and primary-foreign key pairs are inserted.

DataSet with a single table to XML

Want to map a DataSet containing a single table to XML? Piece of cake. The following code illustrates how to fork some information from an Access database into a table and write it to a XML file. 

// DataSet to XML. 
private void SingleTableToXml()
{
   string connectionstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"data\northwind.mdb");

   OleDbConnection conn = new OleDbConnection(connectionstr);

   DataSet myDS = new DataSet("DataSetTransformed");

   string sql="SELECT CategoryID, CategoryName, Description FROM Categories";
   //Create a DataAdapter to load data from original data source to the DataSet
   OleDbDataAdapter myAdapter = new OleDbDataAdapter(sql, conn);

   myAdapter.Fill(myDS, "Categories");

   //disconnect the database
   conn.Close();

   //Write out the schema that this DataSet created,
   //use the WriteXmlSchema method of the DataSet class
   myDS.WriteXmlSchema(Server.MapPath("categories.xsd"));
     

   // To write out the contents of the DataSet as XML,
   //use a file name to call the WriteXml method of the DataSet class
   myDS.WriteXml(Server.MapPath("categoies.xml"), XmlWriteMode.IgnoreSchema);

}

Here's a segment of the output XML file: 

<DataSetTransformed>
<Categories>
<CategoryID>1</CategoryID>
<CategoryName>Beverages</CategoryName>
<Description>Soft drinks, coffees, teas, beers, and ales</Description>
</Categories>
<!-- More -->
</DataSetTransformed>

As you can see, the root element of the XML file takes the name of the DataSet. In .NET, many of the tasks that could previously only be performed by a DataSet can also be tackled by a DataTable; such as XML transformation.

DataSet with two tables in master-detail relationship

It gets a little complicated and code-heavy to map to an appropriately nested XML from a DataSet with master-detail tables. First, you need to specify the primary key of the master table. Second, you have to register the foreign key of the detail table. Finally, you must set to true the Nested property of the DataSet

// DataSet to XML. 
private void MasterDetailTableToXml()
{
   string connectionstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"data\northwind.mdb");

   OleDbConnection conn = new OleDbConnection(connectionstr);

   DataSet myDS = new DataSet("DataSetTransformed");
   
   string sql = "SELECT CategoryID, CategoryName, Description FROM Categories";
   //Create a DataAdapter to load data from original data source to the DataSet
   OleDbDataAdapter myAdapter = new OleDbDataAdapter(sql, conn);
 
   myAdapter.Fill(myDS, "Categories");

   sql = "SELECT ProductID, ProductName, CategoryID, UnitPrice FROM Products";

   //Load detail table from original data source to the DataSet
   myAdapter.SelectCommand = new OleDbCommand(sql, conn);
   myAdapter.Fill(myDS, "Products");

  //disconnect the database
  conn.Close();
       

  //Get the primary key column from the master table
  DataColumn primarykey = myDS.Tables["Categories"].Columns["CategoryID"];

  //Get the foreign key column from the detail table
  DataColumn foreignkey = myDS.Tables["Products"].Columns["CategoryID"];

  //Assign a relation
  DataRelation relation = myDS.Relations.Add(primarykey, foreignkey);

  //Ask ADO.NET to generate nested XML nodes
  relation.Nested = true;

  //Write out the schema that this DataSet created,
  //use the WriteXmlSchema method of the DataSet class
  myDS.WriteXmlSchema(Server.MapPath("products.xsd"));


  // To write out the contents of the DataSet as XML,
  //use a file name to call the WriteXml method of the DataSet class
  myDS.WriteXml(Server.MapPath("products.xml"), XmlWriteMode.IgnoreSchema);
  
}

The following is the output XML File: 

<DataSetTransformed>
  <Categories>
      <CategoryID>1</CategoryID>
      <CategoryName>Beverages</CategoryName>
      <Description>Soft drinks, coffees, teas, beers, and ales</Description>
     <Products>
          <ProductID>1</ProductID>
          <ProductName>Chai</ProductName>
          <CategoryID>1</CategoryID>
          <UnitPrice>18</UnitPrice>
      </Products>
   <!--More goes here-->
 </Categories>
</DataSetTransformed>

DataSet with three or more tables to XML

While it is quite manageable and even fun to translate a two-master-detail-tabled DataSet to an XML file, it is quite hopeless to go beyond. I mean: If you want to rely only on the built-in methods of DataSet and get the XML file elegantly nested in multiple levels. Of course, it is a different story if you are willing to sweat and traverse among the rows and columns of the different tables of a DataSet.

Summary

While relational database remains the major database technology and crucial infrastructure of most business and web applications, XML has shown clear advantages and great promises in data exchange and storage. Therefore, many database and middleware vendors have taken strides to enable the integration and communication of both technologies. .NET is no exception. And the above tutorial showed how easy it is to transform from XML to DataSet and backwards. 



Posted on Utopian.io - Rewarding Open Source Contributors

Sort:  

Hey @cheretta, your contribution was rejected by the supervisor @mcfarhat because he found out that it did not follow the Utopian rules.

Upvote this comment to help Utopian grow its power and help other Open Source contributions like this one. Do you want to chat? Join me on Discord.

Your contribution cannot be approved because it does not follow the Utopian Rules, and is considered as plagiarism.
You are using text from the web without references, such as for example section "brief history of XML".
Your whole tutorial appears to be picked us from elsewhere.
Plagiarism is not allowed on Utopian, and posts that engage in plagiarism will be flagged and hidden forever.

You can contact us on Discord.
[utopian-moderator]

Hey @mcfarhat, I just gave you a tip for your hard work on moderation. Upvote this comment to support the utopian moderators and increase your future rewards!

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

@flauwy please check note below

Congratulation

Today one year ago you joined SteemIt
Thank you, for making SteemIt great and Steem on for more years to come!

(You are being celebrated here)

Coin Marketplace

STEEM 0.16
TRX 0.15
JST 0.028
BTC 55789.30
ETH 2345.53
USDT 1.00
SBD 2.31