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: stackoverflow.com/questions/209686/…Maxim Zhukov– Maxim Zhukov2013-07-01 10:34:40 +00:00Commented Jul 1, 2013 at 10:34
-
stackoverflow.com/questions/12426300/…Karthik– Karthik2013-07-01 10:35:44 +00:00Commented Jul 1, 2013 at 10:35
Add a comment
|
2 Answers
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.
Comments
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.
