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
, and so on). You should set the property values to an appropriate True/False
value to indicate whether the corresponding property contains a Null
) 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
Console.WriteLine("ORDER # " & order.ID.ToString)
Console.WriteLine("Customer " & order.Customer)
Console.WriteLine("Items in Order " & _
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.