将错误记录写入审计表

问题描述

我正在将数据从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);
    }
}

推荐答案

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