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 values ​​for column B in the array.

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

I'm better

Option 1.

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

This involves combining all the values ​​in the C # array into a single line with comma-delimited ones and passing the string to tsql as a parameter, then looping and breaking the string into separate values ​​and inserting a record for each of them, all inside a stored procedure.

From what I see, this will require easy rollback if necessary, but very awkward line processing in tsql.

Or

Option 2

Running a loop in C # and passing data in the form of sqlparams from a C # one record at a time to a stored process to insert each record.

I.e., foreach (int key in myarray) ... insert record

I could make this code in a dream, but how can I roll back if something happened in the middle of processing? And should I do a loop inside in one connection. Open and connection.close?

Anyone have other options for this?

Answer 1

this topic is widely covered here: Arrays and Lists in SQL 2005

Answer 2

The easiest way to implement this is to use parameter 1: pass the array as a delimited string. I used this in pre-sql2005 days in conjunction with this TSQL Split Function . I would pass an array using "|" as a separator.

Nowadays, I serialize an array into XML and then insert the contents into a table variable for processing using sp_xml_preparedocument .

I would not use option 2 as it makes several calls to the database.

Answer 3

Both options have their advantages (option 1 is one round, option 2 does not use hockey line processing), but I will most likely return to option 2. Option 1 suffers from varchars size varchars (8000 if you do not use varchar(MAX) , I have no idea that the performance will be on a varchar(MAX) comma-delimited string that are terribly long).

As for the rollback, yes, just do all the operations in one open connection and use the SqlTransaction object.

For example...

 using(SqlConnection conn = new SqlConnection("connection string")) { conn.Open(); using(SqlTransaction trans = conn.BeginTrasnaction()) { try { using(SqlCommand cmd = new SqlCommand("command text", conn, trans)) { SqlParameter aParam = new SqlParameter("a", SqlDbType.Int); SqlParameter bParam = new SqlParameter("b", SqlDbType.Int); cmd.Parameters.Add(aParam); cmd.Parameters.Add(bParam); aParam.Value = 1; foreach(int value in bValues) { bValue = value; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch { trans.Rollback(); throw; // so the exception can propogate up } } } 

Answer 4

Not sure if this is ideal for your situation, but many times when we need to pass an N-size data array into a stored procedure, we will use the temporary table trick. Something alone line:

 using (SqlConnection connection = new SqlConnection(connectionstring)) { connection.Open(); string sql = "CREATE TABLE #foo (myvalue [INT]) "; using (SqlCommand command = connection.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); // create the temp table foreach (int value in myValuesList) { command.CommandText = "INSERT INTO #foo ([myvalue]) VALUES (" + value + ") "; command.ExecuteNonQuery(); } command.CommandType = CommandType.StoredProcedure; command.CommandText = "StoredProcThatUsesFoo"; // fill in any other parameters command.ExecuteNonQuery(); } } 

Answer 5

If you want to do multiple inserts in a loop in C #, see TransactionScope. This will allow you to transfer multiple calls to a stored process into a transaction with rollback capabilities. Another option would be that you can pass your array as XML, and in a saved proc you can spoil this XML code into the temporary table used in your proc.

The last thing you need to do is add the Tabular Parameters to your wishlist to go to the next version of SQL Server. As this wish list grows, your excuse to spend money on updating becomes a little easier to do.