DataTable consisting of 2 other DataTables

[Maziar Aflatoun]

Hi,

I have two datatables,

DataTable1
dtCurProducts.Columns.Add(new DataColumn("Id",
Type.GetType("System.String")));
dtCurProducts.Columns.Add(new DataColumn("ProductId",
Type.GetType("System.String")));
dtCurProducts.Columns.Add(new DataColumn("Quantity",
Type.GetType("System.String")));
dtCurProducts.Columns.Add(new DataColumn("FinalPrice",
Type.GetType("System.String")));


DataTable2
dtProducts.Columns.Add(new DataColumn("Id", Type.GetType("System.String")));
dtProducts.Columns.Add(new DataColumn("Name",
Type.GetType("System.String")));
dtProducts.Columns.Add(new DataColumn("ProductCode",
Type.GetType("System.String")));
dtProducts.Columns.Add(new DataColumn("DefaultPrice",
Type.GetType("System.String")));

DataTable2 contains a list of all of my products. DataTable1 contains the
Quantity and the FinalPrice. Now I like to create another DataTable or
expand DataTable1 as I'm creating it to include 3 additional fields taken
from DataTable2 so that my end result would be a DataTable containing
ProductId, Quantity, Name, ProductCode, DefaultPrice and FinalPrice
(Relationship between the 2 tables defined by
DataTable1.ProductId=DataTable2.Id

Can someone please tell me how I can accomplish this? Maybe as I'm creating
the contents of DataTable1 (Given I have DataTable2)

Thank you
Maziar A.

 

[Elton Wang]

Hi Maziar,

If your data are from database tables, that's very simple,
you can run following join query to fill datatable:

Select T1.ID, T1.ProductId, T1.Quantity, T2.Name,
T2.ProductCode,T2.DefaultPrice, T1.FinalPrice
From Table1 T1 Left Join Table2 T2
On T1.ID = T2.Id
Where Condition

Otherwise, you can build DataTable3 like this:

After filling data in both DataTable1 and DataTable2

to build DataTable3, dtAllProducts:

dtAllProducts.Columns.Add(new DataColumn("Id",
Type.GetType("System.String")));
dtAllProducts.Columns.Add(new DataColumn("ProductId",
Type.GetType("System.String")));
// Add other columns

DataView dv2 = dtProducts.DefaultView;
DataRow rowAll;
foreach (DataRow row in dtCurProducts.Rows){
dv2.RowFilter = "Id='" + row["ID"].ToStrong() + "'";
rowAll = dtAllProducts.NewRow();
rowAll["ID"] = row["ID"].ToStrong();
rowAll["ProductId"] = row["ProductId"].ToStrong();
rowAll["ProductId"] = row["ProductId"].ToStrong();
rowAll["Quantity"] = row["Quantity"].ToStrong();
// Other data from dtCurProducts
if (dv2.Count > 0){
rowAll["Name"] = dv2.Item[0].["Name"].ToString();
// Other data from dtProducts
}else{
rowAll["Name"] = DBNull.Value;
rowAll["ProductCode"] = DBNull.Value;
rowAll["DefaultPrice"] = DBNull.Value;
}
}

HTH

Elton Wang
(e-mail address removed)