Passing an array of values to Parameterised SQL Stored procedure

Hi there,
I Hope somebody can help me out with this one.

I have a C# web app that I need to simply run a SQL SP in passing a series of values to one of my parameters.   This SP should not return any values, it just builds up a table that I'll then use an SQLDataReader to call another stored proc that retrieves the table values into a datagrid.

My question is how to I pass an array of set values into my 2nd parameter and loop through the stored procedure until the array is completed and then I'll call the second stored procedure to display the values in my datagrid.

The parameter that needs to change is called @Range

Here is my attemp at the code:-

public class ClubHandicaps
      {
            //This section calls the SP ClubHandicaps2 which iterates through the
            //SP once for each range thereby building up the range handicap table
            //The table is then displayed in the datagrid dgClubGrades

            public string MemberID;

THIS PART is the SP that just need to run so I think SqlDataReader is wrong here.

            public SqlDataReader getClubHandicaps(string MemberID)
            {
                  //Create The Connection String
                  SqlConnection myConnection = new SqlConnection(ConfigurationSettings.                     AppSettings["ConnectionString"]);
                  
                  //Set up an array of ranges
                  float[] rangeArray = new float[7]{300,500,600,700,800,900,1000};
                  
                  foreach (float Range in rangeArray)
                  {

                        //Create the SQL command
                        SqlCommand command = new SqlCommand("ClubHandicaps2", myConnection);
                        // Mark the Command as a SPROC
                        command.CommandType = CommandType.StoredProcedure;

                        //Set the input parameters
                        SqlParameter parameter1 = new SqlParameter("@MemberID", SqlDbType.Int);
                        parameter1.Value = MemberID;
                        command.Parameters.Add(parameter1);

                        SqlParameter parameter2 = new SqlParameter("@Range", SqlDbType.Float);
                        parameter2.Value = Range;
                        command.Parameters.Add(parameter2);

                  }

                  //The code above runs the stored proc for updating the xxRangeHandicap table
                  //This table now needs to be retrieved

                  //Create the SQL command

                  //SqlCommand command1 = new SqlCommand("GetClubHandicap", myConnection);
                  // Mark the Command as a SPROC
                  command1.CommandType = CommandType.StoredProcedure;

                  myConnection.Open();
                  SqlDataReader result = command.ExecuteReader(CommandBehavior.CloseConnection);
                  return result;

Thanks for your help

gjm762Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sumixCommented:

As you may know, stored procedures don't accept array values as parameters. The most common workarround is to create a string of values separated by commas and pass it as parameter. Here is an example of how can the SP look like:

 http://www.codeproject.com/database/Pass_Array_To_SP.asp

   You can built 'Range' parameter as shown below and call ExecuteNonQuery() method of SqlCommand object.

  string Range=rangeArray[0].ToString();
  for (int i=1;i<rangeArray.Length;i++)
     Range+=","+rangeArray[i].ToString();
 
0
gjm762Author Commented:
Dear sumix

Could you show me how to code this please

I know stored procedures don't accept array values as parameters but I'm not sure why I simply cant use an array in C# and then loop through code above each time changing the value in the range array so my table is built up
Thanks
0
sumixCommented:

Hi,
       Maybe I didn't understand well how do you want to build(update?) xxRangeHandicap table. In case this table exists and you want to call a SP that updates the table for each specific Range value you can adopt a solution like yours. In this case an efficient aproach is to create the SqlCommand object before the loop (and note that 'for' is faster than 'foreach' if you can use it):

         //Create the SQL command
         SqlCommand command = new SqlCommand("ClubHandicaps2", myConnection);
         // Mark the Command as a SPROC
          command.CommandType = CommandType.StoredProcedure;

          //Set the input parameters
          SqlParameter parameter1 = new SqlParameter("@MemberID", SqlDbType.Int);
          parameter1.Value = MemberID;
          command.Parameters.Add(parameter1);
          SqlParameter parameter2 = new SqlParameter("@Range", SqlDbType.Float);
         command.Parameters.Add(parameter2);

         for(int i=0;i<rangeArray.Length;i++)
               {
                    parameter2.Value = rangeArray[i];
                    myConnection.Open();
                   command.ExecuteNonQuery();
                   myConnection.Close();
               }

   ClubHandicaps2 can be a SP that will build xxRangeHandicap table only if it doesn't exists (which can happen for the first Range value).

 The second approach will call only once the SP that builds the table, passing it all the range values. The second parameter is of type NVarChar and the SP is called only once:
................
         SqlParameter parameter2 = new SqlParameter("@Range", SqlDbType.NVarChar,100);
         command.Parameters.Add(parameter2);
         
         string Range=rangeArray[0].ToString();
         for (int i=1;i<rangeArray.Length;i++)
              Range+=","+rangeArray[i].ToString();
         
         parameter2.Value = Range;
         myConnection.Open();
         command.ExecuteNonQuery();
         myConnection.Close();
         
 Hope this helps a bit
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

gjm762Author Commented:
Dear Sumix,
 I can see the code stepping through the array for each value however the SQL table only has one result in it which is that of the last aray value being 1000

I need the table to build up for each value in the array
Thanks
0
gjm762Author Commented:
Hi Again Sumix
I've figured it out...I was dropping the required table on each run of the stored procedure
 I'm now inserting the values into another table and all is cool

Thanks for all your help
0
sumixCommented:
Hi gjm,
 Glad you worked it out
All the best.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.