The Back-End OrderProcessor Service

Now that the data classes have abstracted away the heavy lifting, the OrderProcessor has only one task remaining: to scan the Excel file and invoke the methods needed to add the order and order item records.

The OrderProcessor is created as a Windows service that runs uninterrupted on any computer (including a server or the sales manager's workstation). For most of its life, it remains quietly idle. When a new Excel file appears in the designated directory, however, it springs into action, transferring the information into the database and moving the file to a new directory. To automate this task, the OrderProcessor uses the FileSystemWatcher class from the System.IO namespace. This class has a single purpose in life: to forward notification from the operating system to your program when certain changes occur to the file system. This enables you to respond to actions such as the creation, deletion, or modification of a file without resorting to resource-intensive polling. Your code is automatically notified when a change occurs through a standard .NET event.

To use the FileSystemWatcher class, you need to take three simple steps:

Set the Path property to indicate the directory you want to monitor (for instance, E:\ExcelFiles).
Set the Filter property to indicate the types of files you are monitoring for (for instance, *.xls).
If you're monitoring changes, set the NotifyFilter to indicate the type of changes you want to monitor for. In our case, we're monitoring file creation, so we don't need to use the NotifyFilter.

The FileSystemWatcher class raises four events: Changed, Created, Deleted, and Renamed. If you need to, you can disable these events by setting the EnableRaisingEvents property to False.

The Created, Deleted, and Renamed events are easy to handle. If you want to use the Changed event, however, you need to use the NotifyFilter property to indicate the types of changes you're looking for (for example, a file size change or filename change). Otherwise, your program might be swamped by an unceasing series of events as files are modified. If you're monitoring changes, you might also need to tweak the InternalBufferSize property to make sure that changes aren't lost if they occur in quick succession. The InternalBufferSize property should be left alone if possible, however, because it uses nonpaged memory that cannot be swapped to disk. The default buffer is 8192 bytes (8 KB).

Listing 16-10 shows the basic structure for the OrderProcessor service.

Listing 16-10 The OrderProcessor service

 Public Class OrderProcessor     Inherits System.ServiceProcess.ServiceBase     ' This class will notify the code when a new Excel appears.     Private Watcher As New FileSystemWatcher()     ' This provides the link to the database tables.     Private Tables As InvoicerTables     ' This stores the directory where files should be placed after they     ' are processed.     Private CompleteDirectory As String     ' Fires when the service is started.     Protected Overrides Sub OnStart(ByVal args() As String)         ' (Code omitted.)     End Sub     ' Fires when the service is stopped.     Protected Overrides Sub OnStop()         ' (Code omitted.)     End Sub     ' Handles the FileSystemWatcher.Created event and processes     ' the new file.     Private Sub Watcher_Created(ByVal sender As Object, _      ByVal e As System.IO.FileSystemEventArgs)         ' (Code omitted.)     End Sub     ' Used to log diagnostic messages.     Private Sub Log(ByVal message As String)         ' (Code omitted.)     End Sub     ' Creates the service instance when it is first loaded.     Public Shared Sub Main()         Dim Process As New OrderProcessor()         ServiceBase.Run(Process)     End Sub End Class

You'll notice that a private Log function handles any diagnostic messages the code needs to output. This is an important step because Windows services are notoriously difficult to debug. The OnStart code, for example, will complete before you have the chance to attach a debugger. If an unhandled exception is thrown, you won't receive an informative message; instead, the Service Control Manager will just inform you that the service has started and stopped.

To help track problems, the Log function adds messages to the Application event log, as shown in Listing 16-11.

Listing 16-11 Logging messages

 Private Sub Log(ByVal message As String)     ' Place the message in the event log.     Dim Log As New EventLog("Application")     Log.Source = "OrderProcessor"     Log.WriteEntry(message)     ' Echo the message to any debug listeners.     Debug.WriteLine(DateTime.Now.ToLongTimeString() & " ------------")     Debug.WriteLine(message) End Sub

The OnStart method for the service performs several tasks (as shown in Listing 16-12). First of all, it reads the required information from the application configuration file. This step could be performed in the Main method or the constructor, but placing it in the OnStart method ensures that any changes to the configuration file are picked up when the service is stopped and restarted. The OnStart method also creates a new instance of the InvoicerTables class, allowing it to read the same configuration file to determine the database connection string. Finally, the FileSystemWatcher class is configured and the event handler is attached.

Listing 16-12 Starting the service

 Protected Overrides Sub OnStart(ByVal args() As String)     ' Determine the path to monitor.     Watcher.Path = ConfigurationSettings.AppSettings("MonitorPath")     CompleteDirectory = _      ConfigurationSettings.AppSettings("CompletePath")     Tables = New InvoicerTables()     ' Configure the FileSystemWatcher for Excel files.     Watcher.Filter = "*.xls"     ' Enable events.     Watcher.EnableRaisingEvents = True     AddHandler Watcher.Created, AddressOf Watcher_Created     Log("Started watching " & Watcher.Path) End Sub

The configuration file is shown here in its entirety:

 <?xml version="1.0"?> <configuration>   <appSettings>     <add key="InvoicerConnection"          value="Data Source=localhost;Initial Catalog=Invoicer;                 Integrated Security=SSPI" />     <add key="MonitorPath"          value="E:\ExcelFiles" />     <add key="CompletePath"          value="E:\ExcelFiles\Complete" />   </appSettings> </configuration>

The OnStop method detaches the event handler and releases the InvoicerTables instance (as shown in Listing 16-13).

Listing 16-13 Stopping the service

 Protected Overrides Sub OnStop()     Watcher.EnableRaisingEvents = False     RemoveHandler Watcher.Created, AddressOf Watcher_Created     Tables = Nothing     Log("Stopped") End Sub

Finally, the most important work is performed in response to the FileSystemWatcher.Created event (as shown in Listing 16-14). The file is processed in four stages:

The file is submitted to the ExcelTranslator.ConvertExcelFileToOrder method, which returns the corresponding Order object.
The Order.CustomerName property is checked against the database using the CheckIfCustomerExists method of the CustomersDB class. If a record exists for this customer, the appropriate ID is added to the order. Otherwise, the customer record is inserted.
The Order object is submitted to the AddOrder method of the OrdersDB class.

The Excel file is copied to the directory for completed files. This step isn't necessary, but it enables you to see at a glance (using Windows Explorer) which files have been processed. This last step is performed only if the file is successfully imported, so it also enables you to see which files haven't been imported (perhaps due to an unrecoverable error).

Listing 16-14 Importing a new file

 Private Sub Watcher_Created(ByVal sender As Object, _  ByVal e As System.IO.FileSystemEventArgs)     Log(e.FullPath)     Try         ' A new Excel file has appeared. Process it.         Dim NewOrder As Order         NewOrder = ExcelTranslator.ConvertExcelFileToOrder(e.FullPath)         ' If the order is from a new customer, insert the customer         ' record.         NewOrder.CustomerID = _           Tables.Customers.CheckIfCustomerExists(NewOrder.CustomerName)         If NewOrder.CustomerID = 0 Then             Dim NewCustomer As New Customer()             NewCustomer.Name = NewOrder.CustomerName             NewOrder.CustomerID = _               Tables.Customers.AddCustomer(NewCustomer)         End If         ' Commit the order to the database.         NewOrder.ID = Tables.Orders.AddOrder(NewOrder)         ' Move the file to a new directory.         File.Move(e.FullPath, CompleteDirectory & "\" & e.Name)         Log("Order " & NewOrder.ID.ToString() & " added")     Catch Err As Exception         ' Additional logging code can be added here.         ' For example, you might want to send an email to the sales          ' manager.         Log("Failure " & Err.ToString())     End Try End Sub

With this code, the solution is complete in its first stage. You can run the OrderProcessor component and manually copy Excel file attachments to the appropriate directory for automatic processing.