How to insert a list of values into SQL Server 2008 from asp.net using C#

Question

I want to store multiple rows at once in SQL Server. I have taken one list datatype which is storing multiple row content. Now I want to pass this list directly to SQL Server. I don't want to use for loop to insert value one by one. How can I pass the whole list as a parameter to my stored procedure?

Double: http://stackoverflow.com/questions/209686/passing-list-to-sql-stored-procedure — Maxim Zhukov, Jul 01 '13 at 10:34
http://stackoverflow.com/questions/12426300/how-to-insert-list-from-c-sharp-into-sql-server-2008 — Karthik, Jul 01 '13 at 10:35

score 0 · Answer 1 · answered Jul 01 '13 at 10:35

0

You can use a comma-separated string, and parse it on sql side. That is the fastest solution, so you will not have an open connection wile doing the loop on c# side.

answered Jul 01 '13 at 10:35

xmidPOE

111
1
4

score -1 · Answer 2 · answered Jul 01 '13 at 10:58

You can use the concept of Batch Update or SQLBulkCopy or you can pass Array as Parameter to Command Object.

You can also Pass the data in XML format to SQL Server Stored Procedure .

Or Simplest way create multiple appended strings for inserts as below:

 string sqlText = "INSERT INTO Person VALUES ( '" + txtPersonId1.Text.Trim() + "',' " +  txtPersonName1.Text + "'); "; 
    sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId2.Text.Trim() + "',' " +  txtPersonName2.Text + "'); "; 
    sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId3.Text.Trim() + "',' " +  txtPersonName3.Text + "'); "; 
    sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId4.Text.Trim() + "',' " +  txtPersonName4.Text + "'); ";

All These Methods are explained over here.

How to insert a list of values into SQL Server 2008 from asp.net using C#

2 Answers2