Try This
private DataTable GetSqlTable()
{
DataTable dt = new DataTable();
DataColumn idColumn = new DataColumn("id", typeof(string));
DataColumn nameColumn = new DataColumn("name", typeof(string));
DataColumn addressColumn = new DataColumn("address", typeof(string));
dt.Columns.Add(idColumn);
dt.Columns.Add(nameColumn);
dt.Columns.Add(addressColumn);
DataRow row = dt.NewRow();
row[idColumn.ColumnName] = "1";
row[nameColumn.ColumnName] = "c";
row[addressColumn.ColumnName] = "c1";
dt.Rows.Add(row);
row = dt.NewRow();
row[idColumn.ColumnName] = "5";
row[nameColumn.ColumnName] = "h";
row[addressColumn.ColumnName] = "h1";
dt.Rows.Add(row);
return dt; //for testing, Instead connecting to database, I created the datatable in code-behind dynamically.
}
private DataTable ReadExcelTable()
{
string filePath = Server.MapPath("upload/test.xls");
string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", filePath);
DataTable dt = null;
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
// Cities$ comes from the name of the worksheet
command.CommandText = "SELECT * FROM [Sheet1$]";
connection.Open();
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = command;
DataSet ds = new DataSet();
adapter.Fill(ds);
dt = ds.Tables[0];
}
}
}
return dt;
}
private void CheckInsertAndUpdateColumnOfExcel()
{
DataTable excelTable = ReadExcelTable();
DataTable sqlTable = GetSqlTable();
DataRow[] excelRowsList = excelTable.Select();
DataRow[] sqlTableList = sqlTable.Select();
string[] oldSqlIdList = new string[sqlTableList.Count()];
int index = 0;
foreach (DataRow row in sqlTableList)
{
oldSqlIdList[index] = row[0].ToString();
index++;
}
var list = from x in excelRowsList
join s in sqlTableList on x[0].ToString() equals s[0].ToString()
select x;
var list2 = from x in excelRowsList
where !oldSqlIdList.Contains(x[0].ToString())
select x;
DataRow[] updateColumn = list.ToArray(); //get the update column
DataRow[] insertColumn = list2.ToArray(); //get the insert column
List<string> updateIdList = new List<string>();
List<string> insertIdList = new List<string>();
foreach (DataRow row in updateColumn)
{
updateIdList.Add(row[0].ToString());
}
foreach (DataRow row in insertColumn)
{
insertIdList.Add(row[0].ToString());
}
Response.Write("the rows id need to update are " + string.Join(",", updateIdList.ToArray()));
Response.Write("<br/>");
Response.Write("the rows id need to insert are " + string.Join(",", insertIdList.ToArray()));
}
protected void Button1_Click(object sender, EventArgs e)
{
CheckInsertAndUpdateColumnOfExcel();
}