none
c# capture problem records in SqlBulkCopy RRS feed

  • Question

  • Hi, I am inserting data from text stream to a table. Previously I was using insert command to write data to a table and I was able to write problem records in the catch block to an audit log table and when the stream size grew it was taking too long for the inserts so using SqlBulkCopy class. Instead of rolling back the transaction is there a way to write problem records to an audit log table.

    For instance, 1st & 3rd records insert fine write them to the actual table and 2nd record has some problem insert the record into the audit log table. My table has varchar datatype on all the columns.

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

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

    Thank you.


    SQLEnthusiast

    Friday, April 27, 2018 4:17 PM

All replies

  • SqlBulkCopy writes in a transaction so imagine this scenario. 

    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

    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.

    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.

    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.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, April 27, 2018 5:29 PM
    Moderator
  • Hello CSharp,

    >>c# capture problem records in SqlBulkCopy

    When you use SqlBulkCopy to transport multi rows data to database you couldn't locate which rows encounter invalid exception because BulkCopy operation insert recode block by block instead of row by row.

    As for your circumstance, consider speed first you could use SqlBulkCopy but when exception occurs you need to loop the table and use insert statement row by row to find which row is invalid. You could refer the below blog.

    Retrieving failed records after an SqlBulkCopy exception

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 30, 2018 7:04 AM
    Moderator