This article will describe how to design a data access layer for a set of entities. You'll learn how to write an XSD schema and design two simple helper classes -- one for reading an XML stream from SQL Server using SQLXML and another for deserializing the XML stream.
Technologies used:
SQLXML libraries are free from Microsoft (http://www.microsoft.com/downloads/results.aspx?productID=&freetext=SQLXML&DisplayLang=en)
A common problem for many developers is designing a reusable data access layer that can be easily modified and performs well.
This is just one of many systems I've developed for mapping database tables to objects in order to avoid hard coding classes that go through datareaders and setting properties of objects from results fields.
The idea comes from SQL Server 2000 and its support to return XML as a response to a query. Using XSD schemas I can define the exact structure of that XML (see MSDN: Creating XML Views by Using Annotated XSD Schemas).
I can also serialize/deserialize quite easily any type of object in .NET as XML, so I assume that if SQL Server gives me the right XML structure I can instantiate objects from that.
I'll jump into a simple example and its implementation. I'll start from the database design and use the Northwind sample database for displaying orders data (as picture below show):
Figure 1
Here's a look at the application's classes with respect to database structure:
Figure 2
Download the sample code and check the classes in the Visual Studio class browser to find all the properties that should map to table fields in the database.
Now, if your OrderCollection is the business object on which you want to operate, you have to fill it with data from SQL Server.
A normal approach in many applications is to open a connection, query a de-normalized view or get a multiple result set and cycle and create entity classes and add them in the collection, and so on.
With this approach, you'll just deserialize an XML stream and let the framework do the job for you.
This is how the XML serialized version of the object is formatted, just so you know what must be returned to the application.
OrderCollection orders = new OrderCollection();
for(int i=1;i<=3;i++)
{
Order o = new Order();
o.Freight = 1.2M;
o.OrderDate = System.DateTime.Now;
o.OrderID = i;
o.RequiredDate = DateTime.Now.AddDays(30);
o.ShipAddress = "103 Park Avenue";
o.ShipCity = "Miami";
o.ShipCountry = "USA";
o.ShipName = "n/a";
o.ShipPostalCode = "72100";
o.ShipRegion = "Florida";
o.OrderLines = new OrderDetailCollection();
for(int li=1;li<=3;li++)
{
OrderDetail ol = new OrderDetail();
ol.Item = new Product();
ol.Item.ProductID = li;
ol.Item.ProductName = "Testing Product";
ol.Item.UnitPrice = 12;
ol.Quantity = Convert.ToInt16(li * 5);
ol.Discount = 0;
o.OrderLines.Add(ol);
}
orders.Add(o);
}
XSerializer.serialize(@"c:\test.xml",orders);
Included in the downloadable sample code is a simple helper class for serializing/deserializing objects. You'll then run test code for filling the OrderCollection with test data, serialize it to disk, and present the XML.
With this code, you create a collection of three orders with three lines each. This is the serialized version:
<?xml version="1.0" encoding="utf-8"?>
<ArrayOfOrder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Order>
<Freight>1.2</Freight>
<OrderDate>2004-05-
28T21:53:50.2656250+02:00</OrderDate>
<OrderID>1</OrderID>
<OrderLines>
<OrderDetail>
<Discount>0</Discount>
<Item>
<ProductID>1</ProductID>
<ProductName>Testing Product
</ProductName>
<UnitPrice>12</UnitPrice>
</Item>
<Quantity>5</Quantity>
</OrderDetail>
<OrderDetail>
<Discount>0</Discount>
<Item>
<ProductID>2</ProductID>
<ProductName>Testing Product
</ProductName>
<UnitPrice>12</UnitPrice>
</Item>
<Quantity>10</Quantity>
</OrderDetail>
<OrderDetail>
<Discount>0</Discount>
<Item>
<ProductID>3</ProductID>
<ProductName>Testing Product&
#060;/ProductName>
<UnitPrice>12</UnitPrice>
</Item>
<Quantity>15</Quantity>
</OrderDetail>
</OrderLines>
<RequiredDate>2004-06-
27T21:53:50.2812500+02:00</RequiredDate>
<ShipAddress>103 Park Avenue</ShipAddress>
<ShipCity>Miami</ShipCity>
<ShipCountry>USA</ShipCountry>
<ShipName>n/a</ShipName>
<ShippedDate>0</ShippedDate>
<ShipPostalCode>72100</ShipPostalCode>
<ShipRegion>Florida</ShipRegion>
</Order>
<Order>
<Freight>1.2</Freight>
<OrderDate>2004-05-
28T21:53:50.2812500+02:00</OrderDate>
<OrderID>2</OrderID>
<OrderLines>
<OrderDetail>
<Discount>0</Discount>
<Item>
<ProductID>1</ProductID>
<ProductName>Testing Product
</ProductName>
<UnitPrice>12</UnitPrice>
</Item>
<Quantity>5</Quantity>
</OrderDetail>
<OrderDetail>
<Discount>0</Discount>
<Item>
<ProductID>2</ProductID>
<ProductName>Testing Product&
#060;/ProductName>
<UnitPrice>12</UnitPrice>
</Item>
<Quantity>10</Quantity>
</OrderDetail>
<OrderDetail>
<Discount>0</Discount>
<Item>
<ProductID>3</ProductID>
<ProductName>Testing Product
</ProductName>
<UnitPrice>12</UnitPrice>
</Item>
<Quantity>15</Quantity>
</OrderDetail>
</OrderLines>
<RequiredDate>2004-06-
27T21:53:50.2812500+02:00</RequiredDate>
<ShipAddress>103 Park Avenue</ShipAddress>
<ShipCity>Miami</ShipCity>
<ShipCountry>USA</ShipCountry>
<ShipName>n/a</ShipName>
<ShippedDate>0</ShippedDate>
<ShipPostalCode>72100</ShipPostalCode>
<ShipRegion>Florida</ShipRegion>
</Order>
</ArrayOfOrder>
You can have different results in this format to increase performance or merely meet your needs. I suggest that you look at these articles regarding serialization and using attributes to change XML format:
Now that you know what kind of XML to expect for the OrderCollection, you need to design a schema that matches the structure. You may also want to investigate the xsd.exe tool (http://msdn.microsoft.com/library/en-us/cptools/html/cpconXMLSchemaDefinitionToolXsdexe.asp) for autogenerating XSD, classes, and typed datasets. But for now start from the Order complex type:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-
microsoft-com:xml-msdata" elementFormDefault="unqualified"
attributeFormDefault="unqualified">
<xs:complexType name="Order">
<xs:sequence>
<xs:element name="Freight" type="xs:decimal"
sql:datatype="money"/>
<xs:element name="OrderID" type="xs:int"
sql:field="OrderID"/>
<xs:element name="OrderDate" type="xs:dateTime"/>
<xs:element name="RequiredDate" type="xs:dateTime"/>
<xs:element name="ShippedDate" type="xs:dateTime"/>
<xs:element name="ShipAddress" type="xs:string"/>
<xs:element name="ShipCity" type="xs:string"/>
<xs:element name="ShipRegion" type="xs:string"/>
<xs:element name="ShipPostalCode" type="xs:string"/>
<xs:element name="ShipCountry" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:element name="Order" type="Order" sql:relation="Orders" sql:key-
fields="OrderID"/>
</xs:schema>
In the complex type, define just the structure of the Order. Each element represents a property of name and type (open SQLXML online help to discover how granular the definition of each element can be). If you don't specify the sql:field attribute, then the element name will assume the database column name.
Now you can insert an element that reflects the complex type and gives the table/view using the sql:relation attribute and the primary keys with the sql:key-fields.
To run the query, you need to import the SQLXML assembly in the project. It's time to install the libraries, so if you haven't done it yet go to http://www.microsoft.com/downloads/results.aspx?productID=&freetext=SQLXML&DisplayLang=en and download and run the installation.
You can find the Microsoft.Data.SQLXML.dll in the GAC. From Visual Studio you can add it from the main tab as the picture below shows.
Figure 3
Then you need another helper class to execute the query using SQLXML, passing the schema and returning an XMLReader to use later for deserialization:
private static SqlXmlCommand getCommand(string xpathQuery,string schemaPath,string rootTag)
{
SqlXmlCommand retVal =
getCommand(xpathQuery,schemaPath);
if (string.Empty!=rootTag) retVal.RootTag =
rootTag;
return retVal;
}
private static SqlXmlCommand getCommand(string
xpathQuery)
{
SqlXmlCommand retVal = getCommand();
retVal.CommandType = SqlXmlCommandType.XPath;
retVal.CommandText = xpathQuery;
return retVal;
}
private static SqlXmlCommand getCommand(string
xpathQuery,string schemaFile)
{
SqlXmlCommand retVal = getCommand(xpathQuery);
retVal.SchemaPath = ConfigurationSettings.AppSettings["sqlxmlSchemasFolder "]+
schemaFile;
return retVal;
}
public static XmlReader executeXmlReader(string
xpathQuery,string schemaPath,string rootTag)
{
SqlXmlCommand cmd =
getCommand(xpathQuery,schemaPath,rootTag);
return cmd.ExecuteXmlReader();
}
SqlXmlCommand definition is pretty simple. You need to provide the path to the XSD schema (To simplify maintenance the directory path of these files has been declared in the app.config [see configuration section below]), set the XPath query, and provide, when the result is not a single record, the root tag name so the resulting XML will have the root element that you expect.
<appSettings>
<add key="sqlxmlConnString"
value="Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial
Catalog=Northwind;Data Source=.;"/>
<add key="sqlxmlSchemasFolder" value="D:\Documents and
Settings\luca\My Documents\Visual Studio
Projects\sqlxml_deserialization\OrdersMgmt\DAL\schemas\"/>
</appSettings>
You'll have to modify these values to reflect your workstation configuration but keep in mind that until now, SQLXML only worked using the OLEDB provider.
Write this test script in the client application and run it:
// we execute the reader with all Orders based on the Orders.xsd
// with ArrayOfOrder as root node
XmlReader reader =
sqlxmlHelper.executeXmlReader("/Order","Orders.xsd","ArrayOfOrder");
reader.MoveToContent();
string xmlstring = reader.ReadOuterXml();
reader.Close();
// lets write the content to a file so we can see
if match the test.xml
// and can be deserialized as the OrderCollection
StreamWriter writer = new
StreamWriter(@"c:\testFromSQLServer.xml");
writer.Write(xmlstring);
writer.Flush();
writer.Close();
Open the c:\testFromSQLServer.xml file and examine the results from SQL Server:
<ArrayOfOrder>
<Order>
<Freight>32.38</Freight>
<OrderID>10248</OrderID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<RequiredDate>1996-08-01T00:00:00</RequiredDate>
<ShippedDate>1996-07-16T00:00:00</ShippedDate>
<ShipAddress>59 rue de l'Abbaye</ShipAddress>
<ShipCity>Reims</ShipCity>
<ShipPostalCode>51100</ShipPostalCode>
<ShipCountry>France</ShipCountry>
</Order>
.... All the Orders are xml nodes
</ArrayOfOrder>
This XML stream reflects the base structure of the OrderCollection; you can deserialize it and create the actual object.
public static object deserialize(XmlReader reader ,object source)
{
XmlSerializer ser = new
XmlSerializer(source.GetType());
MemoryStream ms;
StreamWriter writer = null;
try
{
reader.MoveToContent();
string xmlstring = reader.ReadOuterXml();
reader.Close();
ms = new MemoryStream();
writer = new StreamWriter(ms);
writer.Write(xmlstring);
writer.Flush();
ms.Position = 0;
source = ser.Deserialize(ms);
ms.Close();
writer.Close();
}
catch(InvalidOperationException iex)
{
if(reader.ReadState != ReadState.Closed)
reader.Close();
if(writer!=null)
writer.Close();
throw new Exception("error deserializing
object from xml reader",iex);
}
finally
{
if(reader.ReadState != ReadState.Closed)
reader.Close();
if(writer!=null)
writer.Close();
}
return source;
}
Please note that the code can definitely be improved, as I haven't concentrated on optimizing it. Here I've just created a method to work with an existing instance of an object, deserialized it from an XmlReader, and returned it to the caller.
You are now ready to bind the OrderCollection to a simple grid with three other lines of code:
OrderCollection orders = new OrderCollection();
orders = (OrderCollection)
XSerializer.deserialize(sqlxmlHelper.executeXmlReader("/Order","Or
ders.xsd","ArrayOfOrder"),orders);
this.grd_orders.DataSource = orders;
See my simple form in the picture below:
Figure 4
For each Order you have a property of OrderLines that still hasn't been filled with data. This is where things become really interesting. In the schema definition you need to add new complex type for OrderLine and the related Product entity:
<xs:complexType name="OrderDetail">
<xs:sequence>
<xs:element name="Quantity" type="xs:int" sql:datatype="smallint"
/>
<xs:element name="Discount" type="xs:float" sql:datatype="real"
/>
<xs:element name="Item" type="Product"
sql:relationship="OrderDetailProduct" sql:relation="Products"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Product">
<xs:sequence>
<xs:element name="ProductID" type="xs:int" sql:datatype="int"
/>
<xs:element name="ProductName" type="xs:string"/>
<xs:element name="UnitPrice" type="xs:decimal" sql:datatype="money"
/>
</xs:sequence>
</xs:complexType>
Notice there is an element called Item in the OrderDetail of type Product to reproduce the same class structure and XML serialized format.
Now you can include OrderLines in the Order by declaring a new element named OrderLines and set it as sql:is-constant="true" to avoid the generation of XML nodes for each child record, and more important, to determine which relationship declared in the schema represents the relation to join child elements sql:relationship="OrderDetails".
<xs:element name="OrderLines" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderDetail" type="OrderDetail"
sql:relationship="OrderDetails" sql:relation="[Order Details]"/>
</xs:sequence>
</xs:complexType>
</xs:element>
Relationships are declared at the top of the document:
<xs:annotation>
<xs:appinfo>
<sql:relationship name="OrderDetails" parent="Orders"
parent-key="OrderID" child="[Order Details]" child-key="OrderID"/>
<sql:relationship name="OrderDetailProduct"
parent="[Order Details]" parent-key="ProductID" child="Products" child-
key="ProductID"/>
</xs:appinfo>
</xs:annotation>
This should not be difficult to understand. It looks like a foreign key declaration, multiple keys are comma separated, and parent and child are usually tables but can also be views.
Run the test application again, and by clicking on the OrderLines this time, you can see them:
Figure 5
You have learned a way to use SQLXML and serialization to abstract a data access layer for retrieving hierarchical structures of classes. Using this schema and adding filters to an XPath query, you can retrieve only a subset of one order. This is really a powerful way to fill data from SQL Server in .NET objects.
