Click here to Skip to main content
15,890,882 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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();
}
Posted
Updated 30-Apr-18 7:41am
v2

1 solution

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

It's called: ETL (extract, TRANSFORM, and load) for a reason; keeping it simple.
 
Share this answer
 
Comments
Eric Lynch 30-Apr-18 14:20pm    
I was just about to go off and check whether the SQL bulk API let's you determine which rows failed...classic case of over-engineering.

Gerry's approach is much better...in this case (as usual) simpler is better. You are much better off validating the data before submitting it.
Member 12586110 30-Apr-18 14:29pm    
The table I have has varchar datatype on all the columns and no constarints. All the data I get is also text. I just want to handle exceptions if I were to run into problem records. Please help me.
Eric Lynch 30-Apr-18 17:17pm    
If you're getting an exception, then (by definition) something is going wrong. There are really only two things I can think of that might go wrong. The first is some sort of systemic failure: dropped connection, timeout, resource limit, etc. The second is a problem with the data.

The approach to handling each is different. If its a data problem, Gerry is correct, you should validate the data before the bulk insertion to prevent the problem in the first place. This remains my guess.

Systemic problems fall into two categories: transient and persistent. For transient problems (e.g. a network outage), retrying is really your only option. However, if your problem is systemic, it sounds persistent. So, to fix it you would need to understand what has gone wrong. For example, if its a timeout issue, you can increase the timeout.

Regardless of your problem, the first step to solving it is to understand it. I'm happy to try to help...as are a lot of other folks on this site.

If you update your original question to include more information about the exception, folks here will be able to better assist you. I suggest writing out e.ToString(), where "e" is the exception and then cut/pasting this into your question.

Without this information, we're both just guessing about the correct course of action to fix the problem :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900