Csharp/CSharp Tutorial/ADO.Net/DataRelation

DataRelation Example

using System;
using System.Data;            
using System.Data.SqlClient;  
using System.Collections.Generic;
using System.Text;
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection thisConnection = new SqlConnection(
                @"Data Source=.\SQLEXPRESS;" +
                @"AttachDbFilename="NORTHWND.MDF";" +
                @"Integrated Security=True;Connect Timeout=30;User Instance=true");
      SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection);
      SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
      DataSet thisDataSet = new DataSet();
      SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection);
      SqlDataAdapter orderAdapter = new SqlDataAdapter("SELECT * FROM Orders", thisConnection);
      custAdapter.Fill(thisDataSet, "Customers");
      orderAdapter.Fill(thisDataSet, "Orders");
      DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
         thisDataSet.Tables["Customers"].Columns["CustomerID"],
         thisDataSet.Tables["Orders"].Columns["CustomerID"]);
      foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
      {
        Console.WriteLine("Customer ID: " + custRow["CustomerID"] +
                  " Name: " + custRow["CompanyName"]);
        foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
        {
          Console.WriteLine("  Order ID: " + orderRow["OrderID"]);
        }
      }
            custOrderRel.Nested = true;
            thisDataSet.WriteXml("nwinddata.xml");
            thisConnection.Close();
    }
  }

Define DataRelation

using System;
using System.Data;
using System.Data.SqlClient;
public class XmlRelations
{
    private static string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
    private static string categorySQL = "SELECT CategoryID, CategoryName,Description FROM Categories";
    private static string productSQL = "SELECT ProductID, ProductName, CategoryID FROM Products";
    public static void Main() 
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand(categorySQL, con);
        SqlDataAdapter adapter = new SqlDataAdapter(com);
        DataSet ds = new DataSet("Nortwind");
        con.Open();
        adapter.FillSchema(ds, SchemaType.Mapped, "Categories");
        adapter.Fill(ds, "Categories");
        adapter.SelectCommand.rumandText = productSQL;
        adapter.FillSchema(ds, SchemaType.Mapped, "Products");
        adapter.Fill(ds, "Products");
        con.Close();
        DataColumn parentCol = ds.Tables["Categories"].Columns["CategoryID"];
        DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"];
        DataRelation relation = new DataRelation("Cat_Prod", parentCol, childCol);
        ds.Relations.Add(relation);
        relation.Nested = true;
        ds.WriteXml("mydata.xml");
        ds.WriteXml(Console.Out);
    }
}

Navigate a many-to-many relationship.

using System;
using System.Data;
using System.Data.SqlClient;
public class AddCustomer
{
  public static void Main() 
  {
    string connectionString = "Data Source=localhost;Initial Catalog=pubs;Integrated Security=SSPI";
    string SQL = "SELECT au_lname, au_fname, au_id FROM Authors";
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(SQL, con);
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    con.Open();
    adapter.Fill(ds, "Authors");
    cmd.rumandText = "SELECT au_id, title_id FROM TitleAuthor";
    adapter.Fill(ds, "TitleAuthor");
    cmd.rumandText = "SELECT title_id, title FROM Titles";
    adapter.Fill(ds, "Titles");
    con.Close();
    DataRelation titles_titleAuthor = new DataRelation("", ds.Tables["Titles"].Columns["title_id"], ds.Tables["TitleAuthor"].Columns["title_id"]);
    DataRelation authors_titleAuthor = new DataRelation("", ds.Tables["Authors"].Columns["au_id"], ds.Tables["TitleAuthor"].Columns["au_id"]);
    ds.Relations.Add(titles_titleAuthor);
    ds.Relations.Add(authors_titleAuthor);
    foreach (DataRow rowAuthor in ds.Tables["Authors"].Rows)
    {
        Console.WriteLine(rowAuthor["au_fname"]);
        Console.WriteLine(rowAuthor["au_lname"]);
        foreach (DataRow rowTitleAuthor in rowAuthor.GetChildRows(authors_titleAuthor))
        {
            foreach (DataRow rowTitle in rowTitleAuthor.GetParentRows(titles_titleAuthor))
            {
                Console.WriteLine(rowTitle["title"]);
            }
        }
    }
  }
}