Contiously adding to datatable

Question

I have the code below that goes out to a database using OleDB and fills a dataset with the information. It then checks to make sure it has something in the dataset, if it does it then addes that dataset to a datatable that is then returned at the end of the function.

for (int i = 0; i < UserClassDict[UserName].ControlNumber.Count; i++)
                    {
                        string query = "SELECT * FROM [FNF Taxes] WHERE ControlNumber =" + UserClassDict[UserName].ControlNumber[i] + ";";
                        adapter.SelectCommand = new OleDbCommand(query, conn);
                        DataSet dataset = new DataSet();
                        adapter.Fill(dataset);

                        if (dataset.Tables[0].Rows.Count > 0)
                        {
                            dt = dataset.Tables[0];
                                                }
                    }

My question is this. As you can tell by the for loop that this block is in, it will run numerous times and I would like each of those rows that it pulls from the DB to be added to the datatable. So that when the datatable is returned it has all X amount of rows. However, as it is in the above code-block, the only row that is added to the datatable is the most recent row that was pulled.

Answer 1

Use DataTable.Merge():

DataTable dt = null;
for (int i = 0; i < UserClassDict[UserName].ControlNumber.Count; i++)
{
    string query = "SELECT * FROM [FNF Taxes] WHERE ControlNumber =" + UserClassDict[UserName].ControlNumber[i] + ";";
    adapter.SelectCommand = new OleDbCommand(query, conn);
    DataSet dataset = new DataSet();
    adapter.Fill(dataset);

    if (dataset.Tables[0].Rows.Count > 0)
    {
        if (dt == null)
            dt = dataset.Tables[0].Clone();
        dt.Merge(dataset.Tables[0]);
    }
}
return dt;