Browse DevX
Sign up for e-mail newsletters from DevX


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

Combine SQL Server's XML support and .NET XML serialization to simplify your front-end code by moving custom objects rather than DataSets between application layers and into and out of SQL Server.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Note: In a future article I'll show you how to add a method that accepts an Order object from the presentation tier, serializes it into XML and passes it to a stored procedure that commits the order to the database.

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.

Evangelos Petroutsos is a long time VB developer. When he's not writing code, he writes programming books (most published by Sybex), and articles. Reach him by e-mail at pevangelos@yahoo.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date