Doing a bulk insert in SQL Server

Question

I have to do the insert operation in the db as follows,

1. I create an item
2. I create 5-10 properties for that item, all these data i get in the form of a collection of objects.

I donot want to repeatedly hit the db with the following sequence, insert statement for item, insert statement for property1, insert statement for property2, ..., insert statement for property10.

Kindly note that i can use the C# code transaction for item and properties, i can even insert the item and all properties inside this transaction.

Is there any other way than using the above technique. I also cannot run Stored Procedures or create files with SQL statements as the number of properties is not known before hand.

Can i use the following kind of one,

Begin;
INSERT into item values(...........);
INSERT INTO item values (............);
GO;

Will this execute all the insert statements in a single transaction.

Suggest the best practice to use with C# code.

Answer 1

One approach is to use SQLBulkCopy class. This allows you to insert many rows in one go from some source DataTable or Reader