This post originated from an RSS feed registered with .NET Buzz
by John Papa.
Original Post: Getting GrandParent columns with ADO.NET
Feed Title: John Papa
Feed URL: /error.htm?aspxerrorpath=/blogs/john.papa/rss.aspx
Feed Description: .NET Code Samples, Data Access, Patterns and Other Musings
I've recently had a flurry of email asking how to navigate up to a grandparent table using ADO.NET. Assuming that it is not preferable to export all of the data to Xml and use an XPath, ADO.NET offers a few other ADO.NET based solutions. One solution is to use expression based columns. (If you want more of an explanation of how to use expression based columns in ADO.NET, see my Data Points article here in MSDN Magazine.)
To explain both solutions, I created a DataSet that contains 3 DataTables. The DataTable objects represent Customers, Orders and Order Details from the SQL Server Northwind database. I also create a DataRelation between the Customers and its child table, Orders. Then I create another DataRelation between the Orders and its child table, Order Details. At the end of this code sample, shown below, I have a DataSet with Customers, their Orders and their Order Details linked through relations.
Filling the DataSet with Customers, Orders and Order Details
string sCn = "server=(local);database=northwind;integrated security=true;";
DataSet ds = new DataSet();
using (SqlConnection cn = new SqlConnection(sCn))
{
cn.Open();
// Get the Customersstring sqlCustomers = "SELECT CustomerID, CompanyName, Country "
+ " FROM Customers ORDER BY CustomerID";
using (SqlCommand cmd = new SqlCommand(sqlCustomers, cn))
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(ds, "Customers");
ds.Tables["Customers"].PrimaryKey =
new DataColumn[]{ds.Tables["Customers"].Columns["CustomerID"]};
// Get the Ordersstring sqlOrders = "SELECT OrderID, CustomerID, OrderDate FROM Orders";
using (SqlCommand cmd = new SqlCommand(sqlOrders, cn))
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(ds, "Orders");
ds.Tables["Orders"].PrimaryKey =
new DataColumn[]{ds.Tables["Orders"].Columns["OrderID"]};
ds.Relations.Add("C2O",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
// Get the Order Details
StringBuilder sb = new StringBuilder("");
sb.Append("SELECT od.OrderID, p.ProductID, p.ProductName, ");
sb.Append(" od.UnitPrice, od.Quantity");
sb.Append(" FROM [Order Details] od ");
sb.Append(" INNER JOIN Products p ON od.ProductID = p.ProductID");
string sqlOrderDetails = sb.ToString();
using (SqlCommand cmd = new SqlCommand(sqlOrderDetails, cn))
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(ds, "Order Details");
ds.Tables["Order Details"].PrimaryKey =
new DataColumn[]{ds.Tables["Order Details"].Columns["OrderID"],
ds.Tables["Order Details"].Columns["ProductID"]};
ds.Relations.Add("O2OD",
ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Order Details"].Columns["OrderID"]);
cn.Close();
}
The first solution I mentioned adds an expression column to the Orders and to the Order Details DataTable objects. The expression column in the Orders DataTable navigates up to its parent DataTable (Customers) via a DataRelation and simply gets the value of the Country column. The expression column in the Order Details DataTable navigates up to its parent DataTable (Orders) via a DataRelation and evaluates the expression column in the Orders DataTable to determine if the country is the US or not.
Grandparent via Expressions
// add an expression column, to help out
ds.Tables["Orders"].Columns.Add("CustomerCountry", typeof(string), "Parent.Country");
ds.Tables["Order Details"].Columns.Add("CountryType", typeof(string),
"Iif(Parent.CustomerCountry = 'US', 'US', 'Foreign Country')");
// Find an Order Details Row, just to start things off
DataRow row = ds.Tables["Order Details"].Rows.Find(new object[] {"10260", "57"});
// Look at the expression column
Debug.WriteLine(row["CountryType"]);
The second solution does not require expression columns. Instead, it uses the GetParentRow method of the DataRow. In this example, I evaluate the Customers.Country field by travelling up from the Order Details row to its parent Orders row and then up to its grandparent Customers row.
Grandparent via GetParentRow
// Use the GetParentRow to get the value insteadstring sCountry = row.GetParentRow("O2OD").GetParentRow("C2O")["Country"].ToString();
Debug.WriteLine((sCountry == "US") ? "US" : "Foreign Country");
Previous Topic
