forum

Home / DeveloperSection / Forums / Loop through ado.net sql select to add more rows to datatable

Ask Question

Loop through ado.net sql select to add more rows to datatable

Anonymous User 2731 19-Jun-2013
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!
ado.net ado.net 
Updated on 19-Jun-2013
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

1 Answers

Sumit Kesarwani

Sumit Kesarwani

19-Jun-2013
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];

Liked By