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

Answer 2

Both options have their advantages (option 1 is a single round-trip, option 2 doesn't use hokey string processing), but I would likely end up going with option 2. Option 1 suffers from the size limits of varchars (8000 unless you use varchar(MAX); I have no idea what the performance would be on a comma-delimited varchar(MAX) string that's terribly long).

As far as rollback, yes, just do all of the operations on a single open connection and use a 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 3

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

These days, I serialize the array into XML and then insert the contents into a table variable for processing using the sp_xml_preparedocument stored procedure.

I wouldn't use option 2 since it makes multiple calls to database.

Answer 4

Not sure if this fits your situation perfectly, but many times, when we need to pass an N-sized array of data into a stored procedure, we'll use a temp-table trick. Something alone the lines of:

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're wanting to do multiple inserts in a loop in C# - look at TransactionScope. That will let you roll multiple calls to the stored proc into a transaction with rollback capabilities. Another option would be that you could pass your array as XML, and in the stored proc you could shred that XML out to a temp table to use in your proc.

One last thing you should do is to add Table Valued Parameters to your wish-list of reasons to upgrade to the next version of SQL server. As that wish-list grows, your justification for spending the money to upgrade gets a bit easier to make.