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:
- Adding a column to a child DataTable that displays a Parent’s column value
- 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.
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.
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.
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.
Categories: ADO.NET
chsakell's Blog 
Reblogged this on Sutoprise Avenue, A SutoCom Source.