Can I save object graph using SqlBulkCopy?

Can I save object graph using SqlBulkCopy?

Old forum URL: forums.lhotka.net/forums/t/7739.aspx


reagan123 posted on Tuesday, October 06, 2009

Hello,
We currently have to be able to serialize our business base objects to a file and then later deserialize them and save them into a database.

Currently, after we deserialize the objects we just loop all the objects calling save on them which works fine.  The issue is that sometimes we have a lot of objects (parent-->child-->grandchild) and it takes a bit of time to do this.  I was wondering if I could use SqlBulkCopy to just put all of the objects into the database at once?  I've never used SqlBulkCopy and after doing some reading i'm not sure how we would use it with CSLA objects.

Can anyone tell me if this can be done or if they have any suggestions?

Thanks as always.


DancesWithBamboo replied on Wednesday, October 07, 2009

You would have to do a different SqlBulkCopy object for each table you were interested in.  If your objects match the db closely that may be fairly easy.  But it only works for inserts though.  No delete and no update.

It doesn't sound like you have bazzilions of records so I would recommend using a batch update where you just seperate the sql statements by semi-colons and run them all at once.  The root business object creates a StringBuilder and then each of the children/grandchildren/etc adds on statements during their update/insert/delete calls instead of actually doing the db update.  Then the root BO executes the SQL after it is all built up by the children/grandchildren/etc.

reagan123 replied on Wednesday, October 07, 2009

Thanks for the reply. 

In this circumstance my objects do reflect the database, so that should not be too terrible.  Inserts are the only thing i'm looking to do so this still may be an option.  I'm still not 100% sure how I'd use SqlBulkCopy with my object hierarchy.

The StringBuilder concept sounds like a great possibility.  I'm going to look into that now to see if it is an option as well.

Thanks for the help.




JoeFallon1 replied on Wednesday, October 07, 2009

"We currently have to be able to serialize our business base objects to a file and then later deserialize them and save them into a database."

This could be a dangerous practice depending on how long they stay in the file. If we are talking minutes then it should be OK. If you are talking days then you could have a big problem on your hands when the client upgrades. The change in the app (or .Net) will cause you to be unable to deserialize the BOs out of the file due to incompatible version.

Just something to be aware of.

Joe


 

rasupit replied on Wednesday, October 07, 2009

Reagan123,
SqlBulkCopy will not be able to address what you need.  You're basically need batch sql commands and execute them at once.

I can see two methods to do this: First, you can concatenate your sql statements then execute them at once at the parent level.  Or, you can take advantage the System.Data.SqlClient.SqlCommandSet which unfortunately was made internal by MS and only available internally for them ex: DataSet.

You can find more information about it at http://ayende.com/Blog/archive/2006/09/13/OpeningUpQueryBatching.aspx and the code can be seen in http://github.com/ayende/rhino-etl/blob/master/Rhino.Etl.Core/Infrastructure/SqlCommandSet.cs

HTH,
Ricky

reagan123 replied on Wednesday, October 07, 2009

Thanks everyone.  I really appreciate the feedback.  Sounds like I have some reading to do. :)

Thanks again as always!

dlambert replied on Thursday, October 08, 2009

If you're on SQL 2008 and can use a stored proc for the insert, you might also want to look at table-value parameters, which let you pass in a hunk of data as a table variable -- you'd be able to push the whole set of data in and then let the proc deal with it from there.

Copyright (c) Marimer LLC