Thursday, April 07, 2005

ADO.NET Strongly Typed Relationships

1. Right-click your Project in the Solution Explorer and select Add > Add New Item...
2. Select "Data Set" from the Templates list, and type the name for your item (CustomersDataSet.cs).
3. In the Server Explorer, navigate to the Northwind database tables. Drag across the Customers and Orders tables.
4. Select the Customers table and select Schema > Add > New Relation... from the File Menu.
5. This is where your relationship is defined. Change the name to CustomersOrders, and change the Child element to Orders. Clicking OK will then create the relationship for you.

DataSet1.Tables["Customers"].Rows[0]

DataSet1.Customers[0]

CustomersDataSet.CustomerDataRow customer
    = myDataSet.Customers.FindCustomerByID(1);
if (customer != null)
{
  // Print out order numbers
  foreach (DataRow orderRow in
    customer.GetChildRows("CustomerOrders"))
  {
    Console.WriteLine(orderRow["OrderID"]);
  }
}

CustomersDataSet.CustomerDataRow customer
    = myDataSet.Customers.FindCustomerByID(1);
if (customer != null)
{
  // Print out order numbers
  foreach (CustomersDataSet.OrderRow orderRow
    in customer.GetOrdersRows())
  {
    Console.WriteLine(orderRow.OrderID);
  }
}

Comments

the man with no name	hey thanks for adding us to the links on your page! # Posted - 20/04/2005 06:54:00
joeldixon Posts: 169	No worries - you know that that little link will probably bring a large percentage of my 3 readers to your site # Posted - 20/04/2005 12:18:00
the man with no name	ha! you'd be surprised. perhaps only 3 people post comments but you can never really be sure how many people read. # Posted - 21/04/2005 09:16:00
UVc	Greetings, I am struggling with this in VS 2005. I decided to do as you did and google "GetOrdersRows()". When I call GetOrdersRows() I get an invalid cast exception. Here is my code: If I comment that out, it works fine. I dragged Customers and Orders onto the XSD designer, but did not add the relationship, because it was already there. I've tried it with the "Nested" checkbox either checked or unchecked (default), but no luck. Do you have any advice? Thanks! using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Text; using CustomersDataSetTableAdapters; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { CustomersDataSetTableAdapters.CustomersTableAdapter cta = new CustomersTableAdapter(); CustomersDataSet.CustomersDataTable cdt = cta.GetData(); StringBuilder sb = new StringBuilder(); cdt.WriteXml("c:\\temp\\output.xml", true); foreach (CustomersDataSet.CustomersRow customer in cdt) { sb.AppendFormat("Customer ID: {0}\n", customer.CustomerID); foreach (CustomersDataSet.OrdersRow order in customer.GetOrdersRows()) { sb.AppendFormat("Order: {0}\n", order.OrderID + " " + order.OrderDate); } } lblOutput.Text = sb.ToString(); } } # Posted - 17/09/2005 00:37:00
UVc	PS: I get the same thing in VS.NET 2003 too. # Posted - 17/09/2005 00:51:00