none
Compare two Tables data for each row... RRS feed

  • Question

  • hi,

     I have two tables (say ParentTable & ChildTable).
     Both tables are having same columns.

     Now i want to compare the ChildTable data with ParentTable. How i can compare the data for each row of child table and store the result in a list of bool... The  result will be 'TRUE' for a row if the data matches and result will be 'FALSE' if there is any mismatch in the data.

     Is there is any in build method to do it...? If not which is the fastest the way to do this comparison...?

    Thanks in advancs,
    IamHuM

    Saturday, November 28, 2009 7:49 AM

Answers

  • First you need to create the relationship between your Parent & Child Table (i'm using Northwind Database)

          ' Add the relationships between Categories-Products
          mds.Relations.Add(New DataRelation("relCatProd", _
                 mds.Tables("Categories").Columns("CategoryID"), _
                 mds.Tables("Products").Columns("CategoryID"), True))

    Then you can iterate on the child rows once you iterate you can assign the result to your boolean variable
            ' Category selected; GetChildRows in Products
            For Each rowProduct In rowCategory.GetChildRows( _
             mds.Relations("relCatProd"))
              str.WriteLine(rowProduct("ProductID").ToString & _
              ": " & rowProduct("ProductName").ToString)
            Next
    


    Another Approach this one could be simpler, I put comments in the code
            'assign relationship
            ds.Relations.Add(New DataRelation("relCategories_Products", dtCat.Columns("CategoryID"), _
                                      dtProd.Columns("CategoryID"), True))
            'Add columns
            dtProd.Columns.Add("CategoryName", GetType(System.String), "Parent(relCategories_Products).CategoryName")
            'Add a Boolean Column to compare data
            dtProd.Columns.Add("VerifyColumn", GetType(System.Boolean), "Parent(relCategories_Products).CategoryID = CategoryID")
            'Iterate on the boolean column to compare data
            For Each dtRow As DataRow In dtProd.Rows
                If dtRow("VerifyColumn") = False Then
                    Exit For
                End If
            Next
    



    Here is the code in C# cause you didn't mention the language
    //assign relationship
    
    {
        ds.Relations.Add(new DataRelation("relCategories_Products", dtCat.Columns("CategoryID"), dtProd.Columns("CategoryID"), true));
        //Add columns
        dtProd.Columns.Add("CategoryName", typeof(System.String), "Parent(relCategories_Products).CategoryName");
        //Add a Boolean Column to compare data
        dtProd.Columns.Add("VerifyColumn", typeof(System.Boolean), "Parent(relCategories_Products).CategoryID = CategoryID");
        //Iterate on the boolean column to compare data
        foreach (DataRow dtRow in dtProd.Rows) {
            if (dtRow("VerifyColumn") == false) {
                break; // TODO: might not be correct. Was : Exit For
            }
        }
    }






    John
    Saturday, November 28, 2009 5:34 PM
  • The snippets below may not be directly on point but may give you some ideas. The context for the snippet is I am importing rows that match an exisiting set of rows. Some of imports may be new and should be appended. Others are a key match to an existing row but they could just be exact duplicates and should be ignored.

                SQL = "SELECT * FROM #TEMP_Certs"
                mda.SelectCommand = New SqlCommand(SQL, conPERP)
                mcmdBldr = New SqlCommandBuilder(mda)
                mda.Fill(mds)
                mdt = mds.Tables(0)
                Dim iID As Integer = General.Next_Key_Max("PERP_ARB_Certificates", "ID")
                For Each mrow In mdt.Rows
                    bUpdate = False
                    rows = Me.dtCerts_ARB.Select("certificateNo=" & mrow("certificateNo"), "Instance_ID DESC") ' look for proper row to update
                    If rows.GetUpperBound(0) = -1 Then ' create new row
                        rowNew = Me.dtCerts_ARB.NewRow
                        rowNew("ID") = iID
                        iID += 1
                        rowNew("Instance_ID") = 1
                        For iCol = 2 To mdt.Columns.Count - 1 ' copy all except ID and Instance_ID
                            rowNew(iCol) = mrow(iCol)
                        Next
                        Me.dtCerts_ARB.Rows.Add(rowNew)
                        iCntAdded += 1
                    Else ' look for inequality
                        For iCol = 4 To mdt.Columns.Count - 1
                            Dim lRows As DataRow = rows(0)
                            If Not CellsEqual(lRows, mrow, iCol) Then
                                ' change found, update row
                                lRows("Instance_ID") += 1
                                lRows("Status") = "New"
                                lRows("Source") = mrow("Source")
                                For iCol2 = 4 To mdt.Columns.Count - 1
                                    lRows(iCol2) = mrow(iCol2)
                                Next
                                iCntUpdated += 1
                                Exit For
                            End If
                        Next
                    End If
                Next
                daCerts_ARB.Update(dsCerts_ARB)
    ...
    
    
        Private Function CellsEqual(ByVal r1 As DataRow, ByVal r2 As DataRow, ByVal iCol As Integer) As Boolean
            If IsDBNull(r1(iCol)) And IsDBNull(r2(iCol)) Then Return True
            If IsDBNull(r1(iCol)) Or IsDBNull(r2(iCol)) Then Return False
            Return r1(iCol) = r2(iCol)
        End Function
    
    Wednesday, December 2, 2009 4:48 PM


  • Create relationship for Table1 as the Parent and Table2 as the Child.
    Then Copy Tables (Use Copy Method make sure you copy before adding relationship)
    and create another relationship for Table2 as the Parent and Table1 as the child.

    I'm referring to this following code from earlier posting.

    //assign relationship
    
    {
        ds.Relations.Add(new DataRelation("relCategories_Products", dtCat.Columns("CategoryID"), dtProd.Columns("CategoryID"), true));
        //Add columns
        dtProd.Columns.Add("CategoryName", typeof(System.String), "Parent(relCategories_Products).CategoryName");
        //Add a Boolean Column to compare data
        dtProd.Columns.Add("VerifyColumn", typeof(System.Boolean), "Parent(relCategories_Products).CategoryID = CategoryID");
        //Iterate on the boolean column to compare data
        foreach (DataRow dtRow in dtProd.Rows) {
            if (dtRow("VerifyColumn") == false) {
                break; // TODO: might not be correct. Was : Exit For
            }
        }
    }
    

    John
    Wednesday, December 2, 2009 11:55 PM

All replies

  • First you need to create the relationship between your Parent & Child Table (i'm using Northwind Database)

          ' Add the relationships between Categories-Products
          mds.Relations.Add(New DataRelation("relCatProd", _
                 mds.Tables("Categories").Columns("CategoryID"), _
                 mds.Tables("Products").Columns("CategoryID"), True))

    Then you can iterate on the child rows once you iterate you can assign the result to your boolean variable
            ' Category selected; GetChildRows in Products
            For Each rowProduct In rowCategory.GetChildRows( _
             mds.Relations("relCatProd"))
              str.WriteLine(rowProduct("ProductID").ToString & _
              ": " & rowProduct("ProductName").ToString)
            Next
    


    Another Approach this one could be simpler, I put comments in the code
            'assign relationship
            ds.Relations.Add(New DataRelation("relCategories_Products", dtCat.Columns("CategoryID"), _
                                      dtProd.Columns("CategoryID"), True))
            'Add columns
            dtProd.Columns.Add("CategoryName", GetType(System.String), "Parent(relCategories_Products).CategoryName")
            'Add a Boolean Column to compare data
            dtProd.Columns.Add("VerifyColumn", GetType(System.Boolean), "Parent(relCategories_Products).CategoryID = CategoryID")
            'Iterate on the boolean column to compare data
            For Each dtRow As DataRow In dtProd.Rows
                If dtRow("VerifyColumn") = False Then
                    Exit For
                End If
            Next
    



    Here is the code in C# cause you didn't mention the language
    //assign relationship
    
    {
        ds.Relations.Add(new DataRelation("relCategories_Products", dtCat.Columns("CategoryID"), dtProd.Columns("CategoryID"), true));
        //Add columns
        dtProd.Columns.Add("CategoryName", typeof(System.String), "Parent(relCategories_Products).CategoryName");
        //Add a Boolean Column to compare data
        dtProd.Columns.Add("VerifyColumn", typeof(System.Boolean), "Parent(relCategories_Products).CategoryID = CategoryID");
        //Iterate on the boolean column to compare data
        foreach (DataRow dtRow in dtProd.Rows) {
            if (dtRow("VerifyColumn") == false) {
                break; // TODO: might not be correct. Was : Exit For
            }
        }
    }






    John
    Saturday, November 28, 2009 5:34 PM
  • hi John,

            
    Thanks for the reply. I will try your code. Mean while if i dont know which table is parent and which table is child then how I can get the difference between the tables...?

             I am working in C# .Net 2.0.

    Thanks once again for your reply...

    IamHuM

    Tuesday, December 1, 2009 9:08 AM
  • You can incorporate If Condition in your code to determine Parent from child table, there must be unique identifier for parent and child Table isn't ?
    John
    Tuesday, December 1, 2009 9:33 PM
  • No there is no such unique identifier for this tables.
    Can you please suggest any other way.

    Thanks,
    IamHuM
    Wednesday, December 2, 2009 6:59 AM
  • The snippets below may not be directly on point but may give you some ideas. The context for the snippet is I am importing rows that match an exisiting set of rows. Some of imports may be new and should be appended. Others are a key match to an existing row but they could just be exact duplicates and should be ignored.

                SQL = "SELECT * FROM #TEMP_Certs"
                mda.SelectCommand = New SqlCommand(SQL, conPERP)
                mcmdBldr = New SqlCommandBuilder(mda)
                mda.Fill(mds)
                mdt = mds.Tables(0)
                Dim iID As Integer = General.Next_Key_Max("PERP_ARB_Certificates", "ID")
                For Each mrow In mdt.Rows
                    bUpdate = False
                    rows = Me.dtCerts_ARB.Select("certificateNo=" & mrow("certificateNo"), "Instance_ID DESC") ' look for proper row to update
                    If rows.GetUpperBound(0) = -1 Then ' create new row
                        rowNew = Me.dtCerts_ARB.NewRow
                        rowNew("ID") = iID
                        iID += 1
                        rowNew("Instance_ID") = 1
                        For iCol = 2 To mdt.Columns.Count - 1 ' copy all except ID and Instance_ID
                            rowNew(iCol) = mrow(iCol)
                        Next
                        Me.dtCerts_ARB.Rows.Add(rowNew)
                        iCntAdded += 1
                    Else ' look for inequality
                        For iCol = 4 To mdt.Columns.Count - 1
                            Dim lRows As DataRow = rows(0)
                            If Not CellsEqual(lRows, mrow, iCol) Then
                                ' change found, update row
                                lRows("Instance_ID") += 1
                                lRows("Status") = "New"
                                lRows("Source") = mrow("Source")
                                For iCol2 = 4 To mdt.Columns.Count - 1
                                    lRows(iCol2) = mrow(iCol2)
                                Next
                                iCntUpdated += 1
                                Exit For
                            End If
                        Next
                    End If
                Next
                daCerts_ARB.Update(dsCerts_ARB)
    ...
    
    
        Private Function CellsEqual(ByVal r1 As DataRow, ByVal r2 As DataRow, ByVal iCol As Integer) As Boolean
            If IsDBNull(r1(iCol)) And IsDBNull(r2(iCol)) Then Return True
            If IsDBNull(r1(iCol)) Or IsDBNull(r2(iCol)) Then Return False
            Return r1(iCol) = r2(iCol)
        End Function
    
    Wednesday, December 2, 2009 4:48 PM


  • Create relationship for Table1 as the Parent and Table2 as the Child.
    Then Copy Tables (Use Copy Method make sure you copy before adding relationship)
    and create another relationship for Table2 as the Parent and Table1 as the child.

    I'm referring to this following code from earlier posting.

    //assign relationship
    
    {
        ds.Relations.Add(new DataRelation("relCategories_Products", dtCat.Columns("CategoryID"), dtProd.Columns("CategoryID"), true));
        //Add columns
        dtProd.Columns.Add("CategoryName", typeof(System.String), "Parent(relCategories_Products).CategoryName");
        //Add a Boolean Column to compare data
        dtProd.Columns.Add("VerifyColumn", typeof(System.Boolean), "Parent(relCategories_Products).CategoryID = CategoryID");
        //Iterate on the boolean column to compare data
        foreach (DataRow dtRow in dtProd.Rows) {
            if (dtRow("VerifyColumn") == false) {
                break; // TODO: might not be correct. Was : Exit For
            }
        }
    }
    

    John
    Wednesday, December 2, 2009 11:55 PM