Unique generic data-access layer

I’ve always been interested in “write-once, use-many“ data-access layers. It’s like some sort of Holy Grail to me. I don’t like to spend too long writing data components before I can get started on the business or runtime logic. It came as quite a shock to me to see that Microsoft has come up with ObjectSpaces. It distantly resembles what I came up with this summer. I wrote a data-access layer based on SqlXml of SQL Server. Now that it has been written, coding on the DAL is no longer required. It is a quite useful DAL, but by no means perfect and it has some limitations (you could even say that some are huge). For simple projects it will serve it’s purpose, though.

Here’s how I came up with the idea: did you ever notice the resemblance between XML that is returned when you call myDataSet.GetXml() and the XML returned from SqlXml when you do a SELECT * FROM Customers FOR XML AUTO, ELEMENTS? That started me thinking. You have lots of control over how your XML is returned from SqlXml if you use a XML Schema. You will need to make some SqlXml annotations inside of the XSD file, but no major changes are required. Do we have an XSD editor? Yep, VS.NET has both a XML Schema editor and its DataSet editor (which are essentially the same), where you can create and design your typed DataSets. When you take the XML Schema from your typed DataSet, add the SqlXml annotations and make some minor adjustments (which the XSD editor thankfully doesn’t touch), you can get XML from your SQL Server database and read it into your typed DataSet object. An annotated XML Schema for a typed DataSet looks like this:

<?xml version=1.0 encoding=utf-8?>

<xs:schema id=Bloggers elementFormDefault=qualified

    attributeFormDefault=qualified xmlns:xs=http://www.w3.org/2001/XMLSchema



  <xs:element name=BloggersDataSet msdata:IsDataSet=true sql:is-constant=1>


    <xs:sequence maxOccurs=unbounded>

      <xs:elementname=Bloggers sql:relation=BloggersTable>



            <xs:element name=BloggerID

              msdata:DataType=System.Guid, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

type=xs:string sql:datatype=uniqueidentifier/>

            <xs:element name=Name type=xs:string/>

            <xs:element name=Email type=xs:string minOccurs=0/>

            <xs:element name=Url type=xs:string minOccurs=0/>

            <xs:element name=Created type=xs:dateTime sql:datatype=datetime/>

I’ve put the changes in bold.

  • A namespace declaration for SqlXml
  • A <xs:sequence> element instead of the usual <xs:choice>
  • Datatypes for Guid and DateTime must be specified as the corresponding sql:datatype
  • If there’s a mismatch between the DataTable name of the DataSet and the table in the Sql Server database, the sql:relation should specify the name of the database table.
  • When there is more than one DataTable and a relationship exists between these, it should also be specified inside a element like this:



    <sql:relationship parent=Postings parent-key=PostingID child=Comments child-key=PostingREF/>



The DataSet was created by simply dragging the table(s) from the Server Explorer into the designer. You could write a little tool that will make the changes for you. That’s for later.

With this, you can make changes to the DataSet object’s data and hand it over to a SqlXmlAdapter object that will save all changes back into the database using the same XSD schema. This even works for DataSets consisting of multiple DataTables. So, all you need to do is define your DataSet and you’re good to go for retrieving and saving data. Well, almost anyway. What you also need is a way to do queries on your data. You do not always want to retrieve all data from your database. There is a very elegant mechanism provided by SqlXml, based on XPath queries placed inside so-called XML template files. Here’s an example of what they look like:


  <sql:header xmlns:sql=urn:schemas-microsoft-com:xml-sql>

    <sql:param name=BloggerID></sql:param>


  <sql:xpath-query xmlns:sql=urn:schemas-microsoft-com:xml-sql





The XPath expression does the equivalent of the WHERE clause in a SQL statement. The XML template files can be parametrized, allowing you to create the function-like XPath queries.

To tie it all together, I created a class SqlXmlObjectProvider. It uses the managed SqlXml classes to perform its magic. The amount of code is minimal. SqlXml takes care of most of the work. The core of the class is formed by two methods called GetObjectData and StoreObjectData. The code for these looks like this (note that is not the complete code, just the essential parts):

public DataSet GetObjectData(Type dataSetType, string viewName, Hashtable parameters)



  // Create an instance of typed DataSet

  DataSet theSet = (DataSet)Activator.CreateInstance(dataSetType);

  // Prepare command

  SqlXmlCommand com = new SqlXmlCommand(metadata.ConnectionString);

  com.CommandText = Path.Combine(path, viewName + “.xml”);

  com.BasePath = path;  // Retrieved from config file (not shown)

  com.CommandType = SqlXmlCommandType.TemplateFile;

  // Enumerate all entries in hashtable

  SqlXmlParameter param = null;

  string strKey = “”;

  IDictionaryEnumerator enumerator = parameters.GetEnumerator();

  while (enumerator.MoveNext())


    param = com.CreateParameter();

    strKey = (string)enumerator.Key;

    Debug.Assert(strKey.Substring(0,1) == “@”);

    param.Name = strKey;

    param.Value = enumerator.Value;

    if (enumerator.Value is Guid)


      // Adjust for GUID structure because format has to be without curly braces

      param.Value = ((Guid)enumerator.Value).ToString(“D”);


    if (enumerator.Value is DateTime)


      // Adjust datetime fields to universal format

      param.Value = ((DateTime)enumerator.Value).ToString(“yyyy-MM-ddThh:mm:ss”);





    XmlReader reader = com.ExecuteXmlReader();


    // Because rows have been read from XML, set RowState back to Unchanged



  catch (SqlXmlException ex)


    string error = ExtractSqlXmlException(ex);


    throw new ApplicationException(error, ex);


  catch (ConstraintException ex)


    // Possible reasons:

    // – A real constraint exception because primary-foreign key relation is violated

    // – Fields in DataSet are not set to nillable=”true”

    // – Empty elements for non-existing records in LEFT JOIN queries

    Debug.WriteLine(“Exception: “ + ex.ToString());



  return theSet;



public void StoreObjectData(DataSet objectData)


  // Create a new adapter with correct schema.

  // CommandType, CommandText and RootTag are not necessary

  SqlXmlCommand com = new SqlXmlCommand(metadata.ConnectionString);

  com.SchemaPath = metadata.SchemaFile;

  com.BasePath = Path.Combine(config.BaseSchemaPath, metadata.RelativeSchemaPath);

  adapter = new SqlXmlAdapter(com);



    // Update DataSet to datasource



  catch (SqlXmlException ex)


    string error = ExtractSqlXmlException(ex);


    throw new ApplicationException(error, ex);



I’ll stop here for today. Tomorrow or the day after I’ll finish the story.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s