CODEWITHSUNDEEP
c#datatabledataset
Troy Loberger
Troy Loberger

Reputation: 347

DataTable Join or Merge

I have 3 Data tables populated with a dataset and table adapters/ binding sources and i need to run a Join query or find another way to get specific data. (the dataset contains each table listed)

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

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)

I am really stumped and cant find a way. (i have tried alot) Please help and thank you!

Nudiers approach so far:

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

            table1Column = tlobergeDataSet.Tb_Product.Columns[0];
            table2Column = tlobergeDataSet.Tb_Transactions.Columns[3];
            table3Column = tlobergeDataSet.Tb_Consumer.Columns[0];

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

Upvotes: 1

Views: 5391

Answers (4)

Shiran910029
Shiran910029

Reputation: 67

    public DataTable MergeTables(DataTable dtFirst, DataTable dtSecond, string CommonColumn)
    {
        DataTable dtResults = dtFirst.Clone();
        int count = 0;
        for (int i = 0; i < dtSecond.Columns.Count; i++)
        {
            if (!dtFirst.Columns.Contains(dtSecond.Columns[i].ColumnName))
            {
                dtResults.Columns.Add(dtSecond.Columns[i].ColumnName, dtSecond.Columns[i].DataType);
                count++;
            }
        }

        DataColumn[] columns = new DataColumn[count];
        int j = 0;
        for (int i = 0; i < dtSecond.Columns.Count; i++)
        {
            if (!dtFirst.Columns.Contains(dtSecond.Columns[i].ColumnName))
            {
                columns[j++] = new DataColumn(dtSecond.Columns[i].ColumnName, dtSecond.Columns[i].DataType);
            }
        }

        dtResults.BeginLoadData();
        foreach (DataRow dr in dtFirst.Rows)
        {
            dtResults.Rows.Add(dr.ItemArray);
        }
        foreach (DataRow dr in dtSecond.Rows)
        {
            foreach (DataRow dr1 in dtResults.Rows)
            {
                if (dr1[CommonColumn].ToString().Equals(dr[CommonColumn].ToString()))
                {
                    foreach (DataColumn c in columns)
                    {
                        dr1[c.ColumnName] = dr[c.ColumnName];
                    }
                }
            }
        }
        dtResults.EndLoadData();
        return dtResults;
    }

Upvotes: 2

Tejs
Tejs

Reputation: 41236

In LINQ, you can join the tables to find the data you want with syntax like so:

from a in keyTable
join b in anotherTable on a.Key equals b.Key
join c in aThirdTable on a.Key equals c.Key
select new
{
    // Anonymous Object Properties using identifier a, b, and c to get data
};

You should be able to take that snippet and generate a linq query that will generate an anonymous object containing the specific data representation that you need.

Upvotes: 0

Nudier Mena
Nudier Mena

Reputation: 3274

You can only relate two tables in a DataRelation object and to access the data from the dataset is straightforward because the data has been already related. 

        DataRelation relation = null;
        DataColumn table1Column = null;
        DataColumn table2Column = 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);

Upvotes: 0

Nudier Mena
Nudier Mena

Reputation: 3274

try with this.

DataRelation relation = null;
DataColumn table1Column = null;
DataColumn table2Column = null;
//retrieve column
table1Column = ds.Tables("Table1").Columns(0);
table2Column = ds.Tables("table2").Columns(0);
//relating tables
relation = new DataRelation("relation", table1Column, table2Column);
//assign relation to dataset
ds.Relations.Add(relation);

Upvotes: 0

Related Questions