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,

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

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

Suggest the best practice to use with C# code.

  • Which SQL Server version please?
    – gbn
    Nov 14, 2011 at 6:28
  • SQL Server 2008 and above is what we use.
    – Saravanan
    Nov 14, 2011 at 7:17

1 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

  • I will check it and comeback to you. In the mean time, can you tell me if using a C# transaction as the outer transaction and a SQL transaction command like BEGIN TRAN t1;INSERT.....; INSERT... ; COMMIT TRAN T1; will work as i had this idea, just for knowing if this is a possibility.
    – Saravanan
    Nov 15, 2011 at 4:48
  • @saravanan: That should be a different question abut nesting of transactions. This questions is about bulk insert.
    – gbn
    Nov 15, 2011 at 8:09
  • I thought that i can use the BEGIN TRAN t1; Insert... ; before i came to know about the bulk insert. is that not a good choice.
    – Saravanan
    Nov 15, 2011 at 8:41
  • @saravanan: if you use a c# managed transaction, with a SQL inner one, you add complexity and problems. It is a separate question. You are limited in a bulk insert solution by your choice of "No stored procedures"
    – gbn
    Nov 15, 2011 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.