CODEWITHSUNDEEP
c#datasetdatabase-relations
Troy Loberger
Troy Loberger

Reputation: 347

Data set relations query with 3 tables and addition

I need some help getting data from a dataset. Code:

            DataRelation relation = null;
            DataColumn table1Column = null;
            DataColumn table2Column = null;
            DataColumn table2Column1 = null;
            DataColumn table3Column = null;

            table1Column = tlobergeDataSet.Tb_Product.Columns[0];
            table2Column = tlobergeDataSet.Tb_Transactions.Columns[2];
            table2Column1 = tlobergeDataSet.Tb_Transactions.Columns[1];
            table3Column = tlobergeDataSet.Tb_Consumer.Columns[0];

            relation = new DataRelation("relation", table1Column, table2Column);
            tlobergeDataSet.Relations.Add(relation);

            relation = new DataRelation("relation1", table3Column, table2Column1);
            tlobergeDataSet.Relations.Add(relation);

Given a product name i need to populate a table with each distinct city and how much that product has sold for in that city (add all prices for that product to any consumer in a given city)

ex Tables:

Product Table:

Prod_ID    Name    

1          tv
2          couch

Consumer Table:

Con_Id    Name    City
----------------------
1         Gray    New York
2         Joe     Chicago
3         Steve   Madison

Transactions Table

Tran_Id   Con_ID    Prod_ID    Price
-------------------------------------
1         2         1          900
2         1         2          300
3         1         1          900

Needed Table

City      Total Sales
----------------------
New York  1200
Chicago   900
Madison   0

Upvotes: 1

Views: 2757

Answers (1)

Pravin Pawar
Pravin Pawar

Reputation: 2569

try this one, now this is tested

var query = from consumer in Tb_Consumer.AsEnumerable()
            select new
            {
               CityName = consumer.Field<string>("City"),
               //Name of relationship i.e. relation1 is important here
               Sales = consumer.GetChildRows("relation1").Sum(tx=> tx.Field<decimal>("Price")) 
            };

var datatable = new DataTable();
col = new DataColumn("City");
datatable.Columns.Add(col);
col = new DataColumn("TotalSales");
datatable.Columns.Add(col);

foreach (var item in query.ToList())
{
    var newrow = datatable.NewRow();
    newrow["City"] = item.CityName;
    newrow["TotalSales"] = item.Sales;
    datatable.Rows.Add(newrow);
}

Upvotes: 2

Related Questions