Retrieve Child and Parent column values in ADO.NET

In ADO.NET you can compute Child or Parent column values, coming from DataTable objects that are related using a DataRelation between them. If you don’t know how DataRelation is used in ADO.NET read a previous post of these ADO.NET series. This post will describe you the following procedures:

  1. Adding a column to a child DataTable that displays a Parent’s column value
  2. Adding a column to a parent DataTable that Aggregates a Child Table’s Column Values

Adding a column to a child DataTable that displays a Parent’s column value

Let’start. Open Visual Studio and create a new console application project named RetrieveChildAndParentValuesAdoNet. Add using statements for System.Data and System.Data.SqlClient namespaces. We are going to use AdventureWorks database in this example and more specifically, two related Tables, Product and ProductSubcategory from Production. Each Product record has a foreign key pointing to a ProductSubcategory record, through the ProductSubcategoryID primary key. What we want to do, is add a particular Column to the Product-child table, in order to display the name of the ProductSubcategory.

adonet_child_parent_1
Paste the following code inside your main method.

string sqlConnectString = "Data Source=localhost;" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = @"SELECT * FROM Production.ProductSubcategory;
                SELECT * FROM Production.Product;";

            DataSet ds = new DataSet();

            // Fill the DataSet
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.TableMappings.Add("Table", "ProductSubcategory");
            da.TableMappings.Add("Table1", "Product");
            da.Fill(ds);

            // Relate the Parent and Child tables in the DataSet
            DataRelation dr = new DataRelation("ProductSubcategory_Product",
                ds.Tables["ProductSubcategory"].Columns["ProductSubcategoryID"],
                ds.Tables["Product"].Columns["ProductSubcategoryID"]);
            ds.Relations.Add(dr);

            // Add the Name column from ProductSubcategory to 
            // the Product table
            ds.Tables["Product"].Columns.Add("CategoryName", typeof(string),
                "Parent(ProductSubcategory_Product).Name");

            // Output fields from first three header rows with detail
            for (int i = 0; i < 3; i++)
            {
                DataRow rowParent = ds.Tables["ProductSubcategory"].Rows[i];
                Console.WriteLine("Parent: ProductSubcategoryID = {0}, SubCategory Name = {1}",
                    rowParent["ProductSubcategoryID"], rowParent["Name"]);

                foreach (DataRow rowDetail in rowParent.GetChildRows(dr))
                {
                    Console.WriteLine("\tProduct: ProductID = {0},Product Name = {1}, " +
                        "(Sub) CategoryName = {2}",
                        rowDetail["ProductID"], rowDetail["Name"],
                        rowDetail["CategoryName"]);
                }
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();

Lot’s of interesting lines here. The sqlSelect string, will retrieve Production.ProductSubcategory and Production.Product Tables from the AdventureWorks database. Lines 11-12 will map the retrieved tables to the respective DataTable names. We do this in order to change the default DataTable names, ADO.NET uses when filling a DataSet. Lines 16-19 create the DataRelation between the two DataTables, through the ProductSubcategoryID common column. Notice that we give a specific name to that DataRelation, ProductSubcategory_Product. We ‘ll use this name later, in order to add a special column to the child DataTable Product. We name this column “CategoryName” and it’s computed value will always be Parent(ProductSubcategory_Product).Name. This tells that it’s value will be equal to a Parent column, named “Name”. The DataRow.GetChildRows(DataRelation dr) will get all child rows of current DataRow object, based on a DataRelation dr. Build and run your application.
adonet_child_parent_2

Adding a column to a parent DataTable that Aggregates a Child Table’s Column Values

This time we will do the reversed procedure. We will add two Columns to the parent DataTable ProductSubcategory that can aggregate Product column values. More specifically, first we will try to add a column that can compute the total ListPrice value for all Products in a ProductSubcategory. Next will add another column, that can simply count the child rows, so this way we can see how many products exist in a specific ProductSubcategory. Change the code we added before to the following.

static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=localhost;" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = @"SELECT * FROM Production.ProductSubcategory;
                SELECT * FROM Production.Product;";

            DataSet ds = new DataSet();

            // Fill the DataSet
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.TableMappings.Add("Table", "ProductSubcategory");
            da.TableMappings.Add("Table1", "Product");
            da.Fill(ds);

            // Relate the Parent and Child tables in the DataSet
            DataRelation dr = new DataRelation("ProductSubcategory_Product",
                ds.Tables["ProductSubcategory"].Columns["ProductSubcategoryID"],
                ds.Tables["Product"].Columns["ProductSubcategoryID"]);
            ds.Relations.Add(dr);

            // Add the Name column from ProductSubcategory to 
            // the Product table
            ds.Tables["Product"].Columns.Add("CategoryName", typeof(string),
                "Parent(ProductSubcategory_Product).Name");

            // Add a column to the ProductSubcategory table summing all
            // Product ListPrice values in Product child table
            ds.Tables["ProductSubcategory"].Columns.Add("TotalProductListPrice",
                typeof(decimal), "SUM(Child.ListPrice)");
            // Add a column to the ProductSubcategory table that counts
            // how many child rows exist for a specific ProductSubcategory
            ds.Tables["ProductSubcategory"].Columns.Add("NumberOfProducts",
                typeof(int), "Count(Child.ListPrice)");

            // Output fields from first ten parent rows with detail
            for (int i = 0; i < 10; i++)
            {
                DataRow rowParent = ds.Tables["ProductSubcategory"].Rows[i];
                Console.WriteLine("Parent: ProductSubcategoryID = {0}, SubCategory Name = {1},"+
                "Total Product ListPrice = {2}, Number of Products {3}",
                    rowParent["ProductSubcategoryID"], rowParent["Name"],
                    rowParent["TotalProductListPrice"], rowParent["NumberOfProducts"]);

                //foreach (DataRow rowDetail in rowParent.GetChildRows(dr))
                //{
                //    Console.WriteLine("\tProduct: ProductID = {0},Product Name = {1}, " +
                //        "(Sub) CategoryName = {2}",
                //        rowDetail["ProductID"], rowDetail["Name"],
                //        rowDetail["CategoryName"]);
                //}
            }
        }

You will notice that we have added only few lines to our code. Lines 30-31 to the Parent DataTable a column named “TotalProductListPrice” with a computed value equal to SUM(Child.ListPrice). This is an aggregate function, that will Sum and return all ListPrice values from it’s child rows. Same story in lines 34-35 where we added a Column named “NumberOfProducts” with a computed value of Count(Child.ListPrice) (I think it’s quite self explainatory what it does..). Also, I have commented out lines that displays child rows, in order to see results more clearly. Build and run your application.
adonet_child_parent_3
That’s it, that’s the way you can add special columns either to a child or a parent DataTable in ADO.NET. I hope you enjoyed the post. Download RetrieveChildAndParentValuesAdoNet project we created from here.

Advertisements


Categories: ADO.NET

Tags: ,

1 reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack developer with expertise in ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, NUCES-FAST CS grad, MS candidate @LUMS, EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

Sriramjithendra Nidumolu

Personal Notes of Sriramjithendra

%d bloggers like this: