How to add multiple Datasets to Single DataTable?

Question

I have below code, where in I am creating a dataset based on a query to Oracle mdb.

....

**OracleDataAdapter adapter = new OracleDataAdapter(sqlstr, conn);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
DataSet dataset = new DataSet();
adapter.Fill(dataset);**


DataTable dataTable = dataset.Tables[0];

....

I would like to have the highlighted code in a loop, and then add the dataset objects to a common datatable outside the loop.

Do I need to use datatable.Merge()? The resultset in 'n' datasets need to be combined into a single DataTable, hence I am not sure Merge is the right way.

How can this be implemented?

Thanks

DataSet is a container for DataTable objects, not the other way around. DataAdapters can fill DataTable objects, it seems you don't actually need a dataset. — Mithrandir, May 02 '14 at 10:11
possible duplicate of [Merge 2 DataTables and store in a new one](http://stackoverflow.com/questions/285474/merge-2-datatables-and-store-in-a-new-one) — Mithrandir, May 02 '14 at 10:12

sr28 · Answer 1 · 2014-05-02T14:35:48.500

Presumably you are creating a new 'sqlstr' for each query to the Oracle mdb? If so you can create a list to put all your sqlstr into and then have a for loop to cycle through them like this:

List<string> sqlstrings = new List<string>();

//add your sqlstr's here

DataSet dataset = new DataSet();

for(int i = 0; i < sqlstrings.count; i++)
{
    OracleDataAdapter adapter = new OracleDataAdapter(sqlstrings[i].ToString(), conn);
    OracleCommandBuilder builder = new OracleCommandBuilder(adapter);

    adapter.Fill(dataset.Tables[i]);
}

This will get all of your queries into 1 dataset with multiple tables. You can then create a data relation between all the tables as per here: http://msdn.microsoft.com/en-us/library/ay82azad(v=vs.110).aspx.

However, if you want to create just 1 DataTable then you can use 'Merge' as per the link supplied by Mithrandir. Of course, you would need to add this into the loop above and account for the first run (you can't merge table 1 with nothing). Create a new DataTable before the loop and do something like:

if(i > 0)
{
    dtBigTable.Merge(dataset.Tables[i]);
}
else
{
    //First table
    dtBigTables = dataset.Tables[0]
}

Note: this is untested code. It's just to give you an idea and something to play with.

How to add multiple Datasets to Single DataTable?

1 Answers1