Avatar of Sandiya
Sandiya
 asked on

Urgent :compare two datatables

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.
.NET ProgrammingASP.NETVisual Basic.NET

Avatar of undefined
Last Comment
Sandiya

8/22/2022 - Mon
Kamal Khaleefa

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
Sandiya

ASKER
Hi

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

Thanks
Dirk Haest

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Sandiya

ASKER
Sorry i have not used linq can u please explain me in detail how to proceed after the code
Thanks.
Nasir Razzaq

ASKER CERTIFIED SOLUTION
pkbugudai

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sandiya

ASKER
Thanks all
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.