XML and Database Mapping in .NET
by Niel Bornstein
|
Pages: 1, 2
Where's the DB?
|
Related Reading 
.NET Framework Essentials |
I mentioned that the DataSet class can represent a
disconnected view of a database. In fact, as in this case, a
DataSet doesn't really need a database backing it
at all. You can even think of the XML output we're producing as
the database, although it lacks all the usual ACID properties of
a relational database. Still, it makes for a convenient
demonstration of some of the XML functionality.
Having created the DataSet for the dog show, you
could now go on and populate it with data using code something
like this:
DataRow show = showTable.NewRow();
show["Id"] = 1;
show["Name"] = "O'Reilly Invitational Dog Show";
showTable.Rows.Add(show);
That works well enough, although it's a lot of work, and there's
nothing being done at compile time to guarantee that you
actually have a column named "Id" in the
DataTable. It would be much better if you could
access tables and columns in a more type-safe way. Writing the
code to do that could be a lot of work, but you'd only have to
do it once. Actually, the .NET framework can do a lot of the
heavy lifting for you. In addition to the work you saw it do in
the last article, the xsd tool can generate a
subclass of DataSet for a specific W3C XML Schema.
But first you need a schema. The DataSet class has
a WriteXmlSchema() method which creates an W3C XML
Schema from the DataSet. You simply need to add a
line to the end of the MakeDataSet program to write
the schema to a file:
dataSet.WriteXmlSchema("DogShow.xsd");
Now you can generate a DogShow class with the
following command line:
xsd /dataset DogShow.xsd
The resulting code, placed by default in the file
DogShow.cs, is quite lengthy, and I won't include
the source listing here (you can read it here). Suffice it to say that
xsd has generated a class which is a subclass of
DataSet, which you can use to directly access
tables and columns. It's quite convenient.
Here's the source code for a program which uses the generated
class to create an instance of DogShow and add data
to it. After that, it writes the data to an XML file.
using System;
using System.Data;
public class DogShowWriter {
public static void Main(string [] args) {
DogShow dogShow = new DogShow();
// insert a show row
DogShow.ShowRow show = dogShow.Show.AddShowRow(1,
"O'Reilly Invitational");
// insert a breed row
DogShow.BreedRow breed = dogShow.Breed.AddBreedRow(
1, "English Springer Spaniel");
// insert a couple of dog rows
dogShow.Dog.AddDogRow(
1, "Wil-Orion's Angus Highlander", breed);
dogShow.Dog.AddDogRow(
2, "Len-Lear's Webmaster", breed);
dogShow.Dog.AddDogRow(
3, "Ch. Sallylyn's Condor", breed);
// insert a judge row
DogShow.JudgeRow judge =
dogShow.Judge.AddJudgeRow(1, "John", "Smith");
// insert a show ring row
DogShow.ShowRingRow showRing =
dogShow.ShowRing.AddShowRingRow(1, "Ring 1");
// insert a judging row
DateTime judgingTime =
new DateTime(2002,10,20,14,00,00);
dogShow.Judging.AddJudgingRow(judgingTime, breed,
judge, showRing, show);
// write the data
dogShow.WriteXml("DogShow.xml");
}
}
You can see that with the generated DogShow class,
it's much easier to write and debug your code, for a few
reasons. First, since the compiler will warn you when you
reference a table or column that doesn't exist, you're much less
likely to run into runtime errors. Second, xsd has
generated constructors for each table's rows, so you don't need
to go through the tedious effort of referencing each column
through the row's indexer property.
You should note the last line of code, where I've called
dogShow.WriteXml("DogShow.xml");. It creates an XML
file representing the DataSet that I just created
and filled with data. Here's the file:
<?xml version="1.0" standalone="yes"?>
<DogShow>
<Show>
<Id>1</Id>
<Name>O'Reilly Invitational</Name>
</Show>
<Breed>
<Id>1</Id>
<Name>English Springer Spaniel</Name>
</Breed>
<Dog>
<Id>1</Id>
<Name>Wil-Orion's Angus Highlander</Name>
<BreedId>1</BreedId>
</Dog>
<Dog>
<Id>2</Id>
<Name>Len-Lear's Webmaster</Name>
<BreedId>1</BreedId>
</Dog>
<Dog>
<Id>3</Id>
<Name>Ch. Sallylyn's Condor</Name>
<BreedId>1</BreedId>
</Dog>
<Judge>
<Id>1</Id>
<FirstName>John</FirstName>
<LastName>Smith</LastName>
</Judge>
<ShowRing>
<Id>1</Id>
<Name>Ring 1</Name>
</ShowRing>
<Judging>
<ShowTime>2002-10-20T14:00:00.0000000-04:00</ShowTime>
<BreedId>1</BreedId>
<JudgeId>1</JudgeId>
<ShowRingId>1</ShowRingId>
<ShowId>1</ShowId>
</Judging>
</DogShow>
This file can also be read back into a DataSet --
or into an instance of the generated DogShow class
-- with the ReadXml() method.
Making the Data Fit
So now we've got an XML file which represents the O'Reilly Invitational Dog Show. Unfortunately, it's not the same as the one in the last article, which was more tree-oriented. The XML generated here is more table-oriented, which is entirely appropriate, given that we are talking about a relational database. It would be nice to be able to transform the original tree-oriented XML into the new table-oriented schema. Seems like there ought to be a tool to do that.
Thanks to the fact that all of .NET's XML classes are tightly
integrated, you can use XSLT to transform a document on disk
into an XmlDocument in memory, and then pass that
to another class. So, given an appropriate XSLT stylesheet
named "DogShow.xsl", the following code will read it right into
the DataSet:
using System;
using System.Data;
using System.Xml;
using System.Xml.Xsl;
public class DogShowTransformer {
public static void Main(string [] args) {
// Create a DataSet instance
DogShow dogShow = new DogShow();
// Read the data
XmlDocument document = new XmlDocument();
document.Load("DogShow.xml");
// Read the stylesheet
XslTransform xslt = new XslTransform();
xslt.Load("DogShow.xsl");
// Transform the data
XmlReader reader = xslt.Transform(document, null);
// Load the DataSet
dogShow.ReadXml(reader);
}
}Now What?
DataSet does quite a bit more than I can cover
here. For example, I mentioned its ability to keep track of
changes while disconnected from the database. The
DiffGram is responsible for this ability. The
DiffGram is itself an XML document, and it is used
to serialize DataSets across a web service. The
DiffGram consists of three sections: one containing
the current data, <DataInstance>; one containing
any changes to the DataSet,
<diffgr:before>; and one listing any errors,
<diffgr:errors>. Any changes in the
<diffgr:before> section and errors in the
<diffgr:errors> section are related back to the
data in the <DataInstance> section by the
diffgr:id attribute.
I haven't touched on the XmlDataDocument class yet,
either. This class enables you to synchronize multiple of views
of the same DataSet, much as I demonstrated with
XSLT earlier. (The main reason I did not use
XmlDataDocument is that it depends on the names of
elements being the same in both schemas, so that the elements
can be mapped to each other properly. As you will recall, in the
previous article, the XML schema had different element names.)
There's still a whole lot more to be covered, including the
SqlCommand's ExecuteXmlReader()
method, which returns an XmlReader instance you can
use to read data from a database with the regular .NET XML
tools.
I continue to be impressed with the extent of .NET's XML integration. All the major technologies are represented, and Microsoft has use XML in innovative ways throughout the framework. I believe that the Java XML community can certainly learn a thing or two from .NET.
Resources |
Postscript
One of the challenges of comparing the handling of XML in Java and C# is the fact that XML processing is not built into the Java class library. When deciding what features to compare, I've often resorted to searching the Web to find comparable Java tools for features that are a standard part of the .NET Common Language Runtime.
In my last article, I wanted to compare XML databinding in Java and C#. I knew that the .NET CLR contained excellent support for generating classes from an XML Schema, and I knew that Castor also supported databinding. As I examined Castor, I saw that it also supported binding Java objects to relational databases using something called JDO, and I put this fact aside to investigate in a future article.
Unfortunately, I forgot to read the fine print:
Does Castor JDO comply with the SUN JSR-000012 specification?
No, Castor JDO doesn't comply with the SUN's JDO specification.
Although Castor JDO carries very similar goals as SUN's JDO, it has been developed independently from the JSR.
And this pretty much tells the story when it comes to support for XML in Java. There are many implementations; some predate the applicable standards, and some conform to the standards.
Comment on this article in our forums.
(* You must be a member of XML.com to use this feature.)
Comment on this Article
| Titles Only | Titles Only | Newest First |
- SportsFeed Xml Processor
2008-01-22 02:38:42 twostepted [Reply]
You can use SportsFeed Xml Processor to automate transforming and importing remote or local xml data into many different databases including Sql Server, Mysql, Oracle, SQLite, PostgreSql and others. It is a cross platform service application with scheduling and monitoring features which can work with any type of xml. There is a users group at http://groups.google.com/group/sportsfeed-users/
- .NET CLR vs. The Class Library
2003-03-06 05:20:14 Isaac Citrom [Reply]
I just wanted to metnion to Niel and other readers that the article, although very good and useful, confuses the .NET runtime and the .NET Class Library.
The runtime is the Common Language Runtime (CLR). It is an executable hosting environment that supplies all of the basic services for .NET executables, such as garbage collection and memory management. Also, it is implemented based on the Common Language Specification (CLS) which, for example, defines primitive types.
The VAST library of classes against which .NET developers code is the .NET Framework Library or the .NET Class Library. It is an enormous and comprehensive framework coded by Microsoft _on top_ and against the CLR. The library includes the various namespaces such as System.Xml or System.Data, etc.
These namespaces are delivered as a set of dlls. I suppose, if one wanted to, one could bypass this functionality and code directly against the CLR, but then the developer would have a whole lot of coding to do. I assume this architecture is similar in the Java world where a developer mainly codes against the various packages delivered with a Java SDK, and not the Java VM itself.
- dynamicaly generate the xml doc
2003-02-12 19:59:31 Jon Bacha [Reply]
I want to be able take this one step further and get the fields inputed from a web form to be out put to the xml doc. Then serialize the outputed doc since there will be many of these xml docs generated.
so i think varibles would need to be added to where the static strings were declared in the class.
Right now I have the requied xml schema and am working backwords to build the appropriate web service that collects the data fields.
Is there some guidence to adding these varibles.
Creating a serial number name for the generated xml docuement?
- Dogshow database
2002-10-26 19:52:39 Michael Maron [Reply]
Major problem with this example is that in practice it is definitely better to create SQL tables using SQL scripts with proper CREATE statements. Such scripts can be prepared either manually or using some visual IDE.
Creating tables programmtically from Java or C# looks pretty artificial if not to say confusing.
For what it is worth,
Michael
