Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Which SQL Server version please? –  gbn Nov 14 '11 at 6:28
    
SQL Server 2008 and above is what we use. –  saravanan Nov 14 '11 at 7:17
add comment

1 Answer

up vote 4 down vote accepted

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

share|improve this answer
    
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 '11 at 4:48
    
@saravanan: That should be a different question abut nesting of transactions. This questions is about bulk insert. –  gbn Nov 15 '11 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 '11 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 '11 at 9:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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