0

I have 2 data sources from different source types, like:

Dim connSql As String = "Data Source=sql_src;Initial Catalog=my_db;Persist Security Info=True;User ID=usr;Password=pwd;"
Dim connOle As String = "Provider=IBMDA400;Data source=src;User Id=usr;Password=pwd"

dim qrySql as String = "Select uniqueID, name From tblSql"
dim qryOle as String = "Select name, ID From tblOle"

I place them into DataTables using functions like this (mostly for reference):

Public Shared Function sqlQryDT(qry As String, conn As String) As DataTable
    Dim dt As New DataTable
    Dim sqlconn As New SqlClient.SqlConnection(conn)
    sqlconn.Open()
    Dim adapter As New SqlClient.SqlDataAdapter(qry, sqlconn)
    Try
        adapter.Fill(dt)
    Catch ex As Exception
    Finally
        sqlconn.Close()
    End Try
    Return dt
End Function

Public Shared Function oleQryDT(qry As String, conn As String) As DataTable
    Dim dt As New DataTable
    Dim cn As New OleDb.OleDbConnection(conn)
    Dim cmd As New OleDb.OleDbCommand("", cn)
    Dim da As New OleDb.OleDbDataAdapter(cmd)
    cmd.CommandText = qry
    Try
        cn.Open()
        da.Fill(dt)
    Catch ex As Exception
    Finally
        cn.Close()
    End Try
    Return dt
End Function

Dim dtSql as DataTable = sqlQryDT(qrySql, connSQL)
Dim dtOle as DataTable = oleQryDT(qryOle , connOle)

My goal is to have a DataTable consisting of uniqueID,name,ID

(Edit) If I could do an SQL query it would look like SELECT * FROM dtSql JOIN dtOle ON dtSql.name = dtOle.name

I have tried using a DataRelation by placing the tables into a DataSet and adding a relationship on name, but since names may not be unique, this fails.

I have also tried using LINQ, but that always returns IEnumerable(System.Collections.Generic) and I can't find an easy way to place the contents into a DataTable, except by iterating through every item. And, the code is messy when there are a large number of columns.

I feel like there should be a way to do this without using a For Each loop

I looked at these question, but they didn't fully help me.

How to inner join two already filled DataTables in VB.NET

Merging 2 data tables in vb.net

Community
  • 1
  • 1
Will
  • 156
  • 2
  • 13
  • @436f6465786572 This is from the MSDN documentation `The Merge method is used to merge two DataTable objects that have largely similar schemas.` What I am looking for is a typical SQL Join. My current situation would most likely use a left outer join, but I care more about just being able to do a join. – Will Feb 24 '15 at 21:27
  • @436f6465786572 I figured the terms "Join", "many-to-many" and "DataTable" would imply I'm looking to do joins as expressed in general database queries. I'm updating my question to include a sample query. – Will Feb 25 '15 at 15:45

0 Answers0