LOOP THROUGH ADO.NET SQL SELECT TO ADD MORE ROWS TO DATATABLE

jayprakash sharma

Total Post:117

Points:821
Posted by  jayprakash sharma
 1630  View(s)
Ratings:
Rate this:
Hi Expert,

I currently have a working query for the first element in the array of ID's as seen below. What I need to do is add a for loop so I rerun the query for every element in the array and add each new row to the datatable but I am not sure how I do this? Unless there is a way I can include all ID's of my array in the where clause so I retrieve all rows through first run.

PID[] is a string array and could have anywhere from 1 to 5 elements that are random ID's.

Any help would be appreciated!

        for loop here?

        string firstQuery = "select * from Property p " + "where p.id in (@pID)";
                              connString.Open();

        SqlCommand selectAll = new SqlCommand(firstQuery, connString);
        selectAll.Parameters.AddWithValue("@pID", PID[0]);
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = selectAll;
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        connString.Close();
        DataTable table = ds.Tables[0];


Any help would be amazing thank you!
  1. Sumit Kesarwani

    Post:378

    Points:2694
    Re: Loop through ado.net sql select to add more rows to datatable

    Hi Jayprakash,


    Yes you can include all ids in one parameter and get results that match them:

    var parameters = new string[PID.Length];
    var selectAll = new SqlCommand();

    for (int i = 0; i < PID.Length; i++)
    {
        parameters[i] = string.Format("@Age{0}", i);
        selectAll .Parameters.AddWithValue(parameters[i], PID[i]);
    }

    selectAll.CommandText = string.Format("SELECT * from Property p WHERE p.id IN ({0})", string.Join(", ", parameters));
    selectAll.Connection = connString;

    connString.Open();

    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = selectAll;
    DataSet ds = new DataSet();
    adapter.Fill(ds);

    connString.Close();
    DataTable table = ds.Tables[0];

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!