Deal with the many to many relation : Many to Many « Database ADO.net « C# / C Sharp

1. C# / C Sharp
2. Database ADO.net
3. Many to Many

Deal with the many to many relation

using System;
using System.Data;            // use ADO.NET namespace
using System.Data.SqlClient;  // use SQL Server provider

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

      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");
      Console.WriteLine(@"Successfully wrote XML output to file nwinddata.xml");

   }
}


           
       

Related examples in the same category