Populate a DataSet object with multiple DataTable objects

   

using System;
using System.Data;
using System.Data.SqlClient;

class MultipleDataTables2
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText =
      "SELECT TOP 2 ID, FirstName, LastName " +
      "FROM Employee " +
      "ORDER BY ID";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Employee");
    Console.WriteLine("numberOfRows = " + numberOfRows);

    mySqlDataAdapter.SelectCommand.CommandText =
      "SELECT ID, Name " +
      "FROM Customers " +
      "WHERE ID = '001'";
    numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Customers");
    Console.WriteLine("numberOfRows = " + numberOfRows);

    mySqlConnection.Close();

    foreach (DataTable myDataTable in myDataSet.Tables) {
      Console.WriteLine("
Reading from the " +
        myDataTable.TableName + "DataTable");
      foreach (DataRow myDataRow in myDataTable.Rows)
      {
        foreach (DataColumn myDataColumn in myDataTable.Columns)
        {
          Console.WriteLine(myDataColumn + "= " +
            myDataRow[myDataColumn]);
        }
      }
    }
  }
}