Migration Strategies

As it stands, our solution addresses all the requirements identified in the problem analysis. It also provides a SQL Server database as a scalable backbone to build further enhancements, such as client programs that can query real-time sales data. However, the system also has a few potential headaches as a result of its evolution from Excel and Access. This final section addresses some of these problems and considers how the application might be enhanced in the future.

Adding Security

Currently, the ASP.NET page allows fairly open access to the database. Any individual who submits an Excel file that matches the prescribed format can commit order information. A better approach is to implement some sort of authorization that restricts uploads to valid clients. If nothing else, this can prevent the server hard drive from becoming clogged with invalid Excel files submitted by anyone else.

Adding this logic is quite easy. The SalesAgent table stores an optional password for every sales employee. To support authorization, you add a password text box to the ASP.NET page. When an upload is initiated, it compares the supplied password against the sales agent ID in the Excel file and stops the upload if the two don't match. This technique is best designed by adding a new ValidateAgent stored procedure along with additional methods.

Adding Early Validation

To further limit invalid data, the ASP.NET can perform the processing synchronously by communicating directly with the component and by assuming the responsibility currently assigned to the OrderProcessor component. This way, errors in the Excel file can be reported automatically. Currently, the sales administrator must check the OrderProcessor directory periodically for files that couldn't be successfully processed.

Using Unique Filenames

The current system assumes that the submitted files contain unique names. Otherwise, the attempt to move the Excel file to the completed directory or the attempt to save it to the processing directory might fail. This approach matches the organization of the Graphic Design Institute, in which orders are given unique filenames that combine the date, customer, and sales agent code. It also ensures that the same order file can't be inadvertently submitted twice.

If duplicate filenames are possible, it's easy to implement a more robust approach using the XML Web service and ASP.NET page. (No alternative is available for the first stage, direct e-mailing, although the sales manager can manually rename the file.) Before saving the file, the XML Web service and ASP.NET page can generate a unique identifier using the current date and time (along with a random number) or by creating a new GUID through the System.Guid class. This string can then be incorporated in the filename. The problem with this approach is that the same order can be submitted twice and added separately to the database. Unfortunately, as long as Excel is used to create orders, there is no easy way to prevent this possibility.

Excel Headaches and a Dedicated Windows Client

Even with these enhancements, a few stumbling blocks are inherent in using Excel to track orders. For example, using the Excel Automation objects imposes a basic overhead that might seriously degrade performance if hundreds of orders need to be processed simultaneously. On some computers, the Excel Automation objects don't release properly from memory and can quickly use up valuable server resources (because each Excel instance will use several megabytes of RAM). When using the OrderProcessor component, you might also run into difficulty because the component runs under a different account from the one Excel has been configured under. To solve this problem, you might need to log in and run Excel under the OrderProcessor account at least once, so all the required initial settings are configured properly. Alternatively, you could configure the OrderProcessor service to use a specific user account.

No obvious alternatives to the Excel Automation objects are available in this scenario. There are open-source projects on the Internet dedicated to reading the Excel file format, but because the format is extremely complex, this code requires a considerable investment of time, and there is no guarantee that the format will remain fixed in future versions of Microsoft Office. Excel files can be read through ADO.NET using an OLE DB provider, but the query-like syntax required to select cell values is awkward at best. And no matter how you retrieve the information from the Excel file, you will always be left with at least two inherent problems:

• There is no easy way to track whether the same Excel file is submitted twice. This can lead to duplicate order records in the database.

• There is no sure-fire way to enforce validation in an Excel spreadsheet. You can use read-only password-protected fields and a proprietary format, but adding these VBScript macros (and maintaining them with the correct business logic) is a time-consuming and error-prone task.

Eventually, if the Graphic Design Institute continues to prosper, these problems will become too significant. For this reason, we've designed our solution using a carefully standardized database and an intermediary layer of entity objects such as the Order class and the Customer class. The best migration plan is to encourage sales associates to use the dedicated Windows upload client. You can then take one of two approaches:

• Add the ability for the Windows client to process the order and submit the Order object through an XML Web service. This approach requires some form of automated deployment (as discussed in Chapter 15) because the business-processing logic changes frequently. But it removes the burden from the server and makes it easier to alert the client if the Excel file is invalid and prevent duplicate submissions.

• Enable sales associates to enter orders directly using the Windows client interface and save these files using a proprietary format. The development of this solution requires considerable extra work, but it solves all validation problems.

Best of all, with either of these approaches you can continue to use the same database and database components. You just need to add new methods to the OrderService XML Web service, as shown in Listing 16-18. Notice that this code is quite similar to the code used in the OrderProcessor service, but it's simplified because it doesn't have to deal with the Excel file.

Listing 16-18 The InsertOrder XML Web service method

 Public Class OrderService     Inherits System.Web.Services.WebService     Private Tables As New InvoicerTables()     <WebMethod()> _     Public Function InsertOrder(ByVal order As Order) As Integer         ' If the order is from a new customer, insert the customer         ' record.         Order.CustomerID = _           Tables.Customers.CheckIfCustomerExists(Order.CustomerName)         If Order.CustomerID = 0 Then             Dim NewCustomer As New Customer()             NewCustomer.Name = Order.CustomerName             Order.CustomerID = _               Tables.Customers.AddCustomer(NewCustomer)         End If         ' Commit the order to the database.         Return Tables.Orders.AddOrder(Order)     End Function End Class