Feb
21
Written by:
Andy Hock
2/21/2007 5:52 PM
Recently, I came up with a design (database and c#) for automating imports and exports of data for clients. It uses an asynchronous mail class to perform the following:
- A client sends a spreadsheet or delimited spreadsheet file as an attachment to a predefined email address.
- Each email address is defined with certain characteristics. For instance, we may know that, for a specific email address, the business_id is 226, and/or that a specific stored procedure will be called, but only if the data is formatted correctly, and only if it passes a detailed authentication procedure.
- The stored procedures are designed to output log files, describing specific errors, whether to only print the first type of an error (I wish Microsoft had implemented this in their VS2005 Java to C# Conversion Tool--it's worthless because there might be 4 different errors repeated 1000 times, but the report you see only shows the 1000 errors, not the same error 1000 times), to always output a file in the same format as received with all the records which were not processed because of errors, and a log file, describing what happened during the import or export process.
- When an email is received, we decode the attachment, authenticate the user, then call one of the stored procedures specified to process the data.
- We call the stored procedure asynchronously, so as to not interupt other processing on the server, or other import requests.
- Once completed, the delegate function is informed, and a response is emailed back to the client, with a log file, any error files, and a file containing the records that need to be fixed.
- The client fixes the bad records according to the error files, and resends the attachment, and the process continues until all records are processed
Some people questioned why we need to do this, since in SQL 2005 (which we are not yet using) has an email capability with DTS. The fact is, by creating a perfect 'loop' in the import process and having the ability to perform our own authentication of email attachments and having the ability to log errors in a more readable way than SQL ever could and having the ability to attach characteristics to specific email addresses (which also cannot be done using SQL 2000 or 2005), we have greatly simplified the import process.
Of course, it's the kind of thing which only those who have clients who need to be able to send you data to import, especially clients who are not technical, but know Microsoft Excel, would probably appreciate.
The important point here is not the automated import and export process, but the fact that, using .NET Framework 2.0 we are able to call stored procedures asynchronously 'out-of-the-box'. We accomplished this thanks to a short article I read some months ago that shows how to call SQL Stored Procedures Asynchronously Using .NET 2.0. Considering the AJAX world we live in, this capability will be greatly appreciated...and is yet another reason I prefer .NET over J2EE.
Tags: