.NN #7: Creating Data Access Contracts with XML

Blog

Since SQL Server 2000 the database developers have enjoyed some cool ways to work with XML in the database.  Jason Follas is even doing a talk on using XML in the database at the upcoming Cincinnati SQL Server Users Group meeting (Sept 25th, 2007).  The story of XML with SQL 2005 is even better than it was with SQL 2000.  This ability to parse and deal with XML in the database has opened up a new way for the data access code to communicate with the database.

I’m sure many of you have used XML to pass batch updates from the data access tier to the database.  This cuts down on the number of trips over the wire and aids in performance when large numbers of updates are necessary in rapid succession.  The focus of this post is on the contract of that call between the data access code and the database.  What does that XML look like and how do you package it up?

Usually what happens is that the DBD (Database Developer) and/or the managed code developer decide on what the XML should look like and (hopefully) generate a XML schema from it.  Hopefully this is a collaborative effort.  Once this is done, you have your contract.  Then you can both go your separate ways to code up what you need to get done.  The DBD needs to code the parsing and reading of the XML into what they need to do in order to process the updates (or whatever else the procedure is intended to do); while the managed code developer needs to translate whatever he is working with into this XML structure.

For the purposes of the rest of this post I’m going to assume that the managed code developer is dealing with objects.  Their goal is to map the objects that need to be passed to the database into the XML structure that was agreed upon.  We can do this in 4 easy steps:

  1. Translate the XML Schema into managed code. Provide mapping of the entity objects/business objects to the XML structures. Serialize the mapped objects into XML. Pass to the database.

I’ve included a little example of this process in the form of a completed C# project.  You can download the code and kind of follow along with the post.

First off let’s start with translating the XML schema into managed code.  This is taken care of by using XSD.exe to map the .xsd file into a set of managed code classes (VB.Net or C#).  This is where the trick of having a shortcut to jump out to the command window at the location of a given file comes in real handy (mentioned in .NN #6, look under the External Tools heading).  Here is what I do to set this up in a project and make it easy to update:

  • Include the .xsd file as a file in the project.  I place this under the same project that I want the created class files to belong to.  Under the properties for this file set it’s Build Action property to “None”.

In the example this is the BusinessObjectUpdateBatch.xsd

  • I also add another file to the project named “GenerateXXXXClasses.cmd”.  The XXXX is replaced by whatever is the root object being generated for the XSD.  So you might see “GenerateOrderBatchClasses.cmd”.  In this file I put the following line:

xsd.exe [nameofXsdFile].xsd /c /l:cs /n:ASpecificNameSpace /nologo

The /c switch means generate classes.  The /l:cs switch says generate C# code, but you can use VB.Net as well, and the /n: switch is used to indicate the namespace the generated code should use.  You can also indicate the output directory, but I usually want it to generate in the same directory as my command file.

Note this file too should have a Build Action property set to “None”.  In the example this is the GenerateBusinessObjectUpdateBatch.cmd file.

After this is set up I use my External Tool command to jump out to the command prompt in the same directory that the GenerateXXXX.cmd file is sitting.  I then execute the command file and the code file is generated from the schema.  I then add that into the project using Add > Existing Item.  When you do this the .cs (or .vb file) will appear in the project under the xsd file in the Solution Explorer.  You can open it up and take a look at the generated code.  Note that you can now change this code file, but remember if you ever have to re-gen the file you may loose it, so use a partial class to add functionality to the generated code if you can. 

In the example the BusinessObjectUpdateBatch.cs file was generated.

So, step one from above is taken care of and we have our managed code version of our data contract.  I’ll refer to this as our contract object(s).  Now all that remains is to translate our objects into the new structure and serialize it.

I’ve taken to either having a simple translator method or helper in the data access class (if the structure is really small) or adding a constructor in a partial class for the generated code to do the mapping.  The helper can simply take the object you are wanting to update (or the collection of objects) and pass that into a method or constructor that steps through and generates an instance of the contract objects fully populated.  You can see that I’ve done this in the example with the BusinessObjectUpdateBatch.Partial.cs file.  Note that I added a constructor to the batch record object to make things cleaner in the helper functions.  This constructor was put in the partial class file so that if the schema changes and we re-gen the contract object classes we don’t loose this code.

Once the contact objects are populated all we need to do is serialize them with the XmlSerializer and then we have a XML string that we can pass along as a database parameter to the stored procedure.  I’ve added this ability via the partial class again in the form of a static Serialize method on the batch object.

Now we’re done, let’s see if it worked.  Take a look at the Program.cs file in the example and you’ll see that I generate a couple of BusinessObjects that need to be saved.  I then pass that to a DataAccess method to perform the save.  The business objects are translated to our contract objects, serialized and then saved (or displayed on the console so you can see the outcome).  Pretty slick, eh?

Okay, so why go through all this work (and really, it’s not that much work)?  Let’s take a look some options on how we could have done this differently:

  1. We could have created a System.Text.StringBuilder and just created our XML completely manually by looping through each business object in the list and creating our XML representation.  Ouch.  Painful and error prone, especially when generating XML.
  2. We could have created a System.Xml.XmlTextWriter and looped through each business object constructing the xml manually.  This works and is better than option 1 because you know it is valid XML being generated.  But if you have ever had to manually create XML using the XmlTextWriter you know how much of a pain this can be.  Why not let the XmlSerializer do the work for us?
  3. We could have taught the BusinessObject how to be serialized by providing the correct attributes and such so that the XmlSerializer could just serialize it and skip the whole translation bit.  Well, this is certainly true and cuts out several steps; however, then our business object is locked into being represented in XML only one way.  What if we need to pass it to a different save method and the XML needs to look different?  What if we don’t own the BusinessObject code?  By using the separate BusinessObjectUpdateBatch object we can keep our needs separated and we have a perfect match for the data contract we specified.  You may have noted that the BusinessObject had a property that wasn’t serialized and passed along (SomeIntegerValue property).  Our data contract didn’t need that information, so why send it?  The translation and contract objects allowed us to tailor our update to the situation.
  4. We could have used an ORM package…..yeah, that’s a completely different approach and outside the scope of this post. The method described above works well in case your project decided to NOT use an ORM.  This method also works well if your database isn’t the target for these updates…perhaps you’re serializing these updates to send to a partner via a message.

The benefits for the method I describe are:

  • You get a perfect match with the data contract (as long as the schema is correct).
  • You can re-generate the contract classes simply by updating the schema file, dropping to the command prompt and running the GenerateXXXX.cmd. (If you used constructors in your mappings you may need to update those in your partial classes).
  • You work with objects, not strings or XmlWriters.
  • You don’t have to be a schema power user to create the schema, just come up with the XML and use Visual Studio’s Create Schema feature to generate the .xsd file for you.

One drawback I’ve seen is you may need to play with the schema element names a little.  For example, in the sample code the property name of the array of BusinessObjectUpdateBatchRecord objects on the BusinessObjectUpdateBatch object is not plural.  Not that big of a deal, but could annoy some people.

Once you do this a couple of times the set up becomes incredibly easy.  I was able throw the example code together in about five minutes (thanks to using CodeRush templates for the objects properties, fields, etc.). 

So what do you think?  Do you like this method?  Where do you see holes, or how would you do it differently (besides using an ORM)?  We use this in a few places within our code-base on my current project and it works great.

Download the Sample Code.