SqlBulkCopy Not Respecting NotifyAfter

c# sqlbulkcopy

Question

UPDATE: So this was a dumb mistake and somewhat embarassing. I had the following line below where I was setting the value correctly:

copier.NotifyAfter = data.Rows.Count;

So I was overwriting my own value and it wasn't working.

I'm going to leave this post here as a reminder to others that sometimes, you just need to look a few lines lower to see that you're overwriting what you were trying to do. Hopefully someone will look at this and think, "Ah ha! Yeah, got it. Oops."

If someone with more mod powers than me wants to lock this, or thinks it adds no value and wants to remove it, that works for me.

In the meantime, oops. :)


I'm trying to illustrate (for a demo) the batching support in SqlBulkCopy. To do this, I have a method that takes a data table (with 500,000 rows in it), sets both BatchSize and NotifyAfter to 100,000, and prints at each firing of the SqlRowsCopied event.

The problem is, it doesn't work. No matter what I do I can't get more than one message printed. I know BatchSize works, because if I set it very low I can see the rows coming in, but I can't get the event to fire more than once. Anyone know what I'm doing wrong?

The method:

public void InsertInBatches(SqlConnection connection, DataTable data)
{
    int currentBatch = 1;

    Console.WriteLine($"{data.Rows.Count.ToString("N0")} rows, {data.Rows.Count / 100000} batches");
    using (var copier = new SqlBulkCopy(connection))
    {
        copier.NotifyAfter = 100000;
        copier.BatchSize = 100000;
        copier.SqlRowsCopied += (s, e) => Console.WriteLine($"Batch {currentBatch++} complete.");
        copier.DestinationTableName = "dbo.BulkInsertPerformance";
        MapBulkCopyColumns(copier, data);
        copier.NotifyAfter = data.Rows.Count;
        copier.WriteToServer(data);
    }
}

The Output

500,000 rows, 5 batches

Batch 1 complete.

Press any key to continue.

By the end of it, I can see 500,000 rows in the database. I just can't get the output to respect the NotifyAfter property.

Accepted Answer

Remove

copier.NotifyAfter = data.Rows.Count;

public void InsertInBatches(SqlConnection connection, DataTable data)
{
    int currentBatch = 1;

    Console.WriteLine($"{data.Rows.Count.ToString("N0")} rows, {data.Rows.Count / 100000} batches");
    using (var copier = new SqlBulkCopy(connection))
    {
        copier.NotifyAfter = 100000;
        copier.BatchSize = 100000;
        copier.SqlRowsCopied += (s, e) => Console.WriteLine($"Batch {currentBatch++} complete.");
        copier.DestinationTableName = "dbo.BulkInsertPerformance";
        MapBulkCopyColumns(copier, data);
        copier.WriteToServer(data);
    }
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why