Urgent :compare two datatables

Sandiya
Sandiya used Ask the Experts™
on
Hi All,

I want a vb.net code tocheck all columns of Source table and see if they exist in Target table. If they dont exist then i want to add the columns in the target table.The source table comes from salesforce and the target table is in sql server.

I just want a code to identify the missing columns and suggest the user to create the column in the target.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kamal KhaleefaInformation Security Specialist

Commented:
hi

i recommend the following

dim dt1 as new datattable=get data from course in db1
dim dt2 as new datattable=get data from course in db2

then write a for loop to compare the columns name
ex: for i as integer=0 to dt1. Columns.Count -1 ...
and then u can know what is the missing
and base on that create a sql statement to do the necessary modification and execute it

Author

Commented:
Hi

Can u please explain the logic to compare column names between 2 datatable

Thanks
Dirk HaestProject manager

Commented:
You can use linq. Small example attached.

The last query will tell you that you have a column (namely state) in your second datatable that is not in your first datatable

 Private Sub compareTables()
        Dim Table1 As DataTable = New DataTable("Customers")
        Table1.Columns.Add("Name", Type.GetType("System.String"))
        Table1.Columns.Add("Firstname", Type.GetType("System.String"))
        Table1.Columns.Add("Street", Type.GetType("System.String"))
        Table1.Columns.Add("HouseNumber", Type.GetType("System.String"))
        Table1.Columns.Add("PostalCode", Type.GetType("System.String"))

        Dim Table2 As DataTable = New DataTable("Customers2")
        Table2.Columns.Add("Name", Type.GetType("System.String"))
        Table2.Columns.Add("Firstname", Type.GetType("System.String"))
        Table2.Columns.Add("State", Type.GetType("System.String"))

        Dim firstquery = From col In Table1.Columns Select col.columnName
        Dim notinsecondtable = From col In Table2.Columns Where Not firstquery.Contains(col.columnName)
    End Sub

Open in new window

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
Sorry i have not used linq can u please explain me in detail how to proceed after the code
Thanks.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Dim Table1 As DataTable = New DataTable("Customers")
        Table1.Columns.Add("Name", Type.GetType("System.String"))
        Table1.Columns.Add("Firstname", Type.GetType("System.String"))
        Table1.Columns.Add("Street", Type.GetType("System.String"))
        Table1.Columns.Add("HouseNumber", Type.GetType("System.String"))
        Table1.Columns.Add("PostalCode", Type.GetType("System.String"))

        Dim Table2 As DataTable = New DataTable("Customers2")
        Table2.Columns.Add("Name", Type.GetType("System.String"))
        Table2.Columns.Add("Firstname", Type.GetType("System.String"))
        Table2.Columns.Add("State", Type.GetType("System.String"))
        Dim t1(100) As String
        For i As Integer = 0 To Table1.Columns.Count - 1

            t1(i) = Table1.Columns(i).ColumnName
        Next
        Dim t2(100) As String
        For j As Integer = 0 To Table2.Columns.Count - 1
            t2(j) = Table2.Columns(j).ColumnName
        Next
        Dim t3 As String = ""
        Dim found As Boolean = False
        Dim s1 As String
        For i As Integer = 0 To t1.Length - 1

            If Not t2.Contains(t1(i)) Then
                If t3 = "" Then
                    t3 = t1(i)
                Else
                    t3 = t3 & ", " & t1(i)
                End If
                Table2.Columns.Add(t1(i), Type.GetType("System.String"))
            End If

        Next

Author

Commented:
Thanks all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial