How to update a few thousand records
I believe that in data-intensive applications (uh, are there really other types of apps?) there are a few problems to avoid, rather than brilliantly solve. One of this is SQL paging. Another one is, maybe, large batch updates. Of the two, the latter looks much more approachable. None of the two, however, find a system level solution in .NET 1.x.
(A system-level solution here means something the framework provides that gives you a good excuse to take it for granted and avoid thinking of a better approach.)
No matter SQL paging is supported in the PDC build of Whidbey (and even in the March04 Community Drop), it won't be in the final product. Rumors say that there will be an add-on or, maybe, a service pack shortly after with some enhancements including SQL paging, server cursors, and more.
What about batch update improvements?
In version 1.x, batch update submits one record at a time. All the overloads of the data adapter's Update method end up calling the one that processes an array of DataRow objects. The implementation simply loops through the array, decides which command to execute, and goes with that. You understand that 5,000 records to batch update are definitely a pain-in-the-neck. At a minimum.
What's a good way out, if any?
My knee-jerk answer is saying "avoid that rearchitecting the app." In alternative, grouping multiple rows in a single batch update step would alleviate the issue. In 1.x, the batch update size is hard-coded to 1. In version 2.0, there's a new property BatchUpdateSize you can use to fine-tune the whole process.
I hoped some hack was possible in 1.x to force a custom batch update size. My hope was for a private, internal property to set using reflection. No way. And in 2.0, the batch update implementation is significantly different and concatenates more statements into a SQL batch.
Have you tried with SQLXML updategrams? Any real-world feedback from large scale projects?