Getting GrandParent columns with ADO.NET

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.

<fieldset style="LEFT: 10px; TOP: 1320px"><legend>Filling the DataSet with Customers, Orders and Order Details</legend><pre>string sCn = “server=(local);database=northwind;integrated security=true;”; DataSet ds = new DataSet(); using (SqlConnection cn = new SqlConnection(sCn)) { cn.Open(); // Get the Customers string 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 Orders string 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(); }</pre></fieldset>

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.

<fieldset style="LEFT: 10px; TOP: 1320px"><legend>Grandparent via Expressions</legend><pre>// 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”]); </pre></fieldset>
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. <fieldset style="LEFT: 10px; TOP: 1320px"><legend>Grandparent via GetParentRow</legend><pre>// Use the GetParentRow to get the value instead string sCountry = row.GetParentRow(“O2OD”).GetParentRow(“C2O”)[“Country”].ToString(); Debug.WriteLine((sCountry == “US”) ? “US” : “Foreign Country”); </pre></fieldset>

Tweet Post Share Update RSS

Hi, I'm John Papa. I author this blog, create courses for Pluralsight and am a Google Developer Expert and Microsoft Regional Director. I travel speaking at events and train technology thought leaders