Output data from DataTables within DataSet

Question

I currently accessing a database using multiple queries and storing the results from the queries into DatatTables. I decided since I'm using multiple DataTables to store them inside a DataSet.

I can see that data is inside the DataTables when I print them out. However when I try to access them from the DataSet to print out the data, I get nothing back; it is empty.

string querytest1  = "SELECT * FROM test1";
string querytest2  = "SELECT * FROM test2";
string querytest3  = "SELECT * FROM test3";

using(OleDbConnection connection = new OleDbConnection(connectionString)){

     OleDbCommand commandtest1 = new OleDbCommand(querytest1 , connection);
     OleDbCommand commandtest2 = new OleDbCommand(querytest2 , connection);
     OleDbCommand commandtest3  = new OleDbCommand(querytest3 , connection);

     connection.Open();

     DataSet dataSet = new DataSet();

     OleDbDataAdapter dataAdaptertest1  = new OleDbDataAdapter(commandResults);
     OleDbDataAdapter dataAdaptertest2 = new OleDbDataAdapter(commandResults);
     OleDbDataAdapter dataAdaptertest3  = new OleDbDataAdapter(commandProjects);

      DataTable dataTabletest1  = new DataTable();
      DataTable dataTabletest2 = new DataTable();
      DataTable dataTabletest3  = new DataTable();

       dataAdaptertest1.Fill(dataTabletest1 );
       dataAdaptertest2.Fill(dataTabletest2 );
       dataAdaptertest3.Fill(dataTabletest3 );

       dataTabletest1  = dataSet.Tables.Add("test1 ");
       dataTabletest2  = dataSet.Tables.Add("test2 ");
       dataTabletest3  = dataSet.Tables.Add("test3 ");

        Console.WriteLine("DataSet has {0} DataTables \n", dataSet.Tables.Count);

        foreach (DataTable objDt in dataSet.Tables)
                Console.WriteLine("{0}", objDt.TableName);

        return dataSet;

Expected behaviour: is to be able to access a DataTable from the DataSet and print out the data.

Example: Print out the contents in Test1

The code is redundant. I just don't know a better way to do this.

Why do you need a dataset for this? You can just create an array of table names ["test1","test2","test3"] and then open connection and use a loop to get the results like OledbAdapter oa = new OledbAdapter(command) and Datatable tb = new Datatable(); oa.Fill(tb); print(tb) write a print function which loops through columns and displays it to the user. — Nilesh

Steve Steve · Accepted Answer · 2019-08-29T22:27:21

You don't need three adapters to fill a DataSet with the results from three commands.

You can simply write:

string query1 = "SELECT * FROM test1";
string query2 = "SELECT * FROM test2";
string query3 = "SELECT * FROM test3";
DataSet dataSet = new DataSet();

using(OleDbConnection connection = new OleDbConnection(connectionString)){

     connection.Open();

     OleDbCommand cmd = new OleDbCommand(q1, connection);
     OleDbDataAdapter da  = new OleDbDataAdapter(cmd);
     da.Fill(ds, "Test1");
     cmd.CommandText = q2;
     da  = new OleDbDataAdapter(cmd);
     da.Fill(ds, "Test2");
     cmd.CommandText = q3;
     da  = new OleDbDataAdapter(cmd);
     da.Fill(ds, "Test3");
}
Console.WriteLine("DataSet has {0} DataTables \n", ds.Tables.Count);
    foreach (DataTable objDt in ds.Tables)
            Console.WriteLine("{0}", objDt.TableName);

The trick is filling the same DataSet three times instead of three different DataTables and giving at each fill a different name for each table created.

Output data from DataTables within DataSet

2 Answers