divide dataset in to matched and unmatched datatab

Question

In my project there are two datasets dsNames and dsDetails.

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

dtNames

EmployeeName    EmployeeRole

   a                2
   b                3
   c                4

dtDetails

 Empid     EmployeeName   EmpCity   EmployeeRole

  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 datasets based on the EmployeeName and EmployeeRole (here comparing to dsNames) and store the result in another DataSet dsMain(table from dsDetails) and then divide the two dataSets 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)

            foreach (DataRow rwMain in dsNames.Tables[0].Rows)
            {
                foreach (DataRow rwSub in dsDetails.Tables[0].Rows)
                {
                    if (rwMain["EmployeeName"].Equals(rwSub["EmployeeName"]))
                    {
                        if (rwMain["EmployeeRole"].Equals(rwSub["EmployeeRole"]))
                        {
                            // Matched DataTable
                        }
                        else
                        {
                            //Unmatched DataTable
                        }
                    }
                }
            }

I am doing something wrong in the above code and also it looks me i am not doing it in a right way.

Please no linq because i know it but i cant use it in my project.

please assist.

Answer 1

You can use LINQ, you just have to join both tables. Once by EmpName only and once by both columns.

var sameRoleNames = from rn in dtNames.AsEnumerable()
                join rd in dtDetails.AsEnumerable()
                on new
                {
                    Name = rn.Field<string>("EmpName"),
                    Role = rn.Field<string>("EmpRole"),
                } equals new
                {
                    Name = rd.Field<string>("EmpName"),
                    Role = rd.Field<string>("EmpRole"),
                }
                select rd;
var notSameRoleNames = from rn in dtNames.AsEnumerable()
                   join rd in dtDetails.AsEnumerable()
                   on rn.Field<string>("EmpName") equals rd.Field<string>("EmpName")
                   where rn.Field<string>("EmpRole") != rd.Field<string>("EmpRole")
                   select rd;
var matchedDataTable = sameRoleNames.CopyToDataTable();
var unmatchedDataTable = notSameRoleNames.CopyToDataTable();

I have only just seen your answer on the "can you use Linq" question. I leave it at that anyway, maybe it's helpful for someone else.

Answer 2

I solved it by using Select Property

 DataRow[] matchedRows = dtNames.Select("EmployeeName = '" + dsDetails.Tables[0].Columns[0].ToString() + "'"
                    + " AND " + " EmployeeRole ='" + dsDetails.Tables[0].Columns[1].ToString() + "'");
                //
 DataRow[] mismatchedRows = dsDetails.Tables[0].Select("EmployeeName <> '" + dtNames.Columns[0].ToString() + "'"
                    + " OR" + " EmployeeRole <>'" + dtNames.Columns[1].ToString() + "'");