The Data Layer

The first step in creating this system is to build the database. In our case, we'll use a fairly simple set of tables so we can focus on the overall architecture rather than mundane details such as setting multiple fields and adding stored procedure parameters. In a typical business system, the logic would closely match our example, but the database tables would be expanded. For example, the Products table would probably require additional fields to track detailed product information. The database might also include additional tables such as ProductCategories.

Figure 16-3 shows the business-specific tables for our stripped-down data layer. Essentially, there are two order-specific tables: Orders, which includes a record for each Excel file that is processed, and the child table OrderItems, which contains the list of ordered items for every order. There are also a Products table, which lists the services and items the Graphic Design Institute sells; a SalesAgent table, which includes a record for every sales associate; and a Customers table, which tracks the companies that buy from the Graphic Design Institute.

Figure 16-3. The business tables

Note

As with just about any financial or commerce-based database, the price of a product has to be recorded in the actual order record. This is common sense because the price might fluctuate after the order is committed and the sale price is important for billing and historical data.

The database includes an additional table that's intended for internal use by the OrderProcessor component. This table, called ExcelMappings, is a critical part of the puzzle because it allows the program code to be abstracted away from the structure of the Excel spreadsheet (as shown in Figure 16-4). This enables the Graphic Design Institute to create new spreadsheets without needing to create new versions of the server-side code for order processing. The details behind this approach are explained a little later.

Figure 16-4. The internal ExcelMappings table

Stored Procedures

To perfect this database, you would probably want to add a full complement of views, indexes, and stored procedures. For our purposes, however, we'll focus on adding the functionality required by the order-processing server component. There are six stored procedures (described in Table 16-1).

Table 16-1. Invoicer Stored Procedures

Stored Procedure	Description
CustomerAdd	Inserts a new record into the Customers table.
OrderAdd	Inserts a new record into the Orders table.
OrderItemAdd	Inserts a new record into the OrderItems table. There is one record for every product purchased as part of an order.
GetCustomerIDByName	Retrieves the ID column for the customer record that matches the supplied name. This allows the code to determine whether a customer exists in the database or whether one must be added.
GetProductIDByCode	Retrieves the ID column for the product record that matches the supplied product code. This is important because products are linked to orders by their unique ID numbers but the Excel spreadsheet lists only the product code.
GetExcelMappings	Retrieves a row from the ExcelMappings table with the specified version code. This row describes all the information the code needs to find values on the spreadsheet.

Listing 16-1 presents the combined code for the first three stored procedures (CustomerAdd, OrderAdd, and OrderItemAdd), which are dedicated to adding database records.

Listing 16-1 Stored procedures for inserting data

 CREATE Procedure CustomerAdd  (     @Name   nvarchar(50),     @ID     int OUTPUT  )  AS  INSERT INTO Customers  (     Name  )  VALUES  (     @Name  )  SELECT     @ID = @@Identity CREATE Procedure OrderAdd  (     @SalesAgentID int,     @CustomerID   int,     @DateEntered  datetime,     @Status       int,     @ID           int OUTPUT  )  AS  INSERT INTO Orders  (     SalesAgentID,     CustomerID,     DateEntered,     Status,     DateCommitted  )  VALUES  (     @SalesAgentID,     @CustomerID,     @DateEntered,     @Status, 

     GETDATE()  )  SELECT     @ID = @@Identity CREATE Procedure OrderItemAdd  (     @OrderID        int,     @ProductID      int,     @PriceOrderedAt money,     @Quantity       int,     @ID             int OUTPUT  )  AS  INSERT INTO OrderItems  (     OrderID,     ProductID,     PriceOrderedAt,     Quantity  )  VALUES  (     @OrderID,     @ProductID,     @PriceOrderedAt,     @Quantity  )  SELECT     @ID = @@Identity 

Optionally, you could create a more complex stored procedure that adds an order record and all subitems in one step. This would reduce the number of round-trips and would improve performance, but it would require some more intricate SQL, it would be more difficult to maintain, and it would offer reduced opportunity for code reuse.

Listing 16-2 shows the stored procedure code used for the GetCustomerIDByName and GetProductIDByCode stored procedures, which accept a unique value and return the corresponding identifier.

Listing 16-2 Stored procedures for retrieving data

 CREATE PROCEDURE GetCustomerIDByName  (     @Name varchar(50)  )  AS     SELECT [ID] FROM Customers     WHERE [Name] = @Name CREATE PROCEDURE GetProductIDByCode  (     @Code varchar(15)  )  AS     SELECT [ID] FROM Products     WHERE ProductCode = @Code 

Finally, another stored procedure is required to return the list of Excel mappings for a specific version code. This stored procedure is quite simple:

 CREATE PROCEDURE GetExcelMappings  (     @VersionCode varchar(15)  )  AS     SELECT * FROM ExcelMappings     WHERE VersionCode = @VersionCode 

The Excel Spreadsheet

The Excel file is the least flexible part of the system. Because it is already in widespread use and is distributed to dozens of sales associates, it cannot be easily changed. However, one change is required: the addition of a dedicated version code field that distinguishes the different spreadsheet versions. This version code is set in cell F2, which is the only hard-coded cell value. This cell was chosen based on the current Graphic Design Institute spreadsheet; all future spreadsheets must adhere to this convention. Most of the other details will be customizable.

Fortunately, the Graphic Design Institute already uses a system in which orders are given unique filenames that combine the date, customer, and sales agent code (for instance, 2003-03-03_FRX_JM.xls). This ensures that all file­names are unique. At the end of this chapter, we'll consider some techniques you could use if unique filenames weren't guaranteed.

Figure 16-5 shows a sample Excel spreadsheet. For the sake of simplicity, we are assuming that only a single workbook sheet is used in the file. In most scenarios, the Excel file will be far more complicated and might include a significant amount of information that isn't relevant to the order (such as a recent price list). In fact, many companies develop multipage spreadsheets that compute the final order values to read-only fields on a separate sheet using Excel macros. These details don't change the validity of our approach, however, which is to abstract the structure of the Excel file as much as possible.

Figure 16-5. The order spreadsheet

To create the mapping record for this version (2003-001), you need to specify the row where order items start (5); the columns for price (B), product (A), and quantity (C) information; and the cell where the sales associate (C16) and customer (B2) are recorded. Figure 16-6 shows the complete ExcelMappings row.

Figure 16-6. The Excel mapping

The Data Component

The first step in creating the data component is to define the record structures, as shown in Listing 16-3. Note that the code doesn't currently include definitions for all the tables, just those that the application will manipulate.

Listing 16-3 Data entities

 Public Class Customer     Public ID As Integer     Public Name As String End Class ' This enumeration contains custom status codes that are used to ' fill the Status field of the Order table. Public Enum StatusCode     Open = 501     Waiting = 502     Fulfilled = 503     Problem = 504 End Enum ' CustomerName is included as a member of this structure, even though ' it is technically part of the linked customer record. The reason is  ' that the Excel file only specifies customer names, not the unique ID  ' numbers. Public Class Order     Public ID As Integer     Public OrderItems As New ArrayList()     Public SalesAgentID As Integer     Public CustomerID As Integer     Public CustomerName As String     Public DateEntered As Date     Public Status As StatusCode End Class ' OrderItem instances can be added to the OrderItems property of the ' Order class. A more defensive approach would be to use a custom class ' to restrict invalid objects from being assigned to the OrderItems ' property, as demonstrated in Chapter 3. Public Class OrderItem     Public ID As Integer     Public OrderID As Integer     Public ProductID As Integer     Public ProductCode As String     Public PriceOrderedAt As Decimal     Public Quantity As Integer End Class 

Public member variables are used rather than full property procedures because these structures will be used only in the server-side code, and there aren't any rules that you need to enforce there. By the time the data is transferred to the server, it's too late to identify input problems and correct them. Instead, the client needs to assume this responsibility. In addition, any server methods that receive data from the client need to be equipped with the appropriate validation logic.

Note

As always, it is valid for an information package to exist even if not all the fields are set to valid values. For example, you might receive an OrderItem instance from a method that sets only some of the properties, or you might have an Order class that doesn't provide its linked OrderItem instances. Similarly, a newly created Order, OrderItem, or Customer object won't yet have its unique ID property set.

As with all well-designed data components, functionality is restricted to using stored procedures that exist in the database. This makes it easier to introduce changes and optimizations later on. Dynamic SQL statements, which are every developer's favorite trick, are notoriously difficult to optimize and more fragile because the database administrator will have no way of knowing the dependencies of your code.

Note

To really round out this solution, you would probably want to create dedicated administrative tools to automate every database interaction. With our implementation, a SQL Server savvy user would be required to commit the details for new Excel sheet versions and new products into the database using a tool such as SQL Server Enterprise Manager.

The CustomersDB Class

Listing 16-4 shows the service provider code for the Customers table. Note that even though each table provides only one or two functions, the code has been organized into two table-specific classes. This isn't necessary, but it provides a solid framework for adding database functionality in the future.

Listing 16-4 The CustomersDB service provider

 Public Class CustomersDB     Private ConnectionString As String     Friend Sub New(Connection As String)         Me.ConnectionString = Connection     End Sub     ' AddCustomer uses the CustomerAdd stored procedure.     Public Function AddCustomer(customer As Customer) As Integer         Dim con As New SqlConnection(ConnectionString)         Dim cmd As New SqlCommand("CustomerAdd", con)         cmd.CommandType = CommandType.StoredProcedure         Dim Param As SqlParameter         Param = cmd.Parameters.Add( _          "@Name", SqlDbType.NVarChar, 50)         Param.Value = customer.Name         ' Add the output parameter.         Param = cmd.Parameters.Add("@ID", SqlDbType.Int)         Param.Direction = ParameterDirection.Output         Try             con.Open()             cmd.ExecuteNonQuery()         Catch Err As Exception             ' Use "caller inform" exception handling pattern.             Throw New ApplicationException( _              "Exception encountered when executing command.", Err)         Finally             con.Close()         End Try         ' Return the unique ID output parameter.         Return Param.Value     End Function     ' CheckIfCustomerExists uses the GetCustomerIDByName stored     ' procedure. 

     Public Function CheckIfCustomerExists(Name As String) As Integer         Dim con As New SqlConnection(ConnectionString)         Dim cmd As New SqlCommand("GetCustomerIDByName", con)         Dim r As SqlDataReader         cmd.CommandType = CommandType.StoredProcedure         Dim Param As SqlParameter         Param = cmd.Parameters.Add( _          "@Name", SqlDbType.NVarChar, 50)         Param.Value = Name         Dim ID As Integer = 0         Try             con.Open()             r  = cmd.ExecuteReader(CommandBehavior.SingleRow)             If r.Read() = True                 ' A customer exists with this name.                 ID = r("ID")             End If         Catch Err As Exception             ' Use "caller inform" exception handling pattern.             Throw New ApplicationException( _              "Exception encountered when executing command.", Err)         Finally             If Not (r Is Nothing) Then r.Close()             con.Close()         End Try         Return ID     End Function End Class 

The OrdersDB Class

The OrdersDB class is a little more complex. It provides a public AddOrder function and a private AddItem function. Here's the basic structure:

 Public Class OrdersDB     Private ConnectionString As String     Friend Sub New(Connection As String)         Me.ConnectionString = Connection     End Sub     ' Encapsulates the logic needed to add an order and its sub-items.     Public Function AddOrder(ByVal order As Order) As Integer         ' (Code omitted.)     End Function     Private Function AddItem(ByVal item As OrderItem, _      ByVal con As SqlConnection)         ' (Code omitted.)     End Function End Class 

AddItem is private so that it can be called only as part of an AddOrder operation. This allows the OrdersDB class to implement a more optimized design. Essentially, it works like this: the AddOrder method opens the connection to the database, calls AddItem as required, and then closes the connection. AddItem receives a reference to the database connection, and assumes that it is open. It uses this connection to execute its commands directly. This way, you can add an order that includes multiple products without incurring the additional overhead of repeatedly opening and closing new connections.

Listing 16-5 shows the logic for the AddOrder method:

Listing 16-5 The OrdersDB.AddOrder method

 Public Function AddOrder(ByVal order As Order) As Integer     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("OrderAdd", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@SalesAgentID", SqlDbType.Int)     Param.Value = order.SalesAgentID 

     Param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int)     Param.Value = order.CustomerID     Param = cmd.Parameters.Add("@DateEntered", SqlDbType.DateTime)     Param.Value = order.DateEntered     Param = cmd.Parameters.Add("@Status", SqlDbType.Int)     Param.Value = order.Status     ' Add the output parameter.     Param = cmd.Parameters.Add("@ID", SqlDbType.Int)     Param.Direction = ParameterDirection.Output     Try         con.Open()         cmd.ExecuteNonQuery()         ' Now add the order items.         Dim Item As OrderItem         For Each Item In order.OrderItems             ' Set the ID of the parent order record             ' that was just inserted.             Item.OrderID = Param.Value             AddItem(Item, con)         Next     Catch Err As Exception         ' Use "caller inform" exception handling pattern.         Throw New ApplicationException( _          "Exception encountered when executing command.", Err)     Finally         con.Close()     End Try     ' Return the unique ID output parameter.     Return Param.Value End Function 

The AddItem method has an additional responsibility. Because the spreadsheet lists only product codes, not product IDs, the AddItem method must look up the corresponding ID for each item before it can insert the order item record, as shown in Listing 16-6.

Listing 16-6 The private OrdersDB.AddItem function

 Private Function AddItem(ByVal item As OrderItem, _  ByVal con As SqlConnection)     Dim Param As SqlParameter     Dim cmd As SqlCommand     ' Check if the ProductID is 0. If so, the number must be looked     ' up using the ProductCode property.     ' This could be implemented as a separate public method in a      ' ProductsDB class, but the overhead of continuously opening and     ' closing the connection to check each product would slow down     ' the application.     If item.ProductID = 0 Then         cmd = New SqlCommand("GetProductIDByCode", con)         cmd.CommandType = CommandType.StoredProcedure         Param = cmd.Parameters.Add("@Code", SqlDbType.VarChar, 15)         Param.Value = item.ProductCode         Dim r As SqlDataReader         r = cmd.ExecuteReader(CommandBehavior.SingleRow)         r.Read()         item.ProductID = r("ID")         r.Close()     End If     cmd = New SqlCommand("OrderItemAdd", con)     cmd.CommandType = CommandType.StoredProcedure     Param = cmd.Parameters.Add("@ProductID", SqlDbType.Int)     Param.Value = item.ProductID     Param = cmd.Parameters.Add("@OrderID", SqlDbType.Int)     Param.Value = item.OrderID     Param = cmd.Parameters.Add("@PriceOrderedAt", SqlDbType.Money)     Param.Value = item.PriceOrderedAt     Param = cmd.Parameters.Add("@Quantity", SqlDbType.Int)     Param.Value = item.Quantity    ' Add the output parameter.     Param = cmd.Parameters.Add("@ID", SqlDbType.Int)     Param.Direction = ParameterDirection.Output     cmd.ExecuteNonQuery() End Function 

The InvoicerTables Class

With this factored design, the client needs to use two classes: OrdersDB and CustomersDB. The InvoicerTables class (shown in Listing 16-7) wraps these two classes, so that they client does not need to create them separately. It also includes the code that retrieves and applies the connection string information from a configuration file.

 Public Class InvoicerTables     Public Customers As CustomerDB     Public Orders As OrderDB     Public Sub New()         ' Retrieve the connection string.         Dim Connection As String = ConfigurationSettings.AppSettings( _           "InvoicerConnection")         Customers = New CustomerDB(Connection)         Orders = New OrderDB(Connection)     End Sub End Class 

To accommodate this design, you should mark the constructors for CustomersDB and OrdersDB with the accessibility keyword Friend. This means that only code in the same assembly (for example, code in the InvoicerTables class) can create a CustomersDB or OrdersDB instance.

The configuration file that InvoicerTables uses is shown in Listing 16-7.

Listing 16-7 Composition with InvoicerTables

 <?xml version="1.0"?> <configuration>   <appSettings>     <add key="InvoicerConnection"          value="Data Source=localhost;Initial Catalog=Invoicer;                 Integrated Security=SSPI" />   </appSettings> </configuration> 

There's one other part of the database the class that will allow access to the Excel mapping information. To ensure that the Excel processing code isn't tied too closely to the business logic, you implement it in a separate class (as discussed in the next section).

The ExcelTranslator Class

The next step is to prepare the server-side code that scans the submitted Excel files and inserts the required database records. This task conceals another key design decision.

A programmer who attacks this problem directly might be tempted to perform all the work in a single method call. The original Access program uses this approach and steps through the Excel file, generating and executing a new database command for each line item. This approach will work, but it is extremely inflexible. If you decide later to upgrade the application by adding a dedicated Windows client that submits order requests directly, you need to rewrite the database code completely. As mentioned at the beginning of this case study, one of the key requirements for this system is the ability to migrate to a more sophisticated design in the future.

To ensure that your solution isn't tightly coupled to the Excel format, you must separate the logic that scans the Excel spreadsheet from the logic that inserts the order and order item records. Thanks to the careful design of the database component, this task is easy all you need to do is rely on the Order information package class to transfer the retrieved data.

The ExcelTranslator encapsulates all the logic needed to scan through the Excel spreadsheet and convert its information in an Order object. The two methods of the ExcelTranslator class are implemented as shared helpers, which means the client won't need to create a class instance in order to use them.

The private GetMappings function retrieves the required mapping information from the database and returns it using a custom ExcelMapping class:

 Public Class ExcelMapping     Public ID As Integer     Public VersionCode As String     Public Description As String     Public PriceColumn As String     Public ProductColumn As String     Public QuantityColumn As String     Public SalesAgentCell As String     Public CustomerNameCell As String     Public DateEnteredCell As String     Public ProductStartRow As Integer End Class 

Listing 16-8 shows the ExcelTranslator.GetMappings function. The same configuration file is used to retrieve the database connection string.

Listing 16-8 The private ExcelTranslator.GetMappings function

 Private Shared Function GetMappings(ByVal version As String) _  As ExcelMapping     Dim Mapping As New ExcelMapping()     ' Retrieve the connection string.     Dim ConnectionString As String     ConnectionString = ConfigurationSettings.AppSettings( _       "InvoicerConnection")     ' Define and execute the GetExcelMappings stored procedure.     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("GetExcelMappings", con)     Dim r As SqlDataReader     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add( _      "@VersionCode", SqlDbType.NVarChar, 15)     Param.Value = version     Try         con.Open()         r = cmd.ExecuteReader(CommandBehavior.SingleRow)         r.Read()         Mapping.ID = r("ID")         Mapping.Description = r("Description")         Mapping.VersionCode = r("VersionCode")         Mapping.PriceColumn = r("PriceColumn")         Mapping.ProductColumn = r("ProductColumn")         Mapping.QuantityColumn = r("QuantityColumn")         Mapping.SalesAgentCell = r("SalesAgentCell")         Mapping.CustomerNameCell = r("CustomerNameCell")         Mapping.DateEnteredCell = r("DateEnteredCell")         Mapping.ProductStartRow = r("ProductStartRow")     Catch Err As Exception         ' This error is not packaged inside a custom application         ' exception because this is a private function.         ' The public function that calls this method can rethrow         ' this exception to the client with a more suitable wrapper.         Throw Err     Finally         If Not (r Is Nothing) Then r.Close() 

         con.Close()     End Try     Return Mapping End Function 

The ConvertExcelFileToOrder method is the most interesting part of the ExcelTranslator class. It accepts a file path to an Excel file and uses the Excel Automation objects to process the file. The Excel Automation objects are a COM-based library of controls that enable you to control (or "drive") Excel, provided Excel is installed on the current computer. You can use the Automation objects to open files, read values, or do anything else you can do manually through the Excel GUI.

To use the Excel Automation objects in .NET, you need to create a run­time callable wrapper (RCW), which is essentially a proxy through which your .NET code can interact with a COM object. You can create this wrapper by using the tlbimp.exe command-line utility or by adding a Visual Studio .NET project reference (as shown in Figure 16-7). Just choose Add Reference from the Project menu, and then click on the COM tab of the Add Reference window. The actual name of the Automation objects depends on the version of Excel you have installed. With Excel 2000, the library is version 9.0, whereas Excel 2002 (included with Microsoft Office XP) uses version 10.

Figure 16-7. The Excel Automation object

The newly added RCW objects use the same name as the COM library. The assemblies are generated in the project output directory and are visible in Solution Explorer (as shown in Figure 16-8).

Figure 16-8. The RCW for the Excel Automation object

After you have added the reference, you're free to create Excel.Application and Excel.Worksheet objects. Listing 16-9 shows the code that parses the Excel file and creates the Order object. The code interacts with the Excel document through a worksheet wrapper, which provides a Range method that enables you to retrieve a value in specific cells. The lookup cells are not hard-coded, however; they're drawn from the ExcelMappings database table.

Listing 16-9 The ExcelTranslator.ConvertExcelFileToOrder method

 Public Shared Function ConvertExcelFileToOrder(ByVal path As String) _  As Order     Dim Order As New Order()     ' This is the programmatic equivalent of starting the     ' Excel application.     Dim ExcelApp As New Excel.Application()     Try         ExcelApp.Workbooks.Open(path)         ' Select the first sheet (note that this object uses         ' 1-based, not zero-based counting, unlike .NET. 

         Dim Sheet As Excel.Worksheet = ExcelApp.Workbooks(1).Sheets(1)         ' Determine the version code.         Dim Version As String = Sheet.Range("F2").Value         ' Get mappings for this version.         Dim Mappings As ExcelMapping = GetMappings(Version)         ' Convert the sheet into an order.         Order.SalesAgentID = Sheet.Range(Mappings.SalesAgentCell).Value         Order.DateEntered = Sheet.Range(Mappings.DateEnteredCell).Value         Order.CustomerName = _          Sheet.Range(Mappings.CustomerNameCell).Value         Order.Status = StatusCode.Open         ' Store the cell column for each piece of information that must         ' be retrieved.         Dim Counter As Integer = Mappings.ProductStartRow         Dim Prod As String = Mappings.ProductColumn         Dim Price As String = Mappings.PriceColumn         Dim Q As String = Mappings.QuantityColumn         Do Until Sheet.Range(Prod & Counter.ToString()).Value = ""             ' Add item at current row.             Dim Item As New OrderItem()             Item.ProductCode = Sheet.Range(Prod & _                                Counter.ToString()).Value             Item.PriceOrderedAt = Sheet.Range(Price & _                                   Counter.ToString()).Value             Item.Quantity = Sheet.Range(Q & Counter.ToString()).Value             Order.OrderItems.Add(Item)             Counter += 1         Loop     Catch Err As Exception         ' Use "caller inform" exception handling pattern.         Throw New ApplicationException( _          "Exception encountered when executing command.", Err)     Finally         Excel.Workbooks.Close()         Excel.Quit()     End Try     Return Order End Function