Browse DevX
Sign up for e-mail newsletters from DevX


Store and Retrieve XML-based Business Objects with SQL Server and .NET (Part II) : Page 3

Improve performance and gain convenience by using custom business objects serialized to and from XML rather than forcing your presentation-tier developers to work directly with DataSets.




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

The AddOrder Method
The AddOrder method comprises the middle tier method that commits the order to the database (see Listing 2). The code is straightforward: first it serializes the newOrder object passed to the method as argument into a string variable, the XMLOrder variable, and then calls the SaveXMLOrder stored procedure passing this string as argument.

Public Shared Function AddOrder( _ ByVal newOrder As Order) As Integer Dim serializer As XmlSerializer serializer = New XmlSerializer(GetType(Order)) ' Serialize the order's details on a memory stream, ' then read the stream into a string variable and ' pass the variable as argument to the SaveOrder ' stored procedure Dim MS As MemoryStream Try MS = New MemoryStream serializer.Serialize(MS, newOrder) Catch exc As Exception MS.Close() Return (-1) End Try MS.Close() Dim XMLOrder As String = _ System.Text.Encoding.UTF7.GetString(MS.ToArray) Dim CMD As New SqlClient.SqlCommand CMD.CommandText = "SaveXMLOrder" CMD.CommandType = CommandType.StoredProcedure CMD.Parameters.Add( _ New SqlClient.SqlParameter("@order", XMLOrder)) Dim CN As New SqlClient.SqlConnection CN.ConnectionString = "your connection string" ' for example: "data source=(local);initial " & _ ' "catalog=Northwind;uid=sa;pwd=" CMD.Connection = CN CN.Open() Dim newOrderID As Integer Try newOrderID = CInt(CMD.ExecuteScalar) Catch ex As Exception Return -1 Finally CN.Close() End Try Return newOrderID End Function

The AddOrder method is almost trivial; it serializes the newOrder object and passes the resulting XML document as argument to the SaveXMLOrder stored procedure, which is responsible for parsing the XML document and committing the entire order to the database in transactional mode. The code at the presentation tier and the middle tier need not be concerned with transactions.

The sample application's New Order button creates a random new order and submits it to the database by calling the AddOrder method of the OrderClass class. The method returns the ID of the new order and the code at the presentation tier retrieves the new order from the database with the GetOrder method and displays its details on the ListView control. It actually calls the Show Order button's code to retrieve the new order. Here's the code that creates a random order and submits it to the database with the AddOrder method:

Dim newOrder As New OrderClass.Orders newOrder.OrderDate = Date.Today newOrder.CustomerID = custID newOrder.Freight = 10 + CDec((rnd.NextDouble * 15000) / 100) Dim lines As Integer = Rnd.Next(2, 20) Dim details(lines - 1) As OrderClass.OrdersProducts Dim dtl As Integer For dtl = 0 To lines - 1 details(dtl) = New OrderClass.OrdersProducts details(dtl).ProductID = rnd.Next(1, 72) details(dtl).Price = OrderClass.GetItemPrice _ (details(dtl).ProductID) details(dtl).Quantity = rnd.Next(3, 50) details(dtl).Discount = _ CDec(rnd.Next(0, 30) / 100) Next newOrder.Products = details Dim ID As Integer = OrderClass.AddOrder(newOrder)

The code also keeps track of each order's running total and updates the order's header after displaying all the detail lines. I won't explain the code in detail, as you will most likely use a different interface to display the orders. However, you won't have to work with ADO.NET objects at the presentation tier. GetItemPrice is another method of the middle tier component. It returns the price of the product specified by its ID as argument.

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