Mega Code Archive

 

Categories / C# / ADO Database

 

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");    } }