Steve Kirk
Microsoft Developer Network
September 1998
Summary: Explains the design of the Business Logic Layer component for the Duwamish Books sample application. (7 printed pages) Defines the Business Logic Layer and discusses the differences between the Business Logic and Workflow layers. Also covers:
In this Phase 3 of the sample, business logic code that was previously contained in client applications is moved to a Component Object Model (COM) component and to stored procedures in a Microsoft® SQL Server™ version 6.5 database. This organization allows simpler, more maintainable client applications and continues the migration toward a distributed solution that scales well by distributing work across multiple computers. Many of the transactional issues that I discuss here will be addressed further when we update Phase 3 implementing Microsoft Transaction Server (MTS).
From the perspective of the client, the main purpose of the Business Logic Layer (BLL) component is to simplify transactions. The BLL simplifies transactions by encapsulating the details of dependent transactions and of the underlying database structure. The client prepares a hierarchical object and then passes the object in a method call to the stateless BLL, which executes the transaction and reports success or failure. Hierarchical data is packaged in Microsoft ActiveX® Data Objects (ADO) 2.0 disconnected recordsets, which are efficient and language-neutral, making them well suited for passing data between components in a distributed application.
Performance requirements demand that the BLL execute transactions using the most scalable techniques. We expect a high volume of sale transactions and that the network latency will be fairly high. This design uses stored procedures for the significant run-time performance advantages over dynamic SQL and because more can be accomplished with each round trip to the database with specialized procedures than with generic transaction semantics.
See "The Duwamish Books Business Logic Layer API Reference"—I will refer to specific application programming interface (API) methods from the documentation within this discussion.
The terms business logic and business rules are often used interchangeably with middle tier in reference to code that occupies the functional region between the presentation layer and the data access layer. In the Duwamish Books Sample we use a more specific definition (see Figure1 in "An Introduction to the Duwamish Books Sample"). We refer to this region as the Business Rules and it is comprised of two layers: Business Logic and Workflow.
The BLL component, which resides closer to the server, is stateless in that it does not hold data between method calls. It encapsulates the details of the underlying data structure so that the client doesn't have to be aware of table structures, relational structures, or even the underlying column names. The BLL also encapsulates transactions and performs indivisible business transactions with single method calls. These hard transactions (for example, InsertSale) are called by the layer (Workflow) that prepares a complex business object (such as a sale) for the submission to the BLL transaction.
The Workflow layer (which resides closer to or, as in the case of Phase 3, on the client side) maintains context for the client application; that is, it holds data between calls to BLL methods. The name Workflow may at first seem inaccurate considering that it is commonly used where a complex business object (for example, a loan application) is routed to several people for approval according to a specified business policy. Use of the word Workflow would be less surprising if a sale were assembled and routed to a broker (via e-mail) for pricing before finally being submitted to the BLL InsertOrder method. If the sale object used in this application were routed in this manner, that routing function would fall squarely within the layer that I am now describing. (The Workflow layer will be broken out into a COM component in Phase 4.)
The various functions of the Duwamish Books sample require data that fits a range of profiles. Some data domains (such as the catalog of merchandise items) change infrequently and are best cached in denormalized form to simplify complex queries. Other domains (such as inventory) are transaction intensive and change frequently. Still others (such as orders) are hierarchical with members that are best expanded on demand as the user browses the headers and drills down into detail. The following use cases show how the client applications can use the BLL API to service these requirements.
Orders are numerous and the domain is dynamic. The hierarchical nature of a header and details makes progressive expansion desirable so that the cost of retrieving a list of orders does not have to include the cost of expanding the details of all orders. The BLL GetOrders function supports uses similar to the following:
The catalog of items changes infrequently and is best searched as a relatively flat set. When cached on the client as a denormalized and disconnected recordset, ADO 2.0 Filter, Find, and Sort methods can make both highly restrictive filters, which return few records, and open searches, which return many records, fast, efficient, and easy to develop. The BLL GetItems function with an ItemTemplateType of icCATALOG returns all items in the catalog in denormalized form with referenced columns such as Author, Publisher, and ItemType resolved from foreign key IDs to their actual values.
The following example shows how client code uses the BLL API to insert a sale:
' The header and detail objects are ADODB.Recordsets.
Dim oRsHeader As ADODB.Recordset
Dim oRsDetail As ADODB.Recordset
' GetSales with a non-existent PKID returns an empty hierarchial recordset for the sale.
oBll.GetSales oRsHeader, icDetails, PKId:=-1
' Add a new record for the sale header and populate header fields.
oRsHeader.AddNew
oRsHeader!CustomerId = 14
oRsHeader!EmployeeId = 5
oRsHeader!StoreId = 1
oRsHeader!SaleDate = Now()
…
' Sale details are contained in another recordset referenced by the header Details field.
Set oRsDetail = oRsHeader("Details").Value
' Add any number of detail records to the sale.
oRsDetail.AddNew
oRsDetail!ItemId = 541
oRsDetail!UnitPrice = 12.99
oRsDetail!Quantity = 1
…
' Submit the completed sale to BLL InsertSale to perform the transactino
oBll.InsertSale oRsHeader.An application using the BLL doesn’t care about the details of a transaction—it only needs to know whether the transaction was successful, and if the transaction failed, what went wrong. The BLL (based on the underlying technologies) guarantees that transactions will exhibit the ACID properties of atomicity, consistency, isolation, and durability. For more information, search on "Acid Properties" on the MSDN Library CD.
In addition to requiring that transactions exhibit the ACID properties, the application has performance requirements for the transactions. As I explained earlier, performance considerations caused me to use stored procedures for both efficient query execution and the reduction in network traffic that they can provide. When the SQL Server engine receives a dynamic SQL expression, it must parse the expression and prepare an execution plan before executing the query. Query preparation can require more work than the actual execution of the query, so it is usually far more efficient to execute a previously compiled stored procedure. (For more information, see the "Using Stored Procedures" section of the Platform SDK documentation on the MSDN Library CD.)
A simpler but somewhat lower performance alternative that retains the disconnected recordset characteristic of this design would employ the batch update capabilities available in ADO, which generate and pass SQL expressions (without the benefits of stored procedures) to the server. Other alternatives, such as transaction strategies based on server-side cursors, would require too many network trips and would load the server too severely to meet the performance requirements for this application.
The BLL executes transactions against the server through the Data Access Layer (DAL) component. In general, commands that return data are executed with DAL.GetRecordset, which returns a disconnected recordset. For more information on the DAL component and data access in the Duwamish Books sample, see "Duwamish Books Data Access Layer API, Phase 2" and "A Phase-to-Phase Guide to the Duwamish Books Data Access Strategy." For a more detailed discussion of data access issues, see "Designing a Data Access Layer."
The GetX methods (GetAuthors, GetItems, GetOrders, and so on) are the simplest transactions because they don’t modify data. The Get functions return a disconnected recordset through the DAL GetRecordset method. GetX method parameters include a container for returned data, a template type, a filter type, and an ADO recordset object that the method populates with data and returns as a disconnected recordset.
When the Receiving application wants a list of order numbers that are awaiting arrival of merchandise, it calls GetOrders with the following parameters:
GetOrders oRs, TemplateType:=icToReceive, _
ExpansionType:=icPK, StoreId:=MyStoreId
The BLL generates the following command expression:
Exec GetOrdersToReceive @ExpansionType=1, @TemplateType=5
The BLL then calls the GetRecordset method of the DAL object to populate the recordset:
ODAL.GetRecordset oRs, sCmd
The BLL then passes the recordset back to the client.
Data objects such as Orders or Sales are hierarchical in nature, with a header and some number of detail records. Queries that retrieve orders with details into a nonhierarchical rowset (using a join between header and detail tables) cause the header data to be returned redundantly with each detail row. ADO 2.0 hierarchical recordsets allow the field value of a recordset to refer to another recordset. This allows header data to be returned only once and can prevent a significant amount of redundant data from being transported over the network.
All hierarchical gets return a disconnected shaped recordset using DAL.GetRecordset.
The GetX series of functions prepare a shape expression that contains a stored procedure name and parameters for the header, a stored procedure name and parameters for the detail, and a join expression. The BLL.GetX function calls DAL.GetRecordset with the shape expression.
The ADO Shape Provider submits the two stored procedure expressions in a single batch and populates a shaped recordset with the results.
When the Receiving application wants to expand an order, it calls GetOrders as follows:
GetOrdersToReceive oRS, PKId:=MyOrderIdf, ExpansionType:=icDetails
The BLL generates the following expression for the ADO Shape Provider, which contains stored procedures with parameters for the parent and child recordsets:
SHAPE {Exec GetOrderHeaders @OrderTemplateType=3, @ExpansionType=4, @PKId=100} APPEND ({Exec GetOrderDetailsReceiveOrder @PKId=100} AS Details RELATE PKId TO OrderId)
The BLL then executes DAL.GetRecordset with the shape expression and returns the recordset to the client as before.
Transactional issues that apply to the Get functions affect performance but do not affect data consistency because they don't actually change data. The Insert and Update functions add data consistency issues to the mix and therefore add to the complexity.
Basic inserts and updates in the BLL do all of their work by executing a single Insert or Update stored procedure with the correct parameters. The stored procedures indicate success if they are able to do their work and raise an error if they are unable to perform the work. These transactions change only a single data entity and the implicit transaction is rolled back if an error occurs.
Hierarchical inserts and updates provide the greatest risk of data inconsistency because multiple data entities are changed within a single transaction. To make matters worse, these transactions can require multiple round trips to the database within the transaction. As I mentioned earlier, the next sample release will address MTS deployment issues and will continue this discussion. Transactional control in this version employs database transaction semantics.
Workflow layer code in the Point of Sale application prepares a hierarchical recordset representing a sale, as described earlier, and submits it to the BLL as follows:
BLL.InsertSale oRs
Within the BLL the transaction is handled in two command batches with each requiring a round trip to the database. Prior to execution of the first batch, a transaction is begun. The first batch inserts the sale header and also inserts an order header if the sale is to be shipped. These inserts are best done on the server in a single visit because the lookups required to relate the new sale and order entity can be accomplished efficiently at this point.
The BLL code takes data from the header recordset in oRS and prepares the following expression:
Exec InsertSaleHeader @EmployeeId=1, @StoreId=2, @Ship=1, @CustomerId=789, @ShipToName=’Sean Wellread’…
This expression is passed to the DAL GetRecordset method. GetRecordset is used because the command returns the newly inserted sale PKId in a recordset. The PKId of the sale header is used to relate the sale details to the header. The BLL code walks through the detail records in the sale recordset and generates the following expression for each detail:
Exec InsertSaleDetail @SaleId=N, @OrderId=6, @ItemId=56, @Quantity=2, @UnitPrice=12.98
The InsertSaleDetail expressions are packaged as a batch and are also passed to the DAL GetRecordset method, which is populated with the resulting sale by the last command in the batch.
If no errors are encountered, the transaction is committed. An error encountered during execution of the stored procedures will raise an error through ADO back to the BLL, which will roll back the transaction and raise an error to the client application.