Home > DeveloperSection > Forums > Loop through ado.net sql select to add more rows to datatable
jayprakash sharma
jayprakash sharma

Total Post:117

Points:821
Posted on    June-19-2013 4:08 AM

 ADO.Net ADO.Net 
Ratings:


 1 Reply(s)
 1333  View(s)
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!


Sumit Kesarwani

Total Post:378

Points:2694
Posted on    June-19-2013 8:46 AM

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];

Don't want to miss updates? Please click the below button!

Follow MindStick