How to compare data of each row between 2 datatable C#?

Question

I have 2 datatables that came from 2 different sql (msSQL and MySQL) with one similar column data.

DataTable 1 : employeeno, device, devicestatus

DataTable 2 : employeeno, name, employeestatus

I probably need to run a foreach with multiple IFs on the Datatable 1 againts Datatable2 and put result to Datatable3 as such :

     DataTable ResultDataTable = new DataTable("ResultDataTable");
            foreach (DataRow dr in myPHPdt.Rows)
            {
                string checkEmpNo = dr.Table.Rows;
                myPHPdt.Select(checkEmpNo);
                if (/*Some Condition here*/ )
                {
                    if (/*Some Condition here*/ )
                    {
                        if (checkEmpNo = '999999')
                        {
                            LMTF.LogMessageToFile("This is skipped");
                        }
                        ResultDataTable.Rows.Add(dr.ItemArray);
                        DefaultCellStyle.BackColor = Color.Red;
                    }
                    ResultDataTable.Rows.Add(dr.ItemArray);
                }
            }
            dataGridView3.DataSource = ResultDataTable;

The problem that i have is how do i check each row of employeeno with multiple IFs in DataTable1 to compare with DataTable2?

Also something wrong with the "string checkEmpNo = dr.Table.Rows" part as it is suppose to return the employeeno of the row it is currently in.

Requirement :

If Employeeno in DataTable1 exist in DataTable2 - Add to DataTable3 rows.

If employeestatus of Employeeno is Terminated - Add to DataTable3 rows with row colour red.

If employeeno in DataTable1 equal to 999999 , do nothing.

Is this even possible to do with DataTable comparison method?

Is there a better method to do this?

Edit 1 - 12/3/2020

I have tried this - but it did not return any result , though it did show the Column Names. ( feels like the DataRows are empty )

DataTable targetTable = new DataTable();


            var dt2Columns = myPHPdt.Columns.OfType<DataColumn>().Select(dc => new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
            var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
                                  where targetTable.Columns.Contains(dc.ColumnName) == false
                                  select dc;

            targetTable.Columns.AddRange(dt2FinalColumns.ToArray());

            var rowData = from row1 in myPHPdt.AsEnumerable()
                          join row2 in orisoftdt.AsEnumerable()
                          on row1["employeeno"] equals row2["EMPLOYEE_ID"]
                          select row1.ItemArray.Concat(row2.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)).ToArray();

            foreach (object[] values in rowData)
                targetTable.Rows.Add(values);


            dataGridView3.DataSource = targetTable;

You'll have to modify his last edit a bit, but combine the data into one datatable then go through the rows and perform your IF statements. [How to Left Outer Join two DataTables in c#?](https://stackoverflow.com/questions/17684448/how-to-left-outer-join-two-datatables-in-c) — Mikael, Mar 11 '20 at 15:48
Let me try this. Thanks @Mikael , didnt know we can do left joins for datatables. — Mohd Nasrul Iwan Fajaruddin, Mar 11 '20 at 15:50
Place breakpoints and step through the code to figure it out — Jonathan Alfaro, Mar 11 '20 at 22:22

score 0 · Accepted Answer · answered Mar 12 '20 at 14:04

After multiple tests with multiple types of method on Joins , i have resorted to do manual row by row insertion to Datatable.

With that - Here is my answer to my own question :

private void Button2_Click(object sender, EventArgs e)
        {
            var LMTF = new LogToFile();

            DataTable targetTable = new DataTable();
            targetTable.Columns.Add("ID", typeof(string));
            LMTF.LogMessageToFile("Column Employee Addedd");
            targetTable.Columns.Add("MACADDRESS", typeof(string));
            LMTF.LogMessageToFile("Column EMPLOYEE_ID Addedd");
            targetTable.Columns.Add("REMARKS", typeof(string));
            LMTF.LogMessageToFile("Column EMPLOYEE_NAME Addedd");
            targetTable.Columns.Add("STAFF ID", typeof(string));
            LMTF.LogMessageToFile("Column EMPLOYEE_STATUS Addedd");
            targetTable.Columns.Add("STAFF NAME", typeof(string));
            LMTF.LogMessageToFile("Column username Addedd");
            targetTable.Columns.Add("Status", typeof(string));
            LMTF.LogMessageToFile("Column comment Addedd");

            try
            {
                foreach (DataRow row in myPHPdt.Rows)
                {
                    string emp1 = row["employeeno"].ToString();
                    string emp2 = row["username"].ToString();
                    string emp3 = row["comment"].ToString();
                    string empcheck = "";
                    string ActiveStaff = "A";
                    LMTF.LogMessageToFile("This is before emp1 check" + emp1 + " " + emp2 + " " + emp3);
                    if (emp1 != empcheck) /*Checks if entry is not null*/
                    {
                        try
                        {
                            orisoftds.Tables["Staff_List"].DefaultView.RowFilter = "EMPLOYEE_ID = " + emp1;
                            DataTable dt = (orisoftds.Tables[0].DefaultView).ToTable();


                            foreach (DataRow orirow in dt.Rows)
                            {
                                SID = orirow["EMPLOYEE_ID"].ToString();
                                sName = orirow["EMPLOYEE_NAME"].ToString();
                                sStatus = orirow["EMPLOYEE_STATUS"].ToString();
                            }
                        }
                        catch (System.Exception ex)
                        {
                            LMTF.LogMessageToFile("something wrong with checking orisoft : " + ex.Message);
                        }
                        if (sStatus != ActiveStaff) /*Checks if Staff is NOT active*/
                        {
                            targetTable.Rows.Add(emp1, emp2, emp3, SID, sName, sStatus);
                            LMTF.LogMessageToFile("Log Non Active Staff" + emp1 + " " + emp2 + " " + emp3 + SID + sName + sStatus);
                        }
                        else
                        {
                            LMTF.LogMessageToFile("Log Active Staff" + emp1 + " " + emp2 + " " + emp3 + SID + sName + sStatus);
                        }
                    }
                    else
                    {
                        targetTable.Rows.Add(emp1+"noid", emp2, emp3, SID, sName, sStatus);
                        LMTF.LogMessageToFile(emp1 + "noid " + emp2 + " " + emp3 + SID + sName + sStatus);
                    }
                }
            }
            catch (System.Exception ex)
            {
                LMTF.LogMessageToFile("something wrong with adding rows to datatable : " + ex.Message);
            }
            try
            {
                dataGridView3.DataSource = targetTable;
            }
            catch (System.Exception ex)
            {
                LMTF.LogMessageToFile("something wrong with  : " + ex.Message);
            }
        }

How to compare data of each row between 2 datatable C#?

1 Answers1