ADO.NET 2.0 - Cool uses of MARS
Basically there are like 5 big things in ADO.NET 2.0 that are supposed to make life great, Bulk Insert, Batch Updates, Multiple Active ResultSets (MARS), Notification Services and Object Spaces. There's also a ResultSet object that's pretty cool too. There's also a bunch of cool stuff in the XML Library (Mark Fussell deserves tons of props on this) but I'm not messing with it at the moment (probably get to it tomorrow). Of the stuff that's actually going to exist and be usable while I still have hair, it seems like Batch Updates and MARS are the most compelling. So here's Bill's first lesson on using the BatchUpdate Feature.
As you know, current DataAdapters walk through the rows of the datatable you pass it, checks the rowstate, determines what command to use and fires the commands one by one. This one at a time stuff isn't the fastest methodology in the world and more than a few people have complained about it. I don't see this as too big of a deal b/c if you deal with reasonable result sizes, it's pretty much a non issue. However, if you are one of those people that insist on shoving over 38 trillion records, then yes, the current infrastructure is a little slow, particularly compared to ADO. So in the new implementation, you can specify how many records you want to send back via the DataAdapter's UpdateBatchSize property. The first thing that should strike you is that the property is of Type DECIMAL. Yes, that means you can send back a meager 79,228,162,514,264,337,593,543,950,335 records in one sitting. I guess some wimpy Integer value like 2,147,483,647 wasn't enough. Just to be a smart ass, I wanted to see if this would actually work. Unfortunately, Northwind isn't going to cut it for the stress test. So I wrote a T-SQL Loop insert nonsense just to get the record count. If I keep it running from now, until ObjectSpaces are officially released, my puny Pentium IV will still be trying to insert that many records. (I have a point here, honestly). To use it, you only need one line of code... myDataAdapter.UpdateBatchSize = 79,228,162,514,256,337,593,543,950,335 and off you go. Make sure however that you don't use this in an unchecked contest b/c I can only imagine what would happen if you had myDataAdapter.UpdateBatchSize = 79,228,162,514,256,337,593,543,950,335++; Then again, this will hopefully keep those VB programmers in line.
To really get a feel for it, you ought to fire up SQL Server profiler and leave the UpdateBatchSize at 1. You'll see the same thing that you would if you were using an old school datadapter. However, crank that baby up to 3 trillion and you'll see the exec sp_executesql and all sorts of stuff. If you had a nice big flat table with like 100 rows and try this with 100,000 records, it's pretty funny what happens but I'll let you have fun playing with that yourself.
So as you can imagine, you may have some blocking here and the user might want to do something else in the tiem it takes to update all of those records. In comes MARS. One the one hand this makes perfect sense, on the other it gives you all the ammo you need to make a big mess! One neat thing about it though is that you can now use Mars to fire a DataReader against your DB while the update is going on. As such, you can simultaneously grab data from the db while you are submitting updates.
Another interesting example of what you can do with MARS is using one DataReader to grab values and use those values from within your Readers while rdr.Read() loop to fire another reader. If you want to do this today, you need to walk through the read, load up an ArrayList or something, close the reader, and use the ArrayList(or whatever) and call another reader or .Excecutexxx. It kind of flies in the face of using Nested Loops so this is a pretty big improvement. Now, the REALLY neat thing to do is try to use this recursively (make Double sure you have an exit condition). In ASP.NET you can bind to a datareader so this could really get interesting and I'm about to try it. Will give you a full report tomorrow.
SqlConnection cn = new SqlConnection(“SomeConnectionString“);
SqlDataReader dr;
SqlCommand cmd = new SqlCommand(”Select * From someTable where KeyField = WhateverField”, cn);
if(cn.State != ConnectionState.Open){cn.Open();}
dr = cmd.ExecuteReader();
while dr.Read(){
DataReader dr2;
SqlCommand cmd2 = new SqlCommand(“Select *from ChildTable where KeyField = @KeyField“, cn);
cmd2.Parameters(“@KeyField“).Value = dr.GetString[0];
while dr2.Read(){ //DoSomething else}
}
This is really one of the coolest things I've seen mainly b/c it actually works. You can also use this in the context of a DataAdapter.Update and using the same connection, spin off a datareader to grab those new values.
There are three basic ways to do this Async and I've only used the first one, so I have some work to do before writing on. Check back tomorrow and I'll have the code and the results. I'm really amazed at what you can do here, amazed! However, this thing can DEFINITELy cause you some trouble if used incorrectly and for previously stated reasons, I'm sure some people are going to do that. I for one know I am just to see what it can handle. But like everything else, if you know what you are doing and use it correctly (I don't yet), this looks like it opens all sorts of doors up. I guess I'll have enough to occupy my time with while waiting for ObjectSpaces that I don't have anything to complain about.
}