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