Since there is no Sqlserver array parameter, what's the best way to proceed?

Question

I need to create multiple records in sqlserver, each with the same value in column A, but with a unique value in column B. I have the values for column B in an array.

I am using VS2008, aspnet, c# 3.5, sqlserver 2005.

Am I better off

Option 1.

Making 1 call to a stored procedure in sqlserver from c# code, and then doing all the processing work in the stored procedure in tsql?

This would involve combining all the values in the c# array into one comma delimited string and passing the string to tsql as a parameter, then looping and breaking the string apart into individual values and inserting a record for each one, all within a stored procedure.

From what I can see, this would involve easy rollback if necessary, but very clumsy string processing in tsql.

Or

Option 2.

Doing the looping in c# and passing the data as sqlparams from c# one record at a time to a stored proc to insert each record.

Ie, foreach ( int key in myarray) … insert a record

I could do this code in my sleep, but how would I be able to rollback if something happened in the middle of processing? And should I do the looping within in a singe connection.open and connection.close?

Anyone have any other options for doing this?

Answer 1

this topic is extensively covered here: Arrays and lists in SQL 2005