Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing an array of values to Parameterised SQL Stored procedure

Posted on 2004-11-13
6
Medium Priority
?
277 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:gjm762
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:sumix
ID: 12577369

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
 

Author Comment

by:gjm762
ID: 12599465
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
 
LVL 12

Accepted Solution

by:
sumix earned 250 total points
ID: 12601589

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:gjm762
ID: 12652023
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
 

Author Comment

by:gjm762
ID: 12652119
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
 
LVL 12

Expert Comment

by:sumix
ID: 12652201
Hi gjm,
 Glad you worked it out
All the best.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question