Geek Answers Handbook

Best way to insert multiple lines (ADO.NET)

I am adding functions to an existing ASP.NET project. The code calls a stored procedure to add a row to a specific table. I need an option that allows one of the columns to have multiple values, in which case a row will be added for each value in this column.

I know that I can call my insert method once for each line, but that sounds terribly inefficient. I know that I can write several lines separated by semi-colonies and send them to the database with one request. But existing code calls a stored procedure, and so for several insert statements, you need to change the value of the existing code.

Please note that several values ​​will be stored as several lines in a text field, one line for each value and, obviously, should be checked for correct input.

Is there an easier way to approach this?

+5
source share
4 answers

SQL Server 2008 has "table type" parameters that allow you to use multiple rows as parameters. An example is provided in this SO question.

SQL Server 2005+ has good XML processing. We are currently using this for small data sets. SQL Server 2000 XML processing is not so good.

, , . SQLBulkCopy . .

, . Erland Sommarskog "" SQL Server 2005 Beyond", ( ) ( ).

:

  • (SQL Server 2008 +)
  • XML ( SQL Server 2005 +)
  • temp ( )
+2

- :

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

.

SQL Server 2008, :

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES('First' ,1)
VALUES('Second' ,2)
VALUES('Third' ,3)
VALUES('Fourth' ,4)

, ...

0

I sent a delimited string as one of the arguments to the stored procedure, and then parsed each item from the string and added a string for each of them.

I’m not sure if this is the best way to accomplish this, but it doesn’t require much processing of the existing code, and it seems to be efficient enough for me.

0
source

SqlBulkCopy opens, this will allow you to transfer your Datatable directly to the database.

0
source

More articles:


All Articles