CODEWITHSUNDEEP
c#arrayssql-serverstored-procedures
MaazKhan47
MaazKhan47

Reputation: 849

Run SQL Stored Procedure for every value in an array

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 ?

Upvotes: 3

Views: 703

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

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

Upvotes: 2

Related Questions