.net - Best Practices for uploading files to database

Question

I am looking for any best practices or ideas on how you would create an interface with a DB from a .NET web application to upload data from Excel files Should I use a mechanism that allows all the records to be loaded and flags the errors or should I use a mechanism that stops the load when an error occurs.

I've never had to deal with this type of requirement before so any help would be super!

Thanks

Answer 1

I would try the following approach which has worked well in the past.

1. Allow the user to upload the file, put it somewhere on disk.
2. Bind the results of the file to some grid (you can connect to Excel files using ODBC/OLE DB using traditional Connection/Command objects).
3. Apply validation to the rows in the grid based on some set of business rules (excel data is normally quite dirty).
4. Allow the user to update values in the grid and correct validation issues.
5. When all the data is kosher and the user is happy with it perform a bulk insert in a transaction.
6. If anything "bad" happens rollback and present some user feedback.