C#捕获sqlbulkcopy异常 [英] C# catch sqlbulkcopy exception

问题描述

如何捕获SqlBulkCopy异常或问题记录.我正在使用以下代码.

How do I catch SqlBulkCopy exception or problem records. I am using the below code.

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);
    }
}

我尝试过的事情:

如何使用下面的代码对上面的代码建模，以通过适当的错误处理捕获异常.

What I have tried:

How to model my above code using the below code to catch exception with proper error handling.

public static string errorIndex = "Error at: "; // record error row index
public static DataTable errorDT; //record row details

public static void Run()
{
    string Lookup = System.Configuration.ConfigurationManager.AppSettings["Lookup"];
    var Lines = File.ReadAllLines(Lookup);
    DataTable dt = new DataTable("lines");
    string[] columnsPLines1 = null;

    if (Lines1.Count() > 0)
    {
        columnsPLines1 = Lines1[0].Split(new char[] { ''|'' });

        foreach (var column in columnsPLines1)
            dt.Columns.Add(column);
    }

    for (int i = 1; i < Lines1.Count(); i++)
    {
        DataRow dr = dt.NewRow();
        string[] values = Lines1[i].Split(new char[] { ''|'' });

        for (int j = 0; j < values.Count() && j < Lines1.Count(); j++)
        {

            dr[j] = values[j];
        }

        dt.Rows.Add(dr);
    }
    errorDT = dt.Clone();
    InsertMp(dt, 0);
    Console.WriteLine(errorIndex);
    for (int i = 0; i < errorDT.Rows.Count; i++)
    {
        for (int ii = 0; ii < dt.Columns.Count; ii++)
        {
            Console.Write(errorDT.Rows[i][ii]+"\t");
        }
        Console.WriteLine();
    }
}

public static void InsertMp(DataTable dt,int index)
{
    SqlConnection connection;
    string constring = ConfigurationManager.ConnectionStrings["Connection"].ToString();
    connection = new SqlConnection(constring);

    using (SqlBulkCopy blkcopy = new SqlBulkCopy(connection.ConnectionString))
    {
        try
        {
            connection.Open();
            blkcopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
            blkcopy.NotifyAfter = 1;
            blkcopy.DestinationTableName = "Lookup";
            blkcopy.WriteToServer(dt);
            try
            {
                blkcopy.WriteToServer(dt);
            }
            catch (Exception e)
            {
                if (dt.Rows.Count == 1)
                {
                    errorIndex +=(index.ToString()+"; " );
                    errorDT.ImportRow(dt.Rows[0]);
                    return;
                }

                int middle = dt.Rows.Count / 2;
                DataTable table = dt.Clone();

                for (int i = 0; i < middle; i++)
                    table.ImportRow(dt.Rows[i]);
                InsertMp(table,index);
                table.Clear();

                for (int i = middle; i < dt.Rows.Count; i++)
                    table.ImportRow(dt.Rows[i]);
                InsertMp(table, index + middle);
                table.Clear();

            }
            finally
            {
                blkcopy.Close();
            }

        }
        catch (Exception ex)
        {

        }
        finally
        {
            connection.Close();

        }
    }
}

private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    throw new NotImplementedException();
}

推荐答案

在尝试批量加载"之前先清除"数据.

它被称为:ETL(提取，转换和加载)是有原因的；保持简单.

"Clean" your data before trying to "bulk load it".

It''s called: ETL (extract, TRANSFORM, and load) for a reason; keeping it simple.

这篇关于C#捕获sqlbulkcopy异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！