Fill one table with multiple OledbConnections C#

Question

I have an Excel file with Column A, Column B, Column C etc. and i have created an OledbConnection

Con1 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\'Excel 12.0;';");

I have an other Access Database file with Column 1, Column B, Column 2 and i have created an other OledbConnection for .mdb file

Con2 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Persist Security Info=True;");   

I have created a DataTable "dt" in c# windowform and filling it using OledbDataAdapter(query, con). My "dt" table has
Column B Column A Column C Column 1 Column 2 Table 1(data) Table 1(data) Table 1(data) Table 2(data) Table 2(data) Con1 Con1 Con1 Con2 Con2

How can i use both connection in OleDbDataAdapter to fill and Which connection i use open() and close(). My both connection are separately working. My Reference column B is in Table 1.

Answer 1

you need to perform merge manually here, try like this

var collection = from t1 in dt1.AsEnumerable()
                 join t2 in dt2.AsEnumerable()
                    on t1["id"] equals t2["id"]
                 select new { Name = t1["id"], Group = t2["Name"] }; 

DataTable result = new DataTable("NameGroups");
result.Columns.Add("ID", typeof(string));
result.Columns.Add("Name", typeof(int));

foreach (var item in collection)
{
   result.Rows.Add(item.ID, item.Name);
}