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 DataTable
s 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.