I'll do that this way:
1) create
DataSet[
^] (
ds
)
//Excel
2) create
OleDbConnection[
^] to Excel file
3) create
OleDbCommand[
^] to grab data from Excel
4) load data into
DataTable[
^] (
dt1
) object via
OleDbDataReader[
^]
5) add
dt1
object to
ds
//SQL Server
6) create
SqlConnection[
^] to SQL database
7) create
SqlCommand[
^] to grab data from SQL server
8) load data into
DataTable
(
dt2
) object via
SqlDataReader[
^]
9) add
dt2
to
ds
//final job
10) join data via Linq:
string sFileName = @"D:\yourExcelFile.xlsx";
string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", sFileName);
string sSqlConn = "enter connection details to SQL server";
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataSet ds = new DataSet();
using (OleDbConnection connection = new OleDbConnection(sConStr))
{
string sql = @"SELECT * FROM [Sheet1$];";
connection.Open();
using(OleDbCommand command = new OleDbCommand(sql, connection))
{
using (OleDbDataReader reader = command.ExecuteReader())
{
dt1.Load(reader);
}
}
ds.Tables.Add(dt1);
command.Dispose();
}
using (SqlConnection connection = new SqlConnection(sSqlConn))
{
string sql = @"SELECT * FROM YourTable";
connection.Open();
using(SqlCommand command = new SqlCommand(sql, connection))
{
using(SqlDbDataReader reader = command.ExecuteReader())
{
dt2.Load(reader);
}
}
ds.Tables.Add(dt2);
command.Dispose();
}
var commonData = (from r1 ds.Tables("dt1").AsEnumerable()
join r2 ds.Tables("dt1").AsEnumerable() on r1.Field<string>("Barcode") equals r1.Field<string>("Barcode"))
.Select(x=>new
{
A = r1.Field<Type>("Name1"),
B = r1.Field<Type>("Name2"),
C = r1.Field<Type>("Name3"),
D = r2.Field<Type>("Name1")
})
.ToList();
foreach(var row in commonData)
{
}
Note: above code has been writen direct from my head, so it can contains errors.
Good luck!