Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
An Exploitation of ADO.NET DataSet: Establishing Relationships among Heterogenous Data Sources
Posted Date: 11 Jul 2007 Resource Type: Articles Category: .NET Framework
|
Posted By: Prajnan Das Member Level: Gold
Rating:  Points: 15
|
Introduction
In this article an example is presented which establishes a relationship between two different data entities - one sourced from SQL Server 2005 and the other from a XML file.
The Problem
We have a simple library application here which stores information about various titles of books written by various author. The application domain involves two entities: Author and Title. Author is a table defined in the SQL Server 2005 with following schema:
AuthorId* int
firstname nchar(10)
lastname nchar(10)
*Primary Key
While, the data pertaining to Title is defined as an XML file driven by the following XML schema definition:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="Publication"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Publication">
<xs:complexType>
<xs:sequence>
<xs:element name="Title" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence/>
<xs:attribute name="AuthorId" type="xs:int" />
<xs:attribute name="Name" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Here is some sample data:
Author:
AuthorId firstname lastname
1 Prajnan Das
2 Aaron Skonnard
3 Morgan Skinner
Title (e.g. define a XML file D:\XML&SchemaFiles\publications.xml)
<?xml version="1.0" encoding="utf-8"?>
<Publication xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<Title AuthorId="1" Name="C# Programming"/>
<Title AuthorId="1" Name="UML and OOAD"/>
<Title AuthorId="2" Name="ADO.NET Programming"/>
<Title AuthorId="2" Name="WebServices in .NET"/>
<Title AuthorId="3" Name="XML Premier"/>
<Title AuthorId="3" Name="A Guide to SQL Server 2005"/>
</Publication>
Now, the challenge is to establish a relationship between the Author data in the table and Title data in the XML file. The AuthorId attribute in the Title element of the XML file needs to act as foreign key while the AuthorId column in the Author’s table is the primary key.
Reading the data from different data sources
We are going to define a class called DataSetDemo which will encapsulate the creation of a DataSet object which would establish a relationship between the heterogeneous data sources as depicted above. Here is the code for the class:
using System;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess
{
public class DataSetDemo
{
DataSet dataSet;
string connectionStr = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=Books;Integrated Security=True;Pooling=False";
public void CreateDataSetFromDifferentSources()
{
dataSet = new DataSet("Library");
dataSet.ReadXmlSchema(@"D:\XML&SchemaFiles\publications.xsd");
dataSet.ReadXml(@"D:\XML&SchemaFiles\publications.xml", XmlReadMode.Auto);
string commandStr = "SELECT * FROM Author";
SqlDataAdapter adapter = new SqlDataAdapter(commandStr, connectionStr);
adapter.Fill(dataSet, "Author");
DataTable author = dataSet.Tables["Author"];
DataTable title = dataSet.Tables["Title"];
dataSet.Relations.Add("Author_Title", author.Columns["AuthorId"], title.Columns["AuthorId"]);
foreach (DataRow authorRow in author.Rows)
{
Console.WriteLine("{0}, {1}:", authorRow["lastname"], authorRow["firstname"]);
foreach (DataRow publicationRow in authorRow.GetChildRows("Author_Title"))
{
Console.WriteLine(publicationRow["Name"]);
}
Console.WriteLine("");
}
}
}
The core of the logic is defined in the method:
CreateDataSetFromDifferentSources()
The method creates a DataSet by the name “Library”. It first reads the XML schema definition from the file "D:\XML&SchemaFiles\publications.xsd" and then it reads the actual data from the path location: "D:\XML&SchemaFiles\publications.xml". We are using the ReadXmlSchema()method of DataSet to read the schema definition. As XML schema is read in; the DataSet internally creates a Table by the name “Title” which corresponds to the element name “Title” in the XML. The element’s attribute AuthorId and Name are translated as columns by the names “AuthorId” and “Name” respectively under the “Title” table. Their types are Int32 and string respectively which gets mapped appropriately as defined in the XML schema definition. All these translation are automatically taken care by the DataSet; and you don’t need to put any translation code. The actual data is then read in using the ReadXml()method of the DataSet. We need to modify the code accordingly if your path locations pertaining to the schema and XML files are different.
Next, comes reading the Author’s data from the SQL Server 2005. The code above creates a SqlDataAdapter object by passing in a connection string to connect to the SQL Server and a very simple SELECT command string to select all the records from the Author table. For this example I have defined the data source by the name “Books” in the location <SQL Server Install Directory≫:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. You can attach your connection to an existing data base file as well. I construct the connection string by supplying the data source name, the data base(initial catalog) and authentication. Here I am using the Windows integrated authentication. I am not discussing further details about configuring a data base connection as that is a different topic altogether. Once the SqlDataAdapter object is created we can invoke the fill() method of it to populate the DataSet with table data. Note that the table name is supplied as “Author” while calling the method.
Establishing the relationship among the tables
After reading all the data we have two tables in the DataSet object: Author and Title. From this point onwards we can treat them as relational tables forgetting about from where they came. That is the power of DataSet. We have defined a relationship between the two tables by the name “Author_Title”. We do this by adding a DataRelation object to the Relations property of the DataSet. The parent column for this relationship is the AuthorId from the Author table while the AuthorId of the Title table is the child column. And that’s it! We have successfully established the relationship.
Navigation the tables
We can navigate between the tables once the relationship is established. That’s what is done in the nested foreach loop towards the end of the code. First each DataRow object from the Author table is fetched in and then GetChildRows() method is called on these rows by passing in the name of relationship created earlier. A call to this method effectively returns all the rows from the Title table connected by the common field AuthorId.
Test code
We can invoke the logic by a call like this:
new DataSetDemo().CreateDataSetFromDifferentSources();
This would be output; depending on data:
Das , Prajnan :
C# Programming
UML and OOAD
Skonnard , Aaron :
ADO.NET Programming
WebServices in .NET
Skinner , Morgan :
XML Premier
A Guide to SQL Server 2005
Summary
The capability of the DataSet to handle data from varied and diversified sources has been demonstrated. Once data is read in; it sits as a relational table model among which relationship can be established and data can be navigated in an uniform way forgetting about the actual sources.
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|