how to make DataSet return two related Tables?

Question

Hello everyone,

I am using C# + .Net 3.5 + VSTS 2008 + ADO.Net + SQL Server 2008. I have two related tables in SQL Server (foreign key relationship). And I want to load the two tables as two datatables in a dataset. Any reference code to do this?

thanks in advance, George

Answer 1

Try this

Dim myAdapter as SqlDataAdapter = new SqlDataAdapter(
      “SELECT * FROM Customers; SELECT * FROM Orders“, connection)

myAdapter.Fill(dsTables)
dsTables.Tables(0).TableName = “Customers“)
dsTables.Tables(1).TableName = “Orders“)

http://pietschsoft.com/post/2004/08/22/Fill-DataSet-with-multiple-Tables-and-update-them-with-DataAdapter.aspx

Answer 2

George,

Can you please clarify your question - is it about filling mulitple tables in dataset or specifically realted tables only.

As far loading multiple tables is concerned you can refer to following code (this code is avialable at MSDN http://msdn.microsoft.com/en-us/library/5fd1ahe2.aspx):

static void Main()
 {
   DataSet dataSet = new DataSet();

    DataTable customerTable = new DataTable();
    DataTable productTable = new DataTable();

    // This information is cosmetic, only.
    customerTable.TableName = "Customers";
    productTable.TableName = "Products";

    // Add the tables to the DataSet:
    dataSet.Tables.Add(customerTable);
    dataSet.Tables.Add(productTable);

    // Load the data into the existing DataSet. 
    DataTableReader reader = GetReader();
    dataSet.Load(reader, LoadOption.OverwriteChanges,
        customerTable, productTable);

    // Print out the contents of each table:
    foreach (DataTable table in dataSet.Tables)
       {
        PrintColumns(table);
        }

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
}

private static DataTable GetCustomers()
{
    // Create sample Customers table.
    DataTable table = new DataTable();
    table.TableName = "Customers";

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
    table.PrimaryKey = new DataColumn[] { idColumn };

    table.Rows.Add(new object[] { 0, "Mary" });
    table.Rows.Add(new object[] { 1, "Andy" });
    table.Rows.Add(new object[] { 2, "Peter" });
    table.AcceptChanges();
    return table;
}

private static DataTable GetProducts()
{
    // Create sample Products table.
    DataTable table = new DataTable();
    table.TableName = "Products";

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID",
        typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
        table.PrimaryKey = new DataColumn[] { idColumn };

    table.Rows.Add(new object[] { 0, "Wireless Network Card" });
    table.Rows.Add(new object[] { 1, "Hard Drive" });
    table.Rows.Add(new object[] { 2, "Monitor" });
    table.Rows.Add(new object[] { 3, "CPU" });
    table.AcceptChanges();
    return table;
}

private static void PrintColumns(DataTable table)
{
    Console.WriteLine();
    Console.WriteLine(table.TableName);
    Console.WriteLine("=========================");
    // Loop through all the rows in the table:
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < table.Columns.Count; i++)
        {
            Console.Write(row[i] + " ");
        }
        Console.WriteLine();
    }
}

private static DataTableReader GetReader()
{
    // Return a DataTableReader containing multiple
    // result sets, just for the sake of this demo.
    DataSet dataSet = new DataSet();
    dataSet.Tables.Add(GetCustomers());
    dataSet.Tables.Add(GetProducts());
    return dataSet.CreateDataReader();
}

Here is the code for managing parent child relation in data sets

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataRelation
  Inherits System.Web.UI.Page

  Protected lblDisplay As System.Web.UI.WebControls.Label

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim objConn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Dim dtrParent As DataRow
    Dim dtrChild As DataRow

    objConn = New SqlConnection(ConfigurationSettings.Appsettings("NorthwindConnection"))
    da = New SqlDataAdapter("SELECT * FROM Categories", objConn)
    ds = New DataSet()
    Try
      objConn.Open()
      da.Fill( ds,"Categories")
      da.SelectCommand = New SqlCommand("SELECT * FROM Products", objConn)
      da.Fill(ds, "Products")
    Catch exc As SqlException
      Response.Write(exc.ToString())
    Finally
      objConn.Dispose()
    End Try 
    'Create the Data Relationship
    ds.Relations.Add("Cat_Prod",ds.Tables("Categories").Columns("CategoryID"), _
                            ds.Tables("Products").Columns("CategoryID"))

    'Display the Category and Child Products Within
    For each dtrParent in ds.Tables("Categories").Rows
      lblDisplay.Text &= "<h3>" & dtrParent("CategoryName") & "</h3><ul>"
      For each dtrChild in dtrParent.GetChildRows("Cat_Prod")
        lblDisplay.Text &= "<li>" & dtrChild("ProductName") & "</li>"
      Next
      lblDisplay.Text &= "</ul>"
    Next
  End Sub

End Class

You can find further explanation over here

Answer 3

Here is a sample code on how to load a DataSet using LINQ queries Here 2 tables have got a relationship. "dc" is the data context.

            var query = dc.GetTable<Media>().Where(s => s.MediaID == new Guid("A72AA79A-6C40-4D6B-A826-241553FECDFE"));
            var query1 = dc.GetTable<MediaVersion>().Where(s => s.MediaID == new Guid("A72AA79A-6C40-4D6B-A826-241553FECDFE"));
            var query2 = dc.GetTable<RootPath>().Where(s => s.RootPathID == new Guid("62145B2C-BA36-4313-8CA2-0F224F8FE7E8"));


            SqlCommand cmd = dc.GetCommand(query) as SqlCommand;
            //Load first
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds, "Media");
            //Load second
            cmd = dc.GetCommand(query1) as SqlCommand;
            ada.SelectCommand = cmd;
            ada.Fill(ds, "MediaVersion");
            ds.Relations.Add("Med_MedVer", ds.Tables["Media"].Columns["MediaID"],
                       ds.Tables["MediaVersion"].Columns["MediaID"]);

            //Load third independent table
            cmd = dc.GetCommand(query2) as SqlCommand;
            ada.SelectCommand = cmd;
            ada.Fill(ds, "RootPath");