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