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.

You can only fill from one connection. So in the first instance you need two DataTables. If you want to combine them into one DataTable, you'll need to handle that yourself. — Jonathan Willcock

Pranay Rana Pranay Rana · Accepted Answer · 2017-09-13T12:21:16

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);
}

Fill one table with multiple OledbConnections C#

1 Answers