etting database data into and out of business objects has always been a problem; however, now that SQL Server 2000 supports XML, you can use interesting techniques that capitalize on .NET’s object serialization capabilities to retrieve XML-formatted data from SQL Server and transform it into custom class instances.

People commonly think of the XML returned from SQL Server as documents. If you take that approach, you can use existing techniques for converting existing stored procedures into Web services and transforming the XML documents using XSLT. However, another approach is to think of the returned XML as an XML-serialized object. The object represented by the XML document has never been properly serialized, so you don’t have the definition of the class that generated the XML data. Fortunately, it’s straightforward to construct a class that matches the schema of the data returned and to deserialize the result of a query into an instance of that class. The classes you construct in this manner represent business objects and you can use them to pass data between tiers.

The technique discussed in this article combines several key technologies of the .NET framework, and demonstrates clearly how (and why) Microsoft used XML as the common substrate for much of .NET. The article describes the process required to build a sample application that retrieves orders from the Northwind database and storing them in business objects that represent orders rather than simply putting the data in DataSets that contain related tables. This approach simplifies the coding of the presentation tier, because developers can work with business objects rather than abstract data.

XML Queries
To retrieve the results of a query in XML format, use the FOR XML clause in your queries?write a SQL query as usual and append the clause at the end of the query. For example, the following query retrieves an order from the Northwind database in XML format:

   SELECT   [Order].OrderID AS ID,          [Order].CustomerID As CustID,         [Order].OrderDate,          [Order].Freight,         Detail.UnitPrice As Price,          Detail.Quantity AS Quantity,          Detail.Discount AS Discount    FROM   [Order Details] Detail          INNER JOIN Orders [Order] ON         [Order].OrderID = Detail.OrderID    WHERE    [Order].OrderID = 10912   FOR XML AUTO

Here’s the XML document returned by the preceding query.

The XML document contains Order and Detail elements that correspond to the two tables accessed by the query. Each table’s fields have become attributes of the table’s element. Note that the query uses As alias names so that SQL Server outputs the desired element and attribute names. The last argument in the FOR XML clause (AUTO in the query example) determines the shape of the XML document, and takes one of the following values:

Controlling the Schema of the XML Response
The syntax of the query determines the structure of the XML document that will be generated by SQL Server and therefore the structure of the class that describes the data. The preceding query retrieves the bare minimum of information needed to describe an order; for example, to complete the information you would have to look up the product names. Similarly, there’s no information about the customer who placed the order, beyond a meaningless customer ID. To solve that problem, add a join to the Products and Customers tables so you can retrieve the names of the products that appear in the order, as well as the name of the company and a company contact. The following code excerpt shows a GetOrderByID stored procedure that returns an order, including product and customer information.

      CREATE PROCEDURE GetOrderByID   @OrderID int   AS   SELECT   [Order].OrderID AS ID,  [Order].Freight,            [Order].CustomerID AS CustomerID,            CONVERT(datetime, [Order].OrderDate )                AS OrderDate,             CONVERT(datetime, [Order].ShippedDate )                AS ShipDate,               (SELECT CompanyName                FROM Customers                WHERE Customers.CustomerID=                  [Order].CustomerID)                   AS Customer,                (SELECT ContactName                FROM Customers                WHERE Customers.CustomerID=                  [Order].CustomerID)                   AS Contact,             Detail.ProductID, Detail.ProductName,             CAST(Details.UnitPrice AS numeric(8,2))                As Price,             CAST(Details.Quantity AS int) AS Quantity,             CAST(Details.Discount AS numeric(8,2))                AS Discount    FROM     [Order Details] Details INNER JOIN Orders              [Order]             ON [Order].OrderID = Details.OrderID                INNER JOIN Products Detail                ON Details.ProductID = Detail.ProductID    WHERE    [Order].OrderID = @OrderID   FOR XML AUTO

To make the two columns of the Order Details table used in the query (the UnitPrice, Quantity and Discountcolumns) attributes of the element, rather than separate elements, you must cast the columns into their own type, and SQL Server will associate them with the preceding table (element) name in the query. Note also that the Products table is aliased as Detail, because this is the name of the element we want to see in the XML output. The XML document returned by the GetOrderByID stored procedure is shown here:

Now, the XML document’s root element is and contains as many elements as there are detail lines in the order. All the column values appear as attributes of these two elements. You can include columns from other tables as attributes of an existing element by inserting a subquery in the selection list. The subquery does not introduce another element in the XML document; instead, it creates a new attribute in the preceding element. The GetOrderByID stored procedure shown earlier uses subqueries to retrieve customer information. The subqueries that select columns from the Customers table must appear below the Order table’s fields in the selection list.

Sometimes, you need even more control over the XML format than simple placement can provide. Using the XML EXPLICIT option provides even more flexibility, but you may find that the query syntax becomes too convoluted. XML EXPLICIT queries are hard to write and even harder to read. Still, if your query is too complicated to be implemented efficiently with the techniques discussed here, you can always resort to the explicit formulation of the XML document.

When you’re satisfied with the XML format returned by a query, you can create a class whose field structure reflects the document content.

Generate a Class to Store Query Results
There are two approaches for getting the XML returned by a query into class instances. First, if you set up the query XML to match the serialization format of an existing class that represents an order, you can deserialize the response of the GetOrderByID stored procedure directly into an instance of this class. If you don’t have such a class already, it’s fairly easy to build the class’s definition manually, but there’s a better option: Let the .NET framework generate the class’s code for you. The XSD command-line tool can infer the schema of an existing XML file, as well as convert that schema into the appropriate .NET classes. Even better, you can request that SQL Server generate the schema of the data in addition to the XML data.

Step 1: Generate an XDR Schema. Modify the FOR XML AUTO clause by appending the XMLDATA option, which tells SQL Server to prefix its response with the definition of the schema that describes the structure of the data:

   FOR XML AUTO, XMLDATA

When you do that, SQL Server outputs the schema for the XML document enclosed in a tag. Physically, the schema appears before the actual data returned by the query. You can view the XML schema and data in the Query Analyzer’s window by changing the Execute Mode to “Result in text.” Set the maximum characters per column to 4,000 (the maximum value) on the Results tab of the Options dialog box. Figure 1 shows the Query Analyzer window after the execution of the GetOrderByID stored procedure.

Figure 1: The result of executing the GetOrderByID XML query in SQL Server’s Query Analyzer with the FOR XML AUTO, XMLDATA option

Step 2: Save the XDR Schema. When you run the query, the result appears on a single long line, so the image doesn’t show exactly what you’ll see?I altered the result by formatting the XML document in the lower pane. That’s purely cosmetic. SQL Server outputs an XDR (XML-Reduced Schema) schema format. You can copy the XML schema from the result pane and save it in a text file with the extension .xdr. Place the file in a folder with a short path name, because you will have to enter the file’s complete path name in the next step. Listing 1 shows the schema returned by the GetOrderByID query.

Step 3: Transform the XDR schema into an XSD schema. Next, open a Command Prompt window and switch to the Bin folder under the Framework’s SDK folder. In most installations this folder’s path is C:Program FilesMicrosoft Visual Studio 2003SDKv1.1Bin. In this folder you’ll find the XSD.EXE command-line tool, which you can use to generate a class with the same structure as a specified schema.

Alternatively, you can open a Command Prompt window using the Command Prompt command from the Visual Studio .NET Tools submenu. When you do that, the XSD tool’s location is part of the path and you can switch to the folder where you saved the XDR schema to process it. The following command converts the XDR schema into an XSD schema and saves it to a file in the current folder with the same name and extension XSD (you can also specify the output file name in the command’s arguments), for example:

   xsd c:
worder.xdr

To learn about the syntax of the XSD utility, enter the name of the utility followed by a question mark in the Command Prompt window, and you’ll see a list of all arguments and switches you can use with this tool:

   xsd /?

Step 4: Generate a class that matches an XSD schema. Finally, you can use the same XSD.EXE tool to create a class with the same structure as the XSD schema you just created.

   xsd nworder.xdr /classes /language:vb

Entering the preceding line at a command prompt causes the XSD utility to create a VB.NET class file called nworder.vb file, which contains the definition of a Schema1Order class that matches the schema of an order. Listing 2 shows the file contents.

Add the Generated Class to your Project
I copied the VB code generated by XSD and pasted it into the OrderClass module of the sample project. The OrderClass class is a middle tier component that serializes the values of an order returned by the GetOrderByID stored procedure into a business object and passes that to the presentation tier. I also renamed Schema1Order to Order and Schema1OrderDetail to OrderDetail. Schema1 is the default root name generated by XSD and it’s used as a prefix for all subordinate classes, but it doesn’t help the readability of the code.

Some of the generated properties don’t correspond directly to attributes of the XML schema. They are the properties with the “Specified” suffix and their type is Boolean (ProductIDSpecified, PriceSpecified, and so on). You should set the property values to an appropriate True/False value to indicate whether the corresponding property contains a Null (Nothing) value. The properties are generated only for the columns that are nullable. The best way is to alter the stored procedure to set a False value automatically for columns that contain Null values, so that the middle tier component can distinguish between Null and empty (or zero) values. However, the properties aren’t used in the sample application, so you can delete them from the class’s definition if you like.

So far you’ve created a query that returns a selected order in an XML document, and a class that represents an order whose structure matches the XML representation of an order. Now you need a mechanism to populate an instance of the Order class with the data of the XML document. This mechanism is the XmlSerializer class, which can deserialize an XML document into an instance of the appropriate class. You can set up a SqlCommand object (the CMD object shown in the following code segment) to execute the GetOrderByID stored procedure against the Northwind database and populate an instance of the Order class with the order’s fields:

   ' create a new Order instance   Dim selOrder As New OrderClass.Order      ' Execute the stored procedure   Dim XMLR As System.Xml.XmlReader = CMD.ExecuteXmlReader      ' create an XmlSerializer for the Order class type   Dim serializer As New XmlSerializer(GetType(OrderClass.Order))      ' deserialize the data into the Order instance   selOrder = Type(serializer.Deserialize (XMLR), Order)

You will most likely want to place this code segment in a middle tier method that accepts an orderID argument and returns an Order object. As you can see, after setting up the FOR XML query and the class, the code to populate the class with data obtained from SQL Server is about as simple as it can get. Because the selOrder object is strongly typed, it acts like any other .NET class in the IDE; you can enter the name of the object followed by a period to see a drop-down list with the object’s members and select any member to see its value. For example, the following statements display several members of the selOrder object:

   Console.WriteLine("ORDER # " & order.ID.ToString)   Console.WriteLine("Customer " & order.Customer)   Console.WriteLine("Items in Order " & _      order.Detail.Length.ToString)

By applying the procedure shown so far to get XML data into a generated business object, you can build an application that both retrieves orders from the Northwind database and displays them on a ListView control.

Building the SQLXML Demo Project
Figure 2 shows the user interface of the SQLXML demo project. The Show Order button retrieves the order with the ID specified in the box next to it and displays its fields on the appropriate controls on the form. The order’s header is displayed on a number of Label controls and its details are displayed on a Listview control. The Customer Orders button retrieves all the orders of a customer and displays them on the same control. The New Order button creates a new order, commits it to the database and then displays it, just like the Show Order button. I’ll discuss techniques for submitting business objects to SQL Server and inserting, or updating, the underlying tables in the second part of this article.

Figure 2: The SQLXML Project

The SQLXML project is a three-tier application. The data tier consists of the Northwind database and a few stored procedures (one of them is the GetOrderByID stored procedure you saw earlier in this article). The middle tier consists of the OrderClass.vb module, which contains the class generated by XSD plus a few new methods that you’ll create to perform basic database operations. The first two methods you’ll implement are the GetOrder method, which retrieves an order when you specify the Order ID, and the GetCustomerOrders method, which retrieves all the orders for a specified customer.

Retrieving Orders by ID
The GetOrder method accepts the ID of the desired order as argument and returns an Order object. The method code opens a database connection, executes the GetOrderByID stored procedure, and deserializes the returned XML data into an instance of the Order class, which is the method’s return value. If the query finds no order with the specified ID, the method returns Nothing. Listing 3 shows the code for the GetOrder method:

The method’s implementation is straightforward and relies on the definition of the OrderClass class, the GetOrderByID stored procedure and the XmlSerializer class. It doesn’t need to know anything about the structure of the database or the SQL statements that will actually retrieve the order; it simply executes the GetOrderByID stored procedure, passing an XmlReader object with the stored procedure’s stream to an XmlSerializer class, which knows how to deserialize the XML stream into an instance of the appropriate class.

The core of the code behind the Show Order button of the sample application is the statement that calls the GetOrder method and populates the Order business object:

   Dim order As OrderClass.Order   order = OrderClass.GetOrder(CInt(txtOrderID.Text))   If order Is Nothing Then       MsgBox("The order you specified does not exist!")       Exit Sub   End If   ShowOrder(order)

The ShowOrder() subroutine displays the order on the form. The subroutine’s code consists of statements that read the properties of the Order object and display them on the appropriate controls. As you can see, the presentation tier doesn’t contain any ADO.NET-specific code; therefore, developers working at this level need not be concerned with tables, relations, or even the structure of the database. When coding the presentation tier, developers work with a single strongly-typed object that represents the entire order. They don’t need to understand the relation between headers and detail lines, or write code to retrieve related rows from multiple tables. All the data describing the order is stored in an object than corresponds to a business entity.

The Customer Orders button retrieves all orders placed by the specified customer and displays them on a ListView control, as shown in Figure 3. The OrdersClass middle tier component uses the GetCustomerOrders stored procedure to retrieve the data and returns them to the presentation tier as a collection of Order objects. You can open the project in the IDE and examine the code. You can find the required stored procedures source in the README file included with the sample project.

Figure 3: Retrieving a customer’s orders

Efficiency Considerations
Is this technique any more efficient than using DataAdapters to retrieve the desired data from the database and passing that between layers as DataSets? No, it’s not. Yet, it’s not less efficient than a strictly DataAdapter-based approach. The XML serialization process introduces a delay, but only the first time you create an instance of the XmlSerializer class. The CLR caches this object for the application’s life time and reuses it. After that, the serialization overhead is negligible.

However, I think there’s a definite advantage of using business objects over plain DataSets?elegance. When you want to display data, something?either hard-coded logic or a business object?needs to “understand” the data content and format. When you pass DataSets to the client tier, you have to hard-code the logic in the application. In contrast, the Order class is a business object that represents a physical entity. By storing the order data in instances of the Order class rather than DataSets, you simplify the presentation tier code enormously, and remove the burden of understanding the data format from the shoulders of presentation tier developers.

So, except for that initial delay, there’s little difference in using DataSets or custom business object classes. I’ve written an application that retrieves orders and populates a DataSet with four DataTables (Orders, Order Details, Customers and Products). I timed both approaches with very large orders and no approach was clearly faster.

The disadvantage of the suggested approach is that you must craft carefully your queries to get back an XML document with the desired schema. After creating those stored procedures and the classes that will accept the data, implementing the presentation tier becomes trivial. A programmer who doesn’t even know how to set up a DataAdapter can program against the business objects and implement the interface.

Not Just for Read-Only Data
In the second part of this two-part article you’ll see how to pass an updated business object from the presentation tier to a SQL Server stored procedure. As you can guess, the article shows how you can serialize an Order object into XML and pass the resulting XML document to SQL Server. Here’s where efficiency truly comes into play. This reverse process is extremely efficient?far more so than updating individual details one at a time?because you can commit all the detail lines to the database with a single INSERT statement from within a stored procedure.