How do I define a DataRelation between a DataSet's DataTables on multiple columns?

Question

In my project there are two datatables dtNames and dtDetails.

I dont know about SQL. Here I am connecting to XML and Textfiles.

dtNames

EmpName    EmpRole

   a         2
   b         3
   c         4

dtDetails

 Empid     EmpName   EmpCity   EmpRole

  101        a         abc       3  //EmpRole not equal to above table EmpRole
  102        b         abc       3
  103        c         xyz       4
  104        d         pqr       5
  105        e         rst       6

I want to relate these two datatables based on the EmpName and EmpRole (here comparing to dtNames) and store the result in a DataSet dsMain(table from dtDetails) and then divide the two datatables in according to comparison like matchedDataTable and unmatchedDataTable.

I know this can be done using DataRelation or RowFilter but i cant get a thought how to do this because there are two columns to be compared with other datatable two columns which i dont know.(I am beginner in .net)

I tried the below code: (not working)

            DataSet dsMain = new DataSet();
            DataRelation newRelation = new DataRelation("processData"
                , dtNames.Columns["EmpName"], dtDetails.Columns["EmpName"]);
            dsMain.Relations.Add(newRelation);

As you can see In the above code I am just comparing one column with other column. How compare with two. I am very close

please assist.

I think this is linq? if it is then i dont want it. I know how to do using linq but my project is strictly non-linq. cant it be done using dataset rowfilters or datarelations.. — Mr_Green
– Mr_Green, Commented Oct 18, 2012 at 13:32
As a side note: you might want to re-evaluate your database design (or your retrieval queries), as the datatables at the moment are not normalized (i.e. you are repeating the EmpRole data in both tables). — pleinolijf
– pleinolijf, Commented Oct 18, 2012 at 13:49

Artemix · Accepted Answer · 2012-10-18 13:39:35Z

7

Use the constructor that accepts column arrays:

DataSet dsMain = new DataSet();
DataRelation newRelation = new DataRelation("processData",
    new DataColumn[] { dtNames.Columns["EmpName"], dtNames.Columns["EmpRole"] },
    new DataColumn[] {dtDetails.Columns["EmpName"], dtDetails.Columns["EmpRole"]}
);
dsMain.Relations.Add(newRelation);

answered Oct 18, 2012 at 13:39

Artemix

2,1682 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr_Green Over a year ago

thank you, how to divide it into two tables like matchedtable and unmatchedtable ?

Artemix Over a year ago

The relation will actually prevent unmatched rows to be added. To separate "matched" from "unmatched" you'll have to use plain old foreach loop.

pleinolijf · Accepted Answer · 2012-10-18 13:37:09Z

4

Did you check the possible constructors of DataRelation ?

Just pass an array of DataColumn, and you can combine multiple columns. Like so:

DataRelation newRelation = new DataRelation("test", new DataColumn[] { dt1.Columns[0], dt1.Columns[1] },
                                                    new DataColumn[] { dt2.Columns[0], dt2.Columns[1] });

answered Oct 18, 2012 at 13:37

pleinolijf

9211 gold badge12 silver badges30 bronze badges

2 Comments

Mr_Green Over a year ago

thank you, how to divide it into two tables like matchedtable and unmatchedtable ?

pleinolijf Over a year ago

'Matching' of data (if I understand you correctly) should happen at database level, so before the data reaches your application. Of course, this depends on your specific situation.

Collectives™ on Stack Overflow

How do I define a DataRelation between a DataSet's DataTables on multiple columns?

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related