将错误记录写入审计表 [英] Write error records to audit table

问题描述

我正在将数据从csv文件插入到表中。以前我使用insert命令将数据写入表中，我能够将catch块中的问题记录写入审计表，当文件大小增加时，插入的时间太长，所以我使用的是SqlBulkCopy类。现在，有一种方法可以将问题记录写入审计表，而不是回滚事务。例如第1和第1第3条记录可以将它们写入查找表，第2条记录存在问题，请将其记录到审计表中。你能帮我吗？



使用INSERT命令。

Hi, I am inserting data from csv file to a table. Previously I was using insert command to write data to a table and I was able to write problem record in the catch block to audit table and when the file size grew it was taking too long for the inserts so I am using SqlBulkCopy class. Now, instead of rolling back the transaction is there a way to write problem records to audit table. For instance the 1st & 3rd records are fine write them to the lookup table and 2nd record has some problem so, log it to the audit table. Could you please help me.

Using INSERT command.

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) {
//Was able to Insert into audit table here
  message = ex.Message;
 }
}

我的尝试：



使用SqlBulkCopy类。

What I have tried:

Using SqlBulkCopy class.

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 into audit table here?   
        errmsg = ex.Message;
        return (errmsg);
    }
}

推荐答案

我不知道你如何定义和识别错误记录，但我会将整个csv行写入审计表中的单个字段。它看起来像你的csv解析代码不够强大，不足以涵盖任何可能发生的奇怪。此外，在尝试将数据推送到数据库之前，您可能会捕获错误记录。



查看此文章。它包含一个CSV解析器，它返回一个DataTable对象，并为所有列提供适当的数据类型。你必须提取适当的代码（应该很容易）并将它放入你自己的项目中。



SQLXAgent - SQL Express的工作 - 第3部分，共6部分 [ ^ ]

I don't know how you define and identify an "error record", but I would write the entire csv row to a single field in your audit table. It looks to me like your csv parsing code isn't robust enough to cover anything weird that would happen. Furthermore, you could potentially catch error records BEFORE you attempt to push the data to the database.

Check out this article. It contains a CSV parser that returns a DataTable object, with appropriate data types for all of the columns. You'll have to extract the appropriate code (should be easy) and put it into your own project.

SQLXAgent - Jobs for SQL Express - Part 3 of 6[^]

这篇关于将错误记录写入审计表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！