SqlBulkCopy
Kim just got a new Media Center PC and I loaded the latest Visual Studio 2005 bits on it. Got a little frustrated with XPath so I took a little while away to see what's going on with the ADO.NET 2.0 Library. When I had played with the SqlBulkCopy
before, I'm not sure if I just missed it or if they weren't there but there's lots o good stuff now.
SqlBulkCopyOptions - There are 7 enum values for this and combined, they
give you an amazing amount of control over the copy procedure.
Default - Nothing special here, it just uses the default settings. As a rule, the defaults are structured in such a way that they'll do the least amount of checking. Basically, they are set at the most permissive level available.
CheckConstraints does just what it says, it verifies CheckConstraints as the data is
being inserted and if anything violates them, it yells.
KeepNulls - another real shocker here. If you have default values set on your tables
KeepNulls will cause them to be ignored.
TableLock - by default, Row locks are used as the data is loaded. This is the least intrusive
way to do an update, particularly in cases where you're dealing with a lot of data. Obviously,
if you lock the entire table, not much else is going to be done while the lock is in place.
I haven't done the performance tests yet but it looks like the reason for this is performance.
You lock the whole table but the operations go faster. This is just conjecture on my part
but if you didn't get a performance benefit, it's hard to see where you'd want to lock the whole table
in most instance.
KeepIdentity - again, this is simply another mechanism to allow you to override what the
database would naturally do. It will use the identity values that you have in place
versus generating new ones.
FireTriggers - by default, this is off. When off, none of the database's triggers are fired. When it's on, they do. Again, you probably could have guessed this without me commenting on it. Yet it's another way that you can precisely control your updates.
UseInternalTransaction - i haven't been able to verify that this actually works the way I understand it to. That probably means I'm mistaken. Essentially, you set this and each update occurs locally within the context of its own transaction. If you specify another transaction for it, an exception will be raised. That definitely works as advertised. .
-----------
There are a few other features that really allow you to finely tune your updates, most of them are very intuitve as well:
public static void Adapter()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlBulkCopy bc = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default);
bc.NotifyAfter = 10;
bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(bc_SqlRowsCopied);
bc.BatchSize = 100;
}
static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Debug.WriteLine("10 More Rows have been copied, damn this is verbose!");
}
NotifyAfter lets you specify an Int32 argument and after that many rows have been updated, the SqlRowsCopied event will be raised. Underneath the line where I set the NotifyAfter property, i added the event handler. Since I set it to 10, this would be pretty damn verbose in most instances, but if that's what floats your boat you can do it. Just like the BatchSize of the SqlDataAdapter, you can set this. So if you had 100 records and set this to 10, you'd have 10 trips to the db. If the NotifyAfter property was set to 10, the SqlRowsCopied event would be raised 10 times.
This is a really cool option b/c up until now, there was no 'good' mechanism for data transfer. You could always fake it by setting the AcceptChangesDuringFill property of your SqlDataAdapter to false, then use that table with a destination adapter and transfer data that way, but this was a hack and as such, was about the least efficient method to transfer data known to man. This isn't a slight on the design of ADO.NET, it's just that disconnected architecture doesn't really lend itself well to large data transfers, at least it didn't until now