Best Way to Insert Multiple Rows (ADO.NET)

Question

I'm adding features to an existing ASP.NET project. The code calls a stored procedure to add a row to a particular table. I need the option of also allowing one of the columns to have multiple values, in which case a row would be added for each value in that column.

I know I can call my insert method once for each row, but that sounds horribly inefficient. I know I could write multiple lines, delimited by semi-colons, and send them to the database in one request. But the existing code calls a stored procedure and so multiple insert statements would require I modify the heck of the existing code.

Note that the multiple values will be stored as multiple lines in a textbox, one line per value, and must obviously checked for being correctly entered.

Is there a simpler way to approach this?

Answer 1

SQL Server 2008 has the "table type" parameters that allows multiple rows as parameters. An example is defined in this SO question

SQL Server 2005+ has good XML handling. We use this currently for small datasets. SQL Server 2000 XML handling isn't as nice.

For all versions, you can create a temp table then call a stored proc that uses this table. You can use SQLBulkCopy to load the table. Useful for many rows.

Generally, the problem is a common one. Erland Sommarskog has an article "Arrays and Lists in SQL Server 2005 and Beyond" which is (one of) the definitive articles on the subject (he has more).

Summary:

• table type (SQL Server 2008+)
• XML (easier with SQL Server 2005+)
• temp table (all versions)