Csharp/C Sharp/Database ADO.net/DataRelation
More than one Relations
<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(@"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); DataSet thisDataSet = new DataSet(); SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection); custAdapter.Fill(thisDataSet, "Customers"); SqlDataAdapter orderAdapter = new SqlDataAdapter("SELECT * FROM Orders", thisConnection); orderAdapter.Fill(thisDataSet, "Orders"); SqlDataAdapter detailAdapter = new SqlDataAdapter("SELECT * FROM [Order Details]", thisConnection); detailAdapter.Fill(thisDataSet, "Order Details"); SqlDataAdapter prodAdapter = new SqlDataAdapter("SELECT * FROM Products", thisConnection); prodAdapter.Fill(thisDataSet, "Products"); DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]); DataRelation orderDetailRel = thisDataSet.Relations.Add("OrderDetail", thisDataSet.Tables["Orders"].Columns["OrderID"], thisDataSet.Tables["Order Details"].Columns["OrderID"]); DataRelation orderProductRel = thisDataSet.Relations.Add( "OrderProducts", thisDataSet.Tables["Products"].Columns["ProductID"], thisDataSet.Tables["Order Details"].Columns["ProductID"]); foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine("Customer ID: " + custRow["CustomerID"]); foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)) { Console.WriteLine("\tOrder ID: " + orderRow["OrderID"]); Console.WriteLine("\t\tOrder Date: " + orderRow["OrderDate"]); foreach (DataRow detailRow in orderRow.GetChildRows(orderDetailRel)) { Console.WriteLine("\t\tProduct: " + detailRow.GetParentRow(orderProductRel)["ProductName"]); Console.WriteLine("\t\tQuantity: " + detailRow["Quantity"]); } } } thisConnection.Close(); }
}
</source>
Setting the Nested property of a DataRelation to true
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class NestedXml {
public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText = "SELECT TOP 2 EmployeeID, CompanyName " + "FROM Employee " + "ORDER BY EmployeeID;" + "SELECT DeptID, EmployeeID, City " + "FROM Dept " + "WHERE EmployeeID IN (" + " SELECT TOP 2 EmployeeID " + " FROM Employee " + " ORDER BY EmployeeID " + ")"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close(); DataTable customersDT = myDataSet.Tables["Table"]; DataTable ordersDT = myDataSet.Tables["Table1"]; DataRelation customersDeptDataRel = new DataRelation( "EmployeeDept", customersDT.Columns["EmployeeID"], ordersDT.Columns["EmployeeID"] ); myDataSet.Relations.Add( customersDeptDataRel ); myDataSet.WriteXml("nonNestedXmlFile.xml"); myDataSet.Relations["EmployeeDept"].Nested = true; myDataSet.WriteXml("nestedXmlFile.xml"); }
}
</source>
Set up DataRelation
<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( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); 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"]); } } thisConnection.Close(); }
}
</source>