SQL Server XML Support and .Net Serialization
by Brian Rush

  Download Source Code
In this article I want to accomplish two main goals. The first goal is to provide a concrete example of how the power of SQL Server XML support and .Net object serialization can be leveraged within applications. One specific benefit of using these features is applications can be set up so all the data related stored procedures and methods need to concern themselves with only one parameter, an XML file. In the past, varying amounts of parameters sometimes became a pain in the neck In the case of a new parameter, this typically meant going back to change all the pertinent methods and stored procedures wherever applicable not to mention the whole binary compatibility issue from the VB days of old.
The second goal is to show how to the above idea can be laid out into, to quote Robbe Morris's article ASP.NET Beginner's Guide To Creating UI, Business, and Data Layers in Applications, a reasonably well designed application. I very much agree with Robbe's article and hope my article reinforces his thoughts through a small concrete example. I tend to think that if I can understand how data flows through the tiers of a system, understand the role of each tier, and I can grasp the boundaries of each tier then I am way ahead of the game. I hope this small application makes these three notions apparent.
Last, what I am presenting here is nothing really new per se, but rather the amalgamation of some very useful articles listed below.
1.ASP.NET Beginner's Guide To Creating UI, Business, and Data Layers in Applications
2.Demystify SQL Server FOR XML EXPLICIT
3.How to pass array values into SQL Server stored procedure using XML and Visual Basic.Net
So all that being said, let's get to the details.
I am going to start with goal two, namely discussing how the sample application (code is included below) is structured. The reason being is that the application structure provides the foundation on which our first goal is built upon. A few comments about the sample application; I downloaded the SQL Express 2005 AdventureWorks database and created a simple form that provides the ability to view information about employees in the AdventureWorks database. This was completely contrived for the purpose of this article. The layers of the sample application are defined as follows.
Thin Model Tier The most important tier as it pertains to this article is the Thin Model Layer. The thin model layer is nothing more than classes that define public properties of our application entities. We use these classes as a mechanism to hold data that can be passed between tiers. These objects are all serializable so they can be represented as XML. There is no reason why these objects could not be auto generated. Note, I would have used interfaces here but some comments as to why I did not will come later. In the sample application the Thin Model Tier is made up of the ModelLayer project. More specifically there is a class called Employees that holds all the public properties of our employees.
Presentation Tier - This tier is strictly for UI purposes. The presentation tier knows about the thin model layer and the control layer. The presentation layer asks the control layer to execute the non UI related logic. In the sample application the Presentation Tier is made up of the SampleApp project.
Controller Tier - The controller layer is responsible for "asking" the business layer to do something based on user interaction with the presentation tier. In the sample application, the Controller Tier is made up of the ControllerLayer project.
Business Tier - The Business Tier contains all the business logic for working with Employees. For example, maybe calculating monthly pay etc. It also interfaces with the Data Tier to store and retrieve employee information. In the sample application, the Business Tier is made up of the BusinessLayer project.
Data Tier - The Data Tier contains all the logic to interact with underlying data storage. In this case the SQL Express 2005 database. In the sample application, the Data Tier is made up of the DataLayer project.

With this covered let's now get into goal two, namely seeing how XML and object serialization can be leveraged.
First off, we can have a stored procedures defined in SQL server that returns XML. In this example I created the stored procedure below:

ALTER PROCEDURE [dbo].[GetEmployees] 
-- Add the parameters for the stored procedure here
@params as ntext

DECLARE @hDoc int
--Read in the XML file that we passed in
exec sp_xml_preparedocument @hDoc OUTPUT, @params


SELECT '<Employees>'

SELECT Employee.*, EmployeeAddress.*, AddressInfo.*
	HumanResources.Employee Employee
	inner join OPENXML (@hDoc, 'EmployeeAddress', 1) WITH (EmployeeID  int) x
	on Employee.EmployeeID < x.EmployeeID 
	inner join HumanResources.EmployeeAddress EmployeeAddress
	on Employee.EmployeeID = EmployeeAddress.EmployeeID
	inner join   Person.Address AddressInfo 
	on EmployeeAddress.AddressID = AddressInfo.AddressID

SELECT '</Employees>'

--Close out the XML File
EXEC sp_xml_removedocument @hDoc

We see that this stored procedure takes one parameter of type ntext. This parameter accepts the xml of a serialized model object. This xml carries along the parameters that we will use in the stored procedure. In this case, we are using the <EmployeeAddress EmployeeID="10" /> portion of our XML string. This XML string was created by serializing our thin model object. In short, we will initialize our thin model with the parameters that we want to be used in our where clause. It is important to note here that the XML file could carry other attributes we may want to use at a latter time. As long as they are on the model we are good to go.
In order to serialize our model object, we call method SerializeObject located in the BaseModel.vb class of the ModelLayer project. In short this method converts an object to XML via .Net serialization.
Public Function SerializeOject() As Xml.XmlDocument

  Dim anXML As New Xml.XmlDocument
  Dim StringWriter As New System.IO.StringWriter
  Dim objArray(0) As ModelLayer.BaseModel
  Dim XmlSerializer As System.XML.Serialization.XmlSerializer


    objArray(0) = Me

    Dim typeOfObject As Type = System.Type.GetTypeArray(objArray)(0)

    XmlSerializer = New System.Xml.Serialization.XmlSerializer(typeOfObject)

    XmlSerializer.Serialize(StringWriter, Me)


    Return anXML

  Catch ex As Exception
            Throw ex

     StringWriter = Nothing
     XmlSerializer = Nothing
     objArray = Nothing
 End Try

End Function

Once we have our object serialized, we call the GetXML method in the DataLayer.DataAccess.vb class. This method accepts the name of stored procedure to be executed and the serialized object. The code looks like the following:
 Public Function GetXML(ByVal storedProcedure As String, ByVal xmlParams As Xml.XmlDocument) As Xml.XmlDocument
 Dim myDataConnection As ADODB.Connection
 Dim myDataCommand As New ADODB.Command
 Dim objStream As New ADODB.Stream
 Dim anXMLDocument As Xml.XmlDocument


   myDataConnection = Me.GetConnection()
   'Create our command statement
   myDataCommand.CommandType = CommandType.StoredProcedure
   myDataCommand.ActiveConnection = myDataConnection
   myDataCommand.CommandText = storedProcedure
   'pass in the xml file that holds all the parameters
   myDataCommand.Properties("Output Stream").Value = objStream
   myDataCommand.Execute(, , 1024)
   objStream.Position = 0
   anXMLDocument = New Xml.XmlDocument
   Return anXMLDocument
 Catch ex As Exception


   myDataConnection = Nothing
   myDataCommand = Nothing
   objStream = Nothing

 End Try

 End Function  

Executing GetXML will give us the results from the database as an XML file. We can then take the resulting XML and deserialize it back into our model tier objects.
A sample output from the GetXML method that executes the GetEmployees stored procedure looks like the following:

<Employee EmployeeID="1" NationalIDNumber="14417807" ContactID="1209" LoginID="adventure-works\guy1" 
           ManagerID="16" Title="Production Technician - WC60" BirthDate="1972-05-15T00:00:00"
           MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00" SalariedFlag="0" 
           VacationHours="21" SickLeaveHours="30" CurrentFlag="1" 
           rowguid="AAE1D04A-C237-4974-B4D5-935247737718" ModifiedDate="2004-07-31T00:00:00">
  <EmployeeAddress EmployeeID="1" AddressID="61" rowguid="77253AEF-8883-4E76-97AA-7B7DAC21A2CD"
    <AddressInfo AddressID="61" AddressLine1="7726 Driftwood Drive"
    City="Monroe" StateProvinceID="79" PostalCode="98272" 
                ModifiedDate="1996-07-24T00:00:00" />

In order to deserialize this XML into our model tier we invoke method DeserializeObject located in the BaseModel.vb class of the ModelTier project. The code looks like the following:

 Public Function DeSerializeObject(ByVal anXML As Xml.XmlDocument) As Object
  Dim objArray(0) As ModelLayer.BaseModel
  Dim XmlSerializer As System.XML.Serialization.XmlSerializer
  Dim returnObject As Object
  Dim typeOfObject As Type
  Dim vr As XmlTextReader


    objArray(0) = Me
    typeOfObject = System.Type.GetTypeArray(objArray)(0)
    XmlSerializer = New System.Xml.Serialization.XmlSerializer(typeOfObject)

    vr = New XmlTextReader(New StringReader(anXML.InnerXml))
    Dim serializer As New XmlSerializer(Me.GetType)
    returnObject = serializer.Deserialize(vr)
    Return returnObject

  Catch ex As Exception
            Throw ex

    XmlSerializer = Nothing
    vr = Nothing
    objArray = Nothing
  End Try

 End Function

I think one of the coolest things about deserialization is that you can initialize an object in the model layer very quickly even when it holds onto nested objects. For example, our sample Employees class holds onto a collection of Employee objects which in turn holds onto a collection of EmployeeAddresses objects which finally holds onto a collection of AddressInfo objects.
As we can see, we are using the thin model layer to pass parameters into SQL Server.
I want to mention a few things about serialization and why I did not use interfaces in the thin model layer. The XMLSerializer class has the limitation that it can only serialize public properties and these properties can not return interfaces. It would be nice if you could specify the default object to create on serialization when a property returns an interface.
Next, the same logic of returning XML from stored procedures can be applied to inserts. I can see how passing XML back and forth to the database can result in less database hits because we can gather up all the info that we either need to select or want to insert.
If you are interested in investigating further I recommend the following:
1.Download SQL Express 2005
2.Install the Adventure Works database
3.Create the GetEmployees stored procedure listed above
4. Step through the code starting at the frmEmployees_Load