将文件上载到数据库的最佳实践

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.

我正在寻找关于如何使用.NET Web应用程序中的数据库创建接口以从Excel文件上传数据的任何最佳实践或想法我应该使用一种机制来允许加载所有记录并标记错误或应该我使用一种机制，在发生错误时停止加载。

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

我从来没有处理过这种类型的要求，所以任何帮助都会超级！

Thanks

谢谢

4 个解决方案

#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.

由于这些是Excel文件，因此用户应该很容易更正Excel文件中的数据，而不是尝试使用其他界面来修复数据。只需确保错误消息将用户指向哪个字段是问题，并清楚地解释出错了什么。

#2

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).
将文件的结果绑定到某个网格（您可以使用传统的Connection / Command对象使用ODBC / OLE DB连接到Excel文件）。
Apply validation to the rows in the grid based on some set of business rules (excel data is normally quite dirty).
根据一组业务规则将验证应用于网格中的行（excel数据通常非常脏）。
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.
如果发生任何“坏”发生回滚并提供一些用户反馈。

#3

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.
ADO.Net批量加载API - 使用批量加载API将其放入临时表中。下面的代码段显示了一个打开.CSV文件并以编程方式将其加载到临时表的过程。

。

  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.
使用BCP或SSIS直接从电子表格或.CSV文件导入它。

#4

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?

你想把excel文件作为blob放在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.

至于DB。要么有一个单独的表上传，直到它们被检查并获得你的“真实”数据，或者有一个UploadUniqueId列，这样你就可以毫不费力地回滚任何上传。

delete tableName where UploadUniqueId = 'GUID'

删除tableName，其中UploadUniqueId ='GUID'

#1