The Data Layer

The Transact database has two types of tables: those that store product and pricing information (as shown in Figure 17-3) and those that store order and shipment information (as shown in Figure 17-4).

Figure 17-3. Product tables

Figure 17-4. Order tables

A standard order consists of one or more computers, each of which is entered separately in the OrderStations table. Each computer is composed of several parts (a CPU, a hard drive, a motherboard, and so on), each of which is represented by a separate record in the OrderItems table. A record is added to the Shipments table after the internal order-filling client has processed an order.

The Products table lists all the available products and their prices. (Other information, such as a description, isn't included but can easily be added.) The Choices table determines how the products are grouped. Essentially, products that reference the same choice record are grouped as mutually exclusive options. When choosing the parts for a computer, the client selects exactly one item out of each choice grouping.

Figure 17-5 shows the sample data preloaded into the Products and Choices tables. There are 4 choice groups defined, and a total of 12 products.

Figure 17-5. Sample product data

Both the Products and Choices tables contain a true/false Obsolete column. When this value is set to True (1), the stored procedure code automatically ignores these rows when returning pricing information. This allows products to be removed from the catalog without deleting the records, ensuring that old order records are still interpretable. More complex logic that links choices to previous selections (for example, logic that allows SCSI hard drives only if the order includes a compatible motherboard or SCSI controller) is typically implemented in the client application because it is extremely difficult to model in the database.

Stored Procedures

The Transact database uses eight stored procedures, as described in Table 17-1. The first three are used to commit a new order (AddOrder, AddOrderItem, and AddStation). Four more (SetOrderInProgress, SetOrderAvailable, CompleteOrder, and GetAvailableOrders) are used by the internal order-filling client when processing orders. The final stored procedure, GetPriceInfo, is used to retrieve the current product catalog.

Table 17-1. Transact Stored Procedures

Stored Procedure	Description
AddOrder	Adds a new order record and returns the unique ID.
AddOrderItem	Adds a new order item for a new station and returns the unique ID.
AddStation	Adds a new computer as part of a new order and returns the unique ID.
SetOrderInProgress	Changes the status of an order from available (Status 0) to in-progress (Status 1) and returns all the associated information (the order, station, and order item records).
SetOrderAvailable	Changes the status of an order from in progress to available. This is used when the order-filling client returns an unfilled order to the pool.
CompleteOrder	Changes the status of an order to shipped (Status 2), generates a new shipment record, and returns the shipment ID for confirmation purposes.
GetAvailableOrders	Returns all the orders that are available (Status 0). However, this stored procedure returns only the ID column. This is because there could be hundreds of orders but the order-filling client only needs the information for orders that are being filled.
GetPriceInfo	Returns all the information (except obsolete records) from the Choices and Products tables. This then becomes the product catalog that the remote client downloads from the XML Web service.

Listing 17-1 shows the stored procedure code used for GetPriceInfo. Note that this stored procedure returns two separate result sets. This information could be combined into a single table using a join query, but it's easier to manipulate if it's kept as separate tables.

Listing 17-1 Returning the product catalog

 CREATE PROCEDURE GetPriceInfo  AS     SELECT * FROM Choices     WHERE NOT (ISNULL(Obsolete,0)=1) ORDER BY OrderPriority     SELECT * FROM Products      WHERE NOT (ISNULL(Obsolete,0)=1) 

Listing 17-2 shows the stored procedures used to commit a new order to the database. They all use fairly straightforward insert commands and return the unique identity value. The date for a new order is inserted automatically using SQL Server's GetDate function.

Listing 17-2 Stored procedures for adding a new order

 CREATE Procedure AddOrder (     @CustomerID int,     @ID         int OUTPUT ) AS INSERT INTO Orders (     CustomerID,     OrderDate,     Status ) VALUES (     @CustomerID,     GETDATE(),     0 ) SELECT     @ID = @@Identity CREATE Procedure AddStation (     @OrderID int,     @ID      int OUTPUT ) AS INSERT INTO OrderStations (     OrderID ) VALUES (     @OrderID ) 

 SELECT     @ID = @@Identity CREATE Procedure AddOrderItem (     @StationID      int,     @ProductID      int,     @PriceOrderedAt money,     @ID             int OUTPUT ) AS INSERT INTO OrderItems (     StationID,     ProductID,     PriceOrderedAt ) VALUES (     @StationID,     @ProductID,     @PriceOrderedAt ) SELECT     @ID = @@Identity 

The order-filling client uses all the other stored procedures. First of all, the client needs a way to determine what orders are in the queue to be shipped (as shown in Listing 17-3). Orders are automatically sorted by date, ensuring a first-in-first-out (FIFO) process.

Listing 17-3 Finding available orders

 CREATE PROCEDURE GetAvailableOrders  AS     SELECT ID FROM Orders     WHERE Status = 0     ORDER BY OrderDate 

Listing 17-4 shows the stored procedures used for setting the status of an order to in-progress or available. Conceptually, the SetOrderInProgress stored procedure retrieves an order from the pool of available orders so that another order-filling client won't try to ship it at the same time. The SetOrderAvailable stored procedure is used only if a previously selected order can't be completed and needs to be returned to the pool.

Listing 17-4 Changing the order status

 CREATE Procedure SetOrderInProgress  (     @ID int  )  AS UPDATE Orders     SET Status = 1     WHERE ID = @ID SELECT * FROM Orders WHERE ID = @ID SELECT * FROM OrderStations WHERE OrderID = @ID SELECT * FROM OrderItems     INNER JOIN Products ON Products.ID = OrderItems.ProductID     INNER JOIN OrderStations ON OrderStations.ID = OrderItems.StationID     WHERE OrderID = @ID CREATE Procedure SetOrderAvailable  (     @ID int  )  AS UPDATE Orders     SET Status = 0     WHERE ID = @ID 

One interesting fact about the SetOrderInProgress stored procedure is that it ends by returning three result sets: the order record, the related stations, and the order items for all the stations. Therefore, when the order-filling client selects an order, it automatically receives the required information.

Finally, after an order has been filled, the order-filling client needs to update the database accordingly. The CompleteOrder stored procedure updates the order status and inserts a new shipment record with the current date (as shown in Listing 17-5).

Listing 17-5 Completing the order

 CREATE Procedure CompleteOrder  (     @OrderID int,     @ShipID  int OUTPUT  )  AS UPDATE Orders     SET Status = 2     WHERE ID = @OrderID INSERT INTO Shipments (     OrderID,     ShippedDate ) VALUES (     @OrderID,     GETDATE() ) SELECT     @ShipID = @@Identity 

The Data Component

The database component wraps all these stored procedures using two classes, ProductDB and OrderDB. There are also three entity classes used to model the parts of an order (as shown in Listing 17-6). We won't create entire classes for the other tables because the application doesn't need to work with this information directly.

Listing 17-6 Data entities

 ' Note that these structures do not define the full records, ' only the pieces that are relevant in this solution. Public Class Order     Public OrderID As Integer     Public CustomerID As Integer     Public Stations As Station()     Public OrderDate As Date     ' This sets the automatic list box display for the     ' OrderFillClient.     Public Overrides Function ToString() As String         Return OrderID & ": " & Stations.GetLength(0).ToString() & _                " station(s)"     End Function End Class Public Class Station     Public OrderItems As OrderItem()     Public OrderID As Integer End Class Public Class OrderItem     Public StationID As Integer     Public PriceOrderAt As Decimal     Public ProductID As Integer     ' This is not truly a part of the OrderItems table,     ' but is included here for convenience when retrieving     ' order information. It displays the product name that     ' corresponds to the order's ProductID.     Public ProductName As String End Class 

The ProductDB Class

The ProductDB class provides a single method, GetPricingDataSet, that retrieves the Choices and Products information and returns this information in a DataSet. When the SqlDataAdapter.Fill method is executed, two tables are created in the DataSet, with the default names Table and Table1. The code in Listing 17-7 uses table mapping to rename these tables to the more descriptive Choices and Products.

Listing 17-7 The ProductDB service provider

 Public Class ProductDB     Private ConnectionString As String     Friend Sub New(ByVal connectionString As String)         Me.ConnectionString = connectionString     End Sub     Public Function GetPricingDataSet() As DataSet         Dim con As New SqlConnection(ConnectionString)         Dim cmd As New SqlCommand("GetPriceInfo", con)         cmd.CommandType = CommandType.StoredProcedure         Dim Adapter As New SqlDataAdapter(cmd)         ' Apply table mappings.         ' This ensures that the tables are renamed with          ' more descriptive titles.         Adapter.TableMappings.Add("Table", "Choices")         Adapter.TableMappings.Add("Table1", "Products")         Dim ds As New DataSet("Pricing")         Try             con.Open()             ' Because this stored procedure returns two result sets,             ' two tables will be inserted in the DataSet.             Adapter.Fill(ds)         Finally             con.Close()         End Try         ' Add a data relation to link the two tables. 

         ' This allows the client to use an easier navigation model.         Dim parent As DataColumn = ds.Tables(0).Columns("ID")         Dim child As DataColumn = ds.Tables(1).Columns("ChoiceID")         Dim relation As New DataRelation("Choices_Products", _                                          parent, child)         ds.Relations.Add(relation)         Return ds     End Function End Class 

For convenience, the GetPricingDataSet method adds a DataRelation to link the two tables before it returns them.

The OrderDB Class

The OrderDB class is by far the most detailed part of the data component. Its basic structure and public methods are shown here:

 Public Class OrderDB     Private ConnectionString As String     Friend Sub New(ByVal connectionString As String)         Me.ConnectionString = connectionString     End Sub     Public Function CommitNewOrder(ByVal order As Order) As Integer         ' (Code omitted.)     End Function     Public Function GetAvailableOrderIDs() As Integer()         ' (Code omitted.)     End Function     ' Returns the structure that represents the full order,      ' with all contained items.     Public Function SetOrderInProgress(ByVal ID As Integer) As Order         ' (Code omitted.)     End Function     Public Sub SetOrderAvailable(ByVal ID As Integer)         ' (Code omitted.)     End Sub     ' Returns the unique ID of the shipment record for confirmation.     Public Function CompleteOrder(ByVal ID As Integer) As Integer         ' (Code omitted.)     End Function End Class 

The CommitNewOrder method is particularly interesting; it makes use of three private methods to insert the separate order, station, and order items records (as shown in Listing 17-8). This logic is divided so that it is more maintainable. It also gives you the option to add other methods to the component that insert records in only one of the three tables.

It's important to note that these three methods (InsertOrder, InsertStation, and InsertOrderItem) are all private. They can't be made public because they all require an open connection. The public CommitNewOrder method opens this connection, performs all the database work by calling these methods multiple times, and then closes the connection. The only other option opening and closing the connection with each database operation would suffer much worse performance.

Listing 17-8 Inserting a new order

 Public Function CommitNewOrder(ByVal order As Order) As Integer     Dim Item As OrderItem     Dim Station As Station     ' Execute all steps in a single ADO.NET transaction.     Dim tran As SqlTransaction     Dim con As New SqlConnection(ConnectionString)     Dim OrderID As Integer     Try         con.Open()         tran = con.BeginTransaction         OrderID = InsertOrder(order, con, tran)         For Each Station In order.Stations             Station.OrderID = OrderID             Dim StationID As Integer             StationID = InsertStation(Station, con, tran)             For Each Item In Station.OrderItems                 Item.StationID = StationID                 InsertOrderItem(Item, con, tran) 

             Next         Next         tran.Commit()     Catch Err As Exception         tran.Rollback()         ' Rethrow the exception.         Throw Err     Finally         con.Close()     End Try     Return OrderID End Function Private Function InsertOrder(ByVal order As Order, _   ByVal con As SqlConnection, ByVal tran As SqlTransaction) As Integer     Dim cmd As New SqlCommand("AddOrder", con)     ' If this parameter is a null reference (Nothing)     ' no transaction will be used.     cmd.Transaction = tran     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int)     Param.Value = order.CustomerID     ' Add the output parameter.     Param = cmd.Parameters.Add("@ID", SqlDbType.Int)     Param.Direction = ParameterDirection.Output     cmd.ExecuteNonQuery()     Return Param.Value End Function Private Function InsertStation(ByVal station As Station, _   ByVal con As SqlConnection, ByVal tran As SqlTransaction) As Integer     Dim cmd As New SqlCommand("AddStation", con)     cmd.Transaction = tran     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@OrderID", SqlDbType.Int)     Param.Value = station.OrderID     ' Add the output parameter.     Param = cmd.Parameters.Add("@ID", SqlDbType.Int)     Param.Direction = ParameterDirection.Output     cmd.ExecuteNonQuery()     Return Param.Value End Function Private Function InsertOrderItem(ByVal orderItem As OrderItem, _   ByVal con As SqlConnection, ByVal tran As SqlTransaction) As Integer     Dim cmd As New SqlCommand("AddOrderItem", con)     cmd.Transaction = tran     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@StationID", SqlDbType.Int)     Param.Value = orderItem.StationID     Param = cmd.Parameters.Add("@ProductID", SqlDbType.Int)     Param.Value = orderItem.ProductID     Param = cmd.Parameters.Add("@PriceOrderedAt", SqlDbType.Money)     Param.Value = orderItem.PriceOrderAt     ' Add the output parameter.     Param = cmd.Parameters.Add("@ID", SqlDbType.Int)     Param.Direction = ParameterDirection.Output     cmd.ExecuteNonQuery()     Return Param.Value End Function 

Notice also that all these database operations run under a single ADO.NET transaction. This ensures that an order can't be partially committed if it contains invalid items, no part of the order is added to the database.

Note

The private methods can be run in the context of an ADO.NET transaction or independent of one. If the caller passes in a null reference (Nothing) for the transaction object, the command executes normally.

Listing 17-9 shows the GetAvailableOrderIDs method, which just wraps the GetAvailableOrder stored procedure and returns an array of integers. For the sake of easier coding, the method iterates through the retrieved results, adds them to an ArrayList, and then converts them to a strongly typed integer array on the last line. (Another approach is to use an ordinary array and to use the ReDim command to expand it with each pass while reading the results.)

Listing 17-9 Retrieving available orders

 Public Function GetAvailableOrderIDs() As Integer()     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("GetAvailableOrders", con)     Dim r As SqlDataReader     Dim IDs As New ArrayList()     cmd.CommandType = CommandType.StoredProcedure              Try         con.Open()         r = cmd.ExecuteReader()         Do While r.Read             IDs.Add(CType(r("ID"), Integer))         Loop     Finally         con.Close()     End Try     Return IDs.ToArray(GetType(Integer)) End Function 

Listing 17-10 shows the methods used to mark an order in-progress (SetOrderInProgress) and return it back to the pool (SetOrderAvailable). The SetOrderInProgress method is more complicated because it needs to step through the retrieved results (three tables in total) and build up the corresponding Order, Station, and OrderItem objects.

Listing 17-10 Retrieving and releasing an order

 Public Function SetOrderInProgress(ByVal ID As Integer) As Order     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("SetOrderInProgress", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@ID", SqlDbType.Int)     Param.Value = ID     Dim Adapter As New SqlDataAdapter(cmd)     Dim ds As New DataSet()     Try         con.Open()         Adapter.Fill(ds)     Finally         con.Close()     End Try     ' Add order information from first table.     Dim row As DataRow = ds.Tables(0).Rows(0)     Dim Order As New Order()     Order.OrderID = row("ID")     Order.CustomerID = row("CustomerID")     Order.OrderDate = row("OrderDate")     Dim OrderStations, OrderItems As New ArrayList()     ' Add station information from second table.     For Each row In ds.Tables(1).Rows         Dim Station As New Station()         OrderStations.Add(Station) 

         ' Add order item information from third table.         Dim rowChild As DataRow         For Each rowChild In ds.Tables(2).Select("StationID='" & _           row("ID") & "'")             Dim Item As New OrderItem()             Item.ProductName = rowChild("Name")             Item.PriceOrderAt = rowChild("PriceOrderedAt")             OrderItems.Add(Item)         Next         Station.OrderItems = OrderItems.ToArray(GetType(OrderItem))     Next     Order.Stations = OrderStations.ToArray(GetType(Station))     Return Order End Function Public Sub SetOrderAvailable(ByVal ID As Integer)     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("SetOrderAvailable", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@ID", SqlDbType.Int)     Param.Value = ID     Try         con.Open()         cmd.ExecuteNonQuery()     Finally         con.Close()     End Try End Sub 

Finally, the CompleteOrder method wraps the CompleteOrder stored procedure and returns the shipping confirmation number (as shown in Listing 17-11).

Listing 17-11 Completing an order

 Public Function CompleteOrder(ByVal ID As Integer) As Integer     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("CompleteOrder", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@OrderID", SqlDbType.Int)     Param.Value = ID     ' Add the output parameter.     Param = cmd.Parameters.Add("@ShipID", SqlDbType.Int)     Param.Direction = ParameterDirection.Output     Try         con.Open()         cmd.ExecuteNonQuery()     Finally         con.Close()     End Try     Return Param.Value End Function 

The TransactTables Class

The TransactTables class provides a gateway to the other database classes (as shown in Listing 17-12), just as in the preceding case study. It accepts a connection string argument and passes it to a new instance of the ProductDB and OrderDB classes.

Listing 17-12 Composition with TransactTables

 Public Class TransactTables     Public Product As ProductDB     Public Order As OrderDB     Public Sub New()         ' Retrieve the connection string.         Dim Connection As String = ConfigurationSettings.AppSettings( _           "TransactConnection") 

         ' Create the table classes.         Product = New ProductDB(Connection)         Order = New OrderDB(Connection)     End Sub End Class