c＃捕获SqlBulkCopy中的问题记录 [英] c# capture problem records in SqlBulkCopy

问题描述

我正在从文本流中将数据插入到表中。以前我使用insert命令将数据写入表中，并且我能够将catch
块中的问题记录写入审计日志表，当流大小增加时，插入所需的时间太长，因此使用SqlBulkCopy类。而不是回滚事务是有一种方法将问题记录写入审计日志表。

例如，1st&第3条记录插入正确写入实际表，第2条记录有一些问题将记录插入审计日志表。我的表在所有列上都有varchar
数据类型。

使用插入命令

public string writetotbl(IList < string > records) {
 string connString = ConfigurationManager.ConnectionStrings["myDBConnString"].ConnectionString;

 try {

var lkup = from record in records
                         let rec = records.Split(',')
                         select new Lookup
                         {
                             Id = rec[0],
                             Code = rec[1],
                             Description = rec[2]
                         };

  foreach(var i in lkup) {
   using(SqlConnection sqlConnection = new SqlConnection(connectionString)) {
    sqlConnection.Open();

    using(SqlCommand cmd = new SqlCommand("INSERT INTO [Lookup] ([Id], [Code], [Description]) VALUES (@Id, @Code, @Description)", sqlConnection)) {
     cmd.Parameters.AddWithValue("@Id", i.Id);
     cmd.Parameters.AddWithValue("@Code", i.Code);
     cmd.Parameters.AddWithValue("@Description", i.Description);

     cmd.ExecuteNonQuery();
    }
      sqlConnection.Close();
   }
  }
 } 
catch (Exception ex) {
using (SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[log] ([ErrorRecord], [ErrorMessage]) VALUES (@ErrorRecord, @ErrorMessage)", sqlConnection))
{
cmd.Parameters.AddWithValue("@ErrorRecord", I.Id + ", " + I.Code + ", " + I.Description);
cmd.Parameters.AddWithValue("@ErrorMessage", ex.Message);
cmd.ExecuteNonQuery();
}
  message = ex.Message;
 }
}

使用SqlBulkCopy

Using SqlBulkCopy

private string writetotbl(IList<string> records)
{
    string connString = ConfigurationManager.ConnectionStrings["myDBConnString"].ConnectionString;

    try
    {
        var lkup = from record in records
                         let rec = records.Split(',')
                         select new Lookup
                         {
                             Id = rec[0],
                             Code = rec[1],
                             Description = rec[2]
                         };

        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("@Id", typeof(int)));
        dt.Columns.Add(new DataColumn("@Code", typeof(string)));
        dt.Columns.Add(new DataColumn("@Description", typeof(string)));
        DataRow dr = dt.NewRow();

        foreach (var i in lkup)
        {
            dr = dt.NewRow();
            dr["Id"] = i.Id.Replace("\"", "");
            dr["Code"] = i.Code.Replace("\"", "");
            dr["Description"] = i.Description.Replace("\"", "");
            dt.Rows.Add(dr);
        }

        using (var conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlBulkCopy s = new SqlBulkCopy(conn))
            {
                s.DestinationTableName = "Lookup";
                s.BatchSize = dt.Rows.Count;
                s.BulkCopyTimeout = 0;
                s.ColumnMappings.Add("Id", "Id");
                s.ColumnMappings.Add("Code", "Code");
                s.ColumnMappings.Add("Description", "Description");
                s.WriteToServer(dt);
                s.Close();
            }
            conn.Close();
        }
        return (null);
    }
    catch (Exception ex)
    {
        //How to Insert records into audit log table here?   
        errmsg = ex.Message;
        return (errmsg);
    }
}

谢谢。

SQLEnthusiast

SQLEnthusiast

推荐答案

SqlBulkCopy会在事务中写入，所以想象一下这种情况。  ;

SqlBulkCopy writes in a transaction so imagine this scenario. 

将行A，B和C添加到表中$
调用WriteToServer

行B失败，但因为这是一个事务无插入行

Add rows A, B and C to the table
Call WriteToServer
Row B fails but since this is a transaction none of the rows are inserted

看到问题？哪一行无效无关紧要，整个批次都被抛出。因此，您描述的关于插入第1行和第3行但将第2行发送到审计表的方案将无法与SqlBulkCopy一起使用，因为任何错误都会导致所有行失败。
这是为什么它更快，因为一切都在一次交易中发生的原因之一。

See the problem? It doesn't matter which row was invalid, the entire batch is thrown out. So the scenario you describe about inserting rows 1 and 3 but sending row 2 to an audit table won't work with SqlBulkCopy as any errors causes all the rows to fail. That is one of the reasons why it is faster since everything is occurring in a single transaction.

不幸的是，你所能做的事情非常有限。返回的异常可能表示如果它返回SqlException或类似的失败，但您必须使用启发式算法来解决它。没有插入任何行（并且
可能是更多无效的行）。此时，您可以尝试删除坏行并再次尝试批处理，但如果有另一行无效，那么您将反复重复此操作。如果所有的行都不好那么你会重复，但是很多行是
，这是非常低效的。

Unfortunately you are very limited in what you can do. The exception that is returned may indicate the failure if it returns a SqlException or similar but you'll have to use heuristics to figure it out. Irrelevant none of the rows were inserted (and there may be more rows that are invalid). At this point you can try to remove the bad row and try the batch again but if there is another row that is invalid then you'll be repeating this over and over. If all the rows are bad then you'll be repeating for however many rows there are which is very inefficient.

另一种方法是使用较小的批量大小（这意味着性能将更接近你自己可以做的批量插入。然后，如果批次失败，您可以将整个批次标记为坏，然后转到下一个批次。批量
大小越大，插入越快，但由于记录不良，将跳过更多（可能）良好的记录。

MSDN 有几个关于如何使用批量复制的事务的示例，这可能为您提供有关如何解决此问题的一些想法。

An alternative approach is to use smaller batch sizes (which means the performance is going to be closer to just batch inserts you can do yourself). Then if a batch fails you can flag the entire batch as bad and move on to the next one. The larger the batch size the faster the inserts but the more (potentially) good records will be skipped because of a bad record. MSDN has several examples on how you can use transactions with bulk copy which may provide you some ideas on how to solve this problem.

就个人而言，我会在记录异常的情况下使批处理失败，然后让别人弄清楚什么是坏的。当然，这是在我已经完成尽职调查并确保该行有效之后，甚至在开始时将其添加到表中。

Personally I would fail the batch with the exception logged and then let someone figure out what went bad. This is, of course, after I've already done due diligence and made sure the row was valid before even adding it to the table to begin with.

这篇关于c＃捕获SqlBulkCopy中的问题记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！