Load all tables of a database in a single dataset

Question

I am learning ADO.Net [C#] and want to fill different DataGridViews with the DataGridView.DataSource = DataSet.tables[] command. But i only know how to fill 1 table to 1 data adapter, is it possible to query the whole database and put all the tables in a single dataset so i can use the dataset.tables[table index] option to fill till the datagridview?

Example:

        OleDbConnection con = new OleDbConnection();
        con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;data source =.accdb";

        ds = new DataSet();
        da = new OleDbDataAdapter("SELECT * ", con);
        da.Fill(ds);
        dgv.DataSource = ds.Tables[0]; (i want to have all the tables as an index so that i can put this as the datasource of the datagridview).

Thanks in advance.

Solution

I believe you can tell the DataAdapter to return multiple result sets, by giving it multiple SELECT queries, separated by a semilcolon, like so:

da = new OleDbDataAdapter("SELECT * FROM Table1; SELECT * FROM Table2", con); 

In addition, you can fill a DataSet using multiple DataAdapters, like so:

daTable1 = new OleDbDataAdapter("SELECT * FROM Table1", con);
daTable2 = new OleDbDataAdapter("SELECT * FROM Table2", con);
daTable1.Fill(ds, "Table1");
daTable2.Fill(ds, "Table2");

You should then be able to access the tables in your DataSet via indexers. Specifically ds.Tables[int index] and ds.Tables[String name], like so:

dgv.DataSource = ds.Tables[0]; or dgv.DataSource = ds.Tables["Table1"];

For more info, see MSDN - Populating a DataSet from a DataAdapter (ADO.NET) (sepcifically Multiple Result Sets and Populating a DataSet from Multiple DataAdapters).