CODEWITHSUNDEEP
sqlsql-servervb.netoracle-database
Tmcc
Tmcc

Reputation: 23

Join two queries from different data sources using VB.NET

I have two queries. One is an Oracle query, and one is a SQL Server query.

Oracle Columns: ID, Subject, Course

SQL Server Columns: ID, Recommended Subject, Recommended Course

I would like to join the two queries on ID. I need to find out which IDs have a subject that is not equal to the recommended subject or a course that is not equal to the recommended course. Then, display the results in a GridView.

Here's what I have tried to do so far. I have removed my SQL commands and connection strings.

Dim sConnectionString As String = ConfigurationManager.ConnectionStrings("sqlserver").ConnectionString
Dim sCN As New SqlConnection(sConnectionString)
Dim sCommandWrapper As SqlCommand = New SqlCommand("SQL", sCN)
Dim sDataAdapter As SqlDataAdapter = New SqlDataAdapter
sDataAdapter.SelectCommand = sCommandWrapper

Dim pConnectionString As String = ConfigurationManager.ConnectionStrings("oracle").ConnectionString
Dim pCN As New OleDbConnection(pConnectionString)
Dim pCommandWrapper As OleDbCommand = New OleDbCommand("SQL", pCN)
Dim pDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
pDataAdapter.SelectCommand = pCommandWrapper

Dim stopDS As DataSet = New DataSet()
sDataAdapter.Fill(stopDS, "Recommendations")
pDataAdapter.Fill(stopDS, "Registrations")

Upvotes: 0

Views: 3711

Answers (2)

Alireza
Alireza

Reputation: 5056

I really like Mike's approach, but let me suggest another one and you can choose whichever more suitable for you.

You can set up a linked server to your Oracle database. It is described in this answer. Alternatively you can follow this article.

Then simply join the two tables and get the results:

SELECT * FROM SqlTable s
INNER JOIN OracleServer.OracleDB..OracleTable o ON o.ID = s.ID 
      AND (s.Course != o.[Recommended Course] OR s.Subject != o.[Recommended Subject])

Unfortunately I don't have oracle installed to completely test this myself, but I hope you get in the direction.

Upvotes: 0

Mark
Mark

Reputation: 8160

Since you have your two results in a DataSet, you can define a relationship between the two tables using the DataSet.Relations property:

stopDS.Relations.Add(
    "ID2ID",
    stopDS.Tables("Recommendations").Columns("ID"),
    stopDS.Tables("Registrations").Columns("ID")
)

You can then get the matching rows from either end of the relationship (apologies if I got the relationship the wrong way around!):

Dim rows() As DataRow = stopDS.Tables("Recommendations").Rows(0).GetChildRows("ID2ID")
Dim row As DataRow = stopDS.Tables("Registrations").Rows(0).GetParentRow("ID2ID")
' Can also use .GetParentRows(...) for an array.

Here is a complete example console app:

Module Module1

    Sub Main()

        Dim t1 = New DataTable()
        t1.TableName = "Names"
        t1.Columns.Add("ID", GetType(Integer))
        t1.Columns.Add("Name", GetType(String))

        Dim t2 = New DataTable()
        t2.TableName = "Addresses"
        t2.Columns.Add("ID", GetType(Integer))
        t2.Columns.Add("Address", GetType(String))

        Dim r As DataRow = Nothing

        r = t1.NewRow()
        r("ID") = 1
        r("Name") = "Bob"
        t1.Rows.Add(r)
        r = t1.NewRow()
        r("ID") = 2
        r("Name") = "Joe"
        t1.Rows.Add(r)
        r = t1.NewRow()
        r("ID") = 3
        r("Name") = "Sue"
        t1.Rows.Add(r)

        r = t2.NewRow()
        r("ID") = 1
        r("Address") = "1 Main St"
        t2.Rows.Add(r)
        r = t2.NewRow()
        r("ID") = 3
        r("Address") = "2 Any St"
        t2.Rows.Add(r)

        Dim ds = New DataSet()
        ds.Tables.Add(t1)
        ds.Tables.Add(t2)

        ' Define relationship between the ID columns
        ds.Relations.Add(
            "NameToAddress",
            ds.Tables("Names").Columns("ID"),
            ds.Tables("Addresses").Columns("ID"))

        For Each nameRow In t1.AsEnumerable()
            Console.WriteLine("Name: {0}", nameRow.Field(Of String)("Name"))
            For Each addrRow In nameRow.GetChildRows("NameToAddress")
                Console.WriteLine("--Addr: {0}", addrRow.Field(Of String)("Address"))
            Next
        Next

        Console.WriteLine("==========")

        For Each addrRow In t2.AsEnumerable()
            Console.WriteLine("Addr: {0}", addrRow.Field(Of String)("Address"))
            Dim pr = addrRow.GetParentRow("NameToAddress")
            If pr IsNot Nothing Then
                Console.WriteLine("++Name: {0}", pr.Field(Of String)("Name"))
            End If
            For Each nameRow In addrRow.GetParentRows("NameToAddress")
                Console.WriteLine("--Name: {0}", nameRow.Field(Of String)("Name"))
            Next
        Next

        Console.ReadLine()

    End Sub

End Module

The results are:

Name: Bob
--Addr: 1 Main St
Name: Joe
Name: Sue
--Addr: 2 Any St
==========
Addr: 1 Main St
++Name: Bob
--Name: Bob
Addr: 2 Any St
++Name: Sue
--Name: Sue

Upvotes: 1

Related Questions