XSLT Object Relational Mapping

• Download source files - 247 Kb

Introduction

This code comes as a result of a prototype project to find a method of mapping data between the relational database representation and the object representation. The thought process went something like this:

• ADO.NET can represent its objects as XML
• C# makes it easy to serialize objects (well designed ones at least) to XML
• XSLT gives a straightforward way of transforming one XML structure to another

Not the most complicated of thought processes to follow (fortunately).

So the idea was to write some code to map relational data structures from ADO.NET into the object oriented data representation using XSLT. We wanted to work with an OO model to make the coding of the business objects cleaner, and to work with a relational database for persistence. The thought was that the ADO.NET objects should allow us to persist to the database with relative ease, or at least have dealt with lots of the problems.

Excuses 'n stuff...

So I said this was a play project, right? Some excuses up front:

• It's all in a single console app.
• It all has a root namespace of ConsoleApplication1 (imaginative).
• The ZIP contains loads of random classes created for various other ideas (messy).
• The Class1 Main entry point method contains the test bed code (that we will walk through here) along with loads of other random test bed code.
• Plenty of cheese to be found within the code (I will highlight some as we go through, the rest you can find).

This example is based on the ubiquitous Northwind database and is set up (with the hard coded connection string) to use an SQL Server database on the local machine (sa and no password!). You can change this in the DataSetFiller class. The object side of things is similarly cheesy. Customer and Order objects are delivered within serializable collections CustomerCollection and OrderCollection. The objects are designed to be real but do not differ in structure from the relational representation at all. You will have to use your imagination!

The code...

Probably the best way to go through this is to go work through the test bed code and fall through to the deeper stuff as we go.

 Mapping.ModelMapping modelMap = 
       Mapping.ModelMapping.CreateMapping(@"..\..\ModelMap.xml");

This first line is creating an instance of the ModelMapping class which describes the entities (the database entities and objects), the mapping between entities (database column and the object property/field), and also the relationships between the entities. This is a simple collection of objects and is designed to be serializable for a couple of reasons; the first is that it makes it really easy to get an instance from an XML file on disk as we are doing here (I am after all a lazy programmer or should I say efficient - always mix those two up); the second reason we will get too later.

    //get customers objects - factory gets from sql database and returns objects

    CustomerFactory fact = new CustomerFactory(modelMap);
    CustomersCollection customers = fact.Find(new Customer(), "Orders");

This bit is the the funky bit. The CustomerFactory class is designed to return the CustomerCollection object based on overloaded Find methods. The Find method does all the work of getting the data out of the database, re-modeling it, and returning the object collection. The factory is instantiated with the ModelMapping instance, and the Find method is passed a Customer instance (which would be a search by example overload but it's hard coded) and a string to represent the span of sub items to extract. The span is probably a wee bit cheesy and might be done better, but it is designed to allow a choice as to the depth of fill required (this would allow us to get a simple CustomerCollection with only the Customer details filled if required).

The Find method itself fills an ADO.NET dataset based on the constructed SQL, then calls the ModelMapping.Conversion method with the dataset and the type to construct.

    CustomersCollection customers = 
                     (CustomersCollection)m_model.Conversion(ds, 
                     typeof(CustomersCollection));

Now is where it gets cool. The ModelMapping.Conversion method firstly calls the RelationalToObject method which loads an XSLT from a compiled resource (RelationalObject.xsl), then applies this transform to the serialized XML representation of the ModelMapping instance. This results in a data specific XSLT to model the dataset data into the objects data. Sweet!! We use the meta data within the ModelMapping class to create a specific XSLT. Note that the built in XSLT is pretty funky as it is designed to create another XSLT specifically based on the data structures defined in the ModelMapping. Oh yeah - creating an XSLT from an XSLT; that was the second reason why the ModelMapping class is designed to be serializable.

    //create model transform

    ModelTransforms txs = new ModelTransforms();
    XslTransform xsl = txs.RelationalToObject(this);

    //get xml document from dataset - hard code name for transforms

    data.DataSetName = "MappingDataSet";
    XmlDocument xml = new XmlDataDocument(data);

    //transform relational representation to serialized object

    XmlReader rdr = xsl.Transform(xml, null);

    //deserialse

    XmlSerializer xsr = new XmlSerializer(objectType);
    return xsr.Deserialize(rdr);

Once the Conversion method has created the relational to object XSLT, it simply applies the transform to the dataset data (represented as an XmlDocument), then de-serializes the resultant data and returns the object. The other side of the function applies a cast to CustomerCollection, as this function just returns an object. Note the cheesy hard coding of the dataset name; this is fixed in the XSLT, I couldn't think of a way of inferring the name into the XSLT.

The rest of the main code just lists the customers, proving that we have related the orders to them. Then it's the route back. Pretty similar really. Again, it's an XSLT from an XSLT (different one though), and a transform to take the serialized object back into an ADO.NET dataset. The extra bit of code just proves that you can change the data in the object, and have this change back in the dataset. Pretty obvious really!

To wrap up...

Simply put, we use a pre-defined XSLT to transform the ModelMapping serialized XML data and create a structure specific XSLT to re-model the relational data to objects and vice versa.

This code is pretty cool - even though I do say so myself, it demonstrates many XML techniques with C#, including serialization, the use of XSLT, and the creation of XSLT from XSLT. Perhaps more importantly, it demonstrates how the .NET supports for XML can give another potential solution to a common real world OO application problem.

and the problems...

We decided not to put this idea into production (another reason why it is still a console app). The main reason is the fact that when you think about it, the solution does not lend itself to easy debugging. There are two big steps in the process that are hidden. Firstly, the transform between the representations, and secondly, the serialization to create the objects. We could see ourselves spending long hours tracking down problems in the mapping, spending time looking at the generated XSLT, or trying to fathom why a property was not being de-serialized or a column being lost in the dataset. Probably a little bit wet, but we bottled it, and decided to be a bit more long hand. At the end of the day, the code is probably just a little too concise for its own good.

Another issue to mention with this code is performance. If you remove the hard coded 'A%' where clause restriction from the SQL and leave an open query to return all rows, you will find that it takes ages! The XSLT is pretty slow - and I'm sure with a bit of work could be made better, the other factor is the de-serialization (which we are also told will be quicker in .NET V2).