How to efficiently save objects to a SQL 2000 database

Question

In the past, whenever my application needed to insert many records into a SQL 2000 db, I would call a stored procedure, once for each record. When inserting many records, I found that performance suffered, particularly when with a fat client, calling a web service to perform the database call.

Then I learned that if I passed XML data representing many records, that SQL 2000 could save that to a temp table and I could efficiently process the records in the XML table using a single SQL that operated across all the rows of the temp table simultaneously. This reduced the number of calls to the SP to one and also was more efficient because the SQL update commands worked on multiple record simultaneously.

I personally like using a Business Objects (BO) layer and am not experienced with LINQ or Entity Framework, so for the moment, let's assume that these tools are out of scope...

In my BO layer, I will typically add a "ToDataset" function to an object to convert the object to a dataset with one table in it, where that table contains one data row. It is the datarow itself that contains the representation of the data in the object.

Then, I will have a collection class, which is a collection of the previous object, for example. It too, will have a "ToDataset" method to create a representation of all of the objects in the collection class. This function will loop through the objects in the collection, and for each, call the objects "ToDataset" function and merge this information into an "accumulating" dataset.

When I want to pass all of the records in the collection class to a SP, I would call the Collection class's ToDataset method and pass the XML for the ds to the SP.

To keep the size of this post down, I'm going to try to refrain from further justifying this approach and open myself up to your suggestions.

How do good developers do stuff like this?

In the following code sample you'll see how I try to define my objects so that they can be nested but that when you call the Todataset function for a parent object, all of the child objects are rolled into it using a pattern defined by the base class. (The code isn't that long, maybe I should post it. If I get flamed, I'll post it)

I expect some horrified reactions. Be kind. :-)

Thanks for your interest in reading this to the end.

http://sites.google.com/site/dokmanc/Home/ObjectToDataset.zip?attredirects=0

Answer 1

Absolutely awesome strategy. Don't change a thing.

Answer 2

Have you looked at the SqlBulkCopy Class?

BTW, I only just saw this question. However, I still might have hesitated to answer it, or even to read very much of it. Once you'd explained you were sending XML in order to load data efficiently, you had pretty much said all you had to say in order to get the question answered.