Run SQL Stored Procedure for every value in an array

Question

public DataSet DataforTable(string fundID, string fromDate, string toDate)
{
    string[] fundList = fundID.Split(',');
    DataSet full = new DataSet();
    DataTable ds = new DataTable();
    full.Tables.Add(ds);
    foreach (var fund in fundList)
    {        
        using (strCon)
        {
            SqlCommand cmd = new SqlCommand("[dbo].[z_Formulas2]", strCon);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            cmd.Parameters.Add(new SqlParameter("@Fund_ID", fund));
            cmd.Parameters.Add(new SqlParameter("@XFund_ID", ""));
            cmd.Parameters.Add(new SqlParameter("@Start_Dated", fromDate));
            cmd.Parameters.Add(new SqlParameter("End_Dated", toDate));

            sda.Fill(ds);
        }
    }
    return full;
}

I want to run my SP for every value in fund(array). Should I be doing this in SP by passing the array in SP or should this be done in c#? If in c# How ? I tried, it runs for first value then for 2nd value of loop it gives error:

The ConnectionString property has not been initialized

(code is given below) If in SP How ?

You don't want to be making multiple database calls if you can help it, so you should be doing whatever you need to do in the SP and pass it the values in the array. — sr28, Aug 17 '16 at 11:17
You can use [CUSTOM TYPE](https://msdn.microsoft.com/en-us/library/ms175007.aspx) Then you can send array to sp. No need to call sp for each value. — NEER, Aug 17 '16 at 11:19
if you want to whole array in store procedure then you can pass string fundID with comma separate . after that In store procedure, split this comma separate string. http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure — user2960398, Aug 17 '16 at 11:31
@MaazKhan47 - To being able to assist with that you will need to show the stored procedure. However, that is already a follow-up question from the answer below. Please look online for examples and try to solve the new problem and only after that, if not successful, post a new question :) — Gilad Green, Aug 17 '16 at 11:53

score 2 · Answer 1 · edited May 23 '17 at 10:33

The problem is with where you places using (strCon) - because it is in the loop then after the first time the strCon will be disposed and then you won't have an open connection to the database to use.

Move the using to be above the foreach:

using (strCon)
{        
    foreach (var fund in fundList)
    {
        SqlCommand cmd = new SqlCommand("[dbo].[z_Formulas2]", strCon);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter sda = new SqlDataAdapter(cmd);

        cmd.Parameters.Add(new SqlParameter("@Fund_ID", fund));
        cmd.Parameters.Add(new SqlParameter("@XFund_ID", ""));
        cmd.Parameters.Add(new SqlParameter("@Start_Dated", fromDate));
        cmd.Parameters.Add(new SqlParameter("End_Dated", toDate));

        sda.Fill(ds);
    }
}

An alternative to the current way that you do it is to pass the array to the SP as a parameter - and so to save all these back and forth to the database. To do so check this question

Yes Sure I would like to do this in that way by passing it to SP But How ? Explanation with any example would be greatly appreciable — MaazKhan47, Aug 17 '16 at 11:21
Yes, but in my case I need to create a cursor or use while loop. — MaazKhan47, Aug 17 '16 at 11:48

Run SQL Stored Procedure for every value in an array

1 Answers1