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

score 5 · Answer 1 · answered Nov 13 '08 at 18:38

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

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

score 2 · Answer 2 · answered Nov 13 '08 at 18:17

You should upload the data and then flag it if it fails validation checks. For actually loading the data, you have a few options:

The ADO.Net bulk-load API - use the bulk load API to put it in a staging table. The snippet below shows a process to open a .CSV file and programatically load it into a staging table.

.

  public void Load() {
        bool OK = File.Exists(_filename);
        if (OK) {
            string sql = String.Format("Select * from {0}", FileName);
            OleDbConnection csv = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand(sql, csv);
            OleDbDataReader rs = null;
            SqlConnection db = null;
            SqlCommand clear = null;

            SqlBulkCopy bulk_load = null;
            try {
                    // Note two connections: one from the csv file
                    // and one to the database;
                    csv = new OleDbConnection();
                    csv.ConnectionString = ConnectionString;
                    csv.Open();
                    cmd = new OleDbCommand(sql, csv);
                    rs = cmd.ExecuteReader();

                    // Dung out the staging table
                    db = // [Create A DB conneciton Here]
                    clear = new SqlCommand("Truncate table Staging", db); // Left to the reader
                    clear.ExecuteNonQuery();

                   // Import into the staging table
                    bulk_load = new SqlBulkCopy(db);
                    bulk_load.DestinationTableName = Destination; // Actually an instance var
                    bulk_load.WriteToServer(rs);
                } catch (Exception ee) {
                    string summary = ee.Message;
                    string detail = ee.StackTrace;
                    //Notify(DisplayType.error, summary, detail);
                } finally {
                    if (rs != null) rs.Close();
                    if (csv != null) csv.Close();
                    if (bulk_load != null) bulk_load.Close();
                }
            }
        }

Use BCP or SSIS to import it, either directly from the spreadsheet or from a .CSV file.

score 1 · Accepted Answer · answered Nov 13 '08 at 18:15

1

If data integrity in your DB is important, do not allow data to be imported that has errors or does not meet the validation requirements of your DB.

Since these are Excel files, it should be easy enough for the user to correct the data in the Excel file, instead of trying to use another interface to fix the data. Just make sure the error messages direct the user to what field is the problem and clearly explain what is wrong.

answered Nov 13 '08 at 18:15

DCNYAM

11,966
8
53
70

1

Though validation is good, make sure you're storing in as raw a format as possible. You end up doing conversions a thousand times in a thousand places you wouldn't expect, and having the raw version in as non-converted a state as possible is a big win. – aronchick Sep 08 '09 at 16:33

score 0 · Answer 4 · answered Nov 13 '08 at 18:07

do you want to put the excel files in the DB as a blob? or do you want to parse the files and put the records in the files into the db?

I'm assuming it is the latter.

As a user I'd like to have the option to know what the errors are so I can fix them and try again. I think how I try again depends on how much data I'm uploading.

I'd try to do a hybrid solution .. if there are only a few errors show a screen to correct those errors so the user can move on quickly. If there are a ton of errors you should just can it and have the user try all over again.

As far as the DB. Either have a separate table that uploads go into until they are checked and get with your "real" data, or have a UploadUniqueId column so you can roll-back any upload without much fuss.

delete tableName where UploadUniqueId = 'GUID'

Best Practices for uploading files to database

4 Answers4

Linked