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


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.


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?


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


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.


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          }      }  }  


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();     }  }  


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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »