Store and Retrieve XML-based Business Objects with SQL Server and .NET : Page 4

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.

Note: In my opinion, the ListView control is usually a better choice for displaying data than the DataGrid control. The DataGrid control is more of a developer's tool and it's not the ultimate control for building data driven Windows applications.

Previous Page

Next Page

---

• Download The Code for this Article

---