How to select data from local database when it is not present in remote database

Question

Is it possible to select data rows from local database when those rows are not present in remote database? I wrote a code, but it doesn't work: conlocal is the local database connection, and conremote is the remote database connection; Dim command as

sqlcommand =  new sqlcommand ("select id from table", conlocal, "where id not in table",conremote)

Answer 1

Comments in line

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    'Get ID's from remote database
    Dim RemoteDT As New DataTable
    Using cn As New SqlConnection("Your remote connection string")
        Using cmd As New SqlCommand("Select Id From [RemoteTableName];", cn)
            cn.Open()
            RemoteDT.Load(cmd.ExecuteReader)
        End Using
    End Using
    'Put the Ids into an array
    Dim IdsInRemote As Integer() = (RemoteDT.AsEnumerable().[Select](Function(x) x.Field(Of Integer)("Id"))).ToArray()
    'Change the array to a single string that can be used in the Select statement
    Dim Remotes As String = String.Join(",", IdsInRemote)
    Dim LocalDT As New DataTable
    Using cn As New SqlConnection("Your local connection string")
        Using cmd As New SqlCommand($"Select Id From [LocalTableName] Where Id Not In ({Remotes});", cn)
            cn.Open()
            LocalDT.Load(cmd.ExecuteReader)
        End Using
    End Using
    For Each row As DataRow In LocalDT.Rows
        Debug.Print(row(0).ToString)
    Next
End Sub

EDIT

For vb.net versions prior to version 14 (2015)

Using cmd As New SqlCommand(String.Format("Select Id From [LocalTableName] Where Id Not In ({0});", Remotes), cn)

EDIT

Explanation of RemoteDT.AsEnumerable().[Select](Function(x) x.Field(Of Integer)("Id")).ToArray()

.AsEnumerable is a method of a DataTable that returns an enumerable of DataRow.

Select will pick rows from the enumerable that match the criteria we provide. In this case we are providing a Function with a parameter x.

x refers to the elements of the of the IEnumerable, in this case a DataRow. You could name this anything you want. The variable would only be seen inside this inline function so a simple name is OK. A descriptive name would be fine too but since it is seen inline only it really isn't necessary.

So the function checks every x in the enumerable (every DataRow) and returns only the values in Field of Integers called "Id"

My error here!

Finally the enumerable is converted to an array. I neglected to add the parenthesis around the enumerable that is to be converted to an array.

(RemoteDT.AsEnumerable().[Select](Function(x) x.Field(Of Integer)("Id"))).ToArray()

EDIT

Since I have now found out that Id is a String

    'Put the Ids into an array
    'Change the datatype of the array and the field to String
    Dim IdsInRemote As String() = (RemoteDT.AsEnumerable().[Select](Function(x) x.Field(Of String)("Id"))).ToArray()
    'Change the array to a single string that can be used in the Select statement
    'Add single quotes to the .Join so Sql Server will interpret as String
    Dim Remotes As String = String.Join("','", IdsInRemote)
    'Put a single quote on the beginning and end of the Remotes string
    'The Join does not do this
    Remotes = "'" & Remotes & "'"
    'You can inspect the string in the immediate window
    Debug.Print(Remotes)