Elegant way to get multiple business objects into a DB (in one go) without RBAR in .Net?

I have a collection of populated custom business objects in my app which I'm going to insert into a SQL 2005 DB using a stored procedure. The collection size isn't massive, maybe 20-30 objects.

Is there an elegant way to insert all of these objects into the DB in one go without doing something like the开发者_C百科 following?:

• Open DB conn
• For each item in collection ...
• Set parameter values of SP from item.properties for SQLCmd
• SQLCmd.ExecuteNonQuery
• Clear parameters
• Next
• Close DB conn

---

Table valued paramaters if you are using Sql 2008.

http://www.sommarskog.se/arrays-in-sql-2008.html

---

You could serialise all your objects into a text format JSON or whatever, pass the string to a SPROC with one input parameter, then de-serialise the objects and iterate them within the SPROC.

You'll still have to do individual inserts but it's one operation from your application.

---

Use a stored proc to get all resultsets in one go?

---

You could programatically create a dynamic sql query to generate a large insert statement and execute that in one go, or do as you're currently doing. Your way is probably best as you can deal with errors better should one of the inserts fail.