Csharp/CSharp Tutorial/ADO.Net/DataRelation

DataRelation Example

<source lang="csharp">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();
   }
 }</source>

Define DataRelation

<source lang="csharp">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);
   }

}</source>

Navigate a many-to-many relationship.

<source lang="csharp">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"]);
           }
       }
   }
 }

}</source>