csharp:Compare two DataTables to rows in one but not the other，csharpdatatables
/// <summary>
/// 賬面數據 Accounting
/// </summary>
/// <returns></returns>
DataTable setDataAccounting()
{
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("empno", typeof(string));
dt.Columns.Add("empname", typeof(string));
dt.Columns.Add("sex", typeof(bool));
dt.Columns.Add("wage", typeof(decimal));
dt.Columns.Add("birthday", typeof(DateTime));
dt.Rows.Add(1, "L0001", "塗聚文",false,4500,"1970-03-04");
dt.Rows.Add(2, "L0002", "劉傑", false, 4300, "1972-04-04");
dt.Rows.Add(3, "L0003", "宋承憲", false, 4500, "1974-04-04");
dt.Rows.Add(4, "L0005", "寧夏", false, 4500, "1973-04-04");
dt.Rows.Add(6, "L0009", "江東", true, 5500, "1975-04-04");
dt.Rows.Add(6, "L0010", "李燕雲", true, 9500, "1976-04-04");
dt.Rows.Add(7, "L0020", "趙雅芝", false, 14500, "1977-04-04");
return dt;
}
/// <summary>
/// 盤點數據 Inventory
/// </summary>
/// <returns></returns>
DataTable setDataInventory()
{
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("empno", typeof(string));
dt.Columns.Add("empname", typeof(string));
dt.Columns.Add("sex", typeof(bool));
dt.Columns.Add("wage", typeof(decimal));
dt.Columns.Add("birthday", typeof(DateTime));
dt.Rows.Add(10, "L0001", "塗聚文", false, 4500, "1970-03-04");
dt.Rows.Add(11, "L0002", "劉傑", false, 4300, "1972-04-04");
dt.Rows.Add(12, "L0009", "江東", true, 5500, "1973-04-04");
dt.Rows.Add(13, "L0010", "李燕雲", true, 9500, "1974-04-04");
dt.Rows.Add(14, "L0020", "趙雅芝", false, 14500, "1975-04-04");
dt.Rows.Add(15, "L0032", "徐若萱", false, 4300, "1976-04-04");
dt.Rows.Add(16, "L0056", "保芝林", true, 4200, "1977-04-04");
dt.Rows.Add(17, "L0042", "何燕華", false, 4100, "1978-04-04");
dt.Rows.Add(18, "L0052", "黃花菜", false, 4400, "1979-04-04");
dt.Rows.Add(19, "L0012", "艾薇兒", true, 5500, "1982-04-04");
dt.Rows.Add(20, "L0018", "傅藝偉", false, 6500, "1932-04-04");
dt.Rows.Add(21, "L0028", "李世民", false, 9500, "1992-04-04");
return dt;
}
/// <summary>
///
/// </summary>
public Form1()
{
InitializeComponent();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
this.dataGridViewAccounting.DataSource = setDataAccounting();
this.dataGridViewInventory.DataSource = setDataInventory();
}
/// <summary>
/// 比結果
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
try
{
DataTable datadiff1 = new DataTable();
DataTable datadiff2 = new DataTable();
DataTable dataOverage = new DataTable();//盤盈
DataTable dataInventoryLoss = new DataTable();//盤虧
DataTable datatable1 = setDataAccounting();
DataTable datatable2 = setDataInventory();
#region
//var qry1 = datatable1.AsEnumerable().Select(a => new { MobileNo = a["empno"].ToString() });
//var qry2 = datatable2.AsEnumerable().Select(b => new { MobileNo = b["empno"].ToString() });
//var exceptAB = qry1.Except(qry2);
////
//DataTable dtMisMatch = (from a in datatable1.AsEnumerable()
// join ab in exceptAB on a["empno"].ToString() equals ab.MobileNo
// select a).CopyToDataTable();
////detect row deletes - a row is in datatable1 except missing from datatable2
//var exceptAB1 = qry1.Except(qry2);
// dataInventoryLoss= (from a in datatable1.AsEnumerable()
// join ab in exceptAB1 on a["empno"].ToString() equals ab.MobileNo
// select a).CopyToDataTable();
////detect row inserts - a row is in datatable2 except missing from datatable1
//var exceptAB2 = qry2.Except(qry1);
// dataOverage = (from a in datatable2.AsEnumerable()
// join ab in exceptAB2 on a["empno"].ToString() equals ab.MobileNo
// select a).CopyToDataTable();
#endregion
//CompareDataTable(setDataAccounting(), setDataInventory(), "empno","id", out dataOverage, out datadiff1, out datadiff2, out dataInventoryLoss);
CompareLinQDataTable(setDataAccounting(), setDataInventory(), "empno", out dataOverage, out dataInventoryLoss);
this.dataGridViewOverage.DataSource =dataOverage;
this.dataGridViewInventoryLoss.DataSource = dataInventoryLoss;
this.dataGridViewAdd.DataSource =datadiff1 ;
this.dataGridViewDel.DataSource = datadiff2;
}
catch (Exception ex)
{
ex.Message.ToString();
}
}
/// <summary>
/// 比較兩個DataTableCompare數據（結構相同）
/// 塗聚文
/// http://www.codeproject.com/Tips/344792/Compare-two-datatable-using-LINQ-Query
/// </summary>
/// <param name="datatable1"></param>
/// <param name="datatable2"></param>
/// <param name="keyField"></param>
/// <param name="dataOverage"></param>
/// <param name="dataInventoryLoss"></param>
public static void CompareLinQDataTable(DataTable datatable1, DataTable datatable2, string keyField,out DataTable dataOverage,out DataTable dataInventoryLoss)
{
var qry1 = datatable1.AsEnumerable().Select(a => new { IdNo = a[keyField].ToString() });
var qry2 = datatable2.AsEnumerable().Select(b => new { IdNo = b[keyField].ToString() });
//detect row deletes - a row is in datatable1 except missing from datatable2
var exceptAB1 = qry1.Except(qry2);
dataInventoryLoss = (from a in datatable1.AsEnumerable()
join ab in exceptAB1 on a[keyField].ToString() equals ab.IdNo
select a).CopyToDataTable();
//detect row inserts - a row is in datatable2 except missing from datatable1
var exceptAB2 = qry2.Except(qry1);
dataOverage = (from a in datatable2.AsEnumerable()
join ab in exceptAB2 on a[keyField].ToString() equals ab.IdNo
select a).CopyToDataTable();
}
/// <summary>
/// 比較兩個DataTableCompare數據（結構相同）
/// 來源於:http://www.cnblogs.com/houlinbo/archive/2010/02/10/1667189.html
/// </summary>
/// <param name="dt1">來自數據庫的DataTable</param>
/// <param name="dt2">來自文件的DataTable</param>
/// <param name="keyField">要比較的關鍵字段名</param>
/// <param name="keyid">不需要比較的字段名id</param>
/// <param name="dtRetAdd">新增數據（dt2中的數據）</param>
/// <param name="dtRetDif1">不同的數據（數據庫中的數據）</param>
/// <param name="dtRetDif2">不同的數據（dt2中的數據,修改過的）</param>
/// <param name="dtRetDel">刪除的數據（dt2中的數據）</param>
public static void CompareDataTable(DataTable dt1, DataTable dt2, string keyField,string keyid, out DataTable dtRetAdd, out DataTable dtRetDif1, out DataTable dtRetDif2,out DataTable dtRetDel)
{
//為三個表拷貝表結構
dtRetDel = dt1.Clone();
dtRetAdd = dtRetDel.Clone();
dtRetDif1 = dtRetDel.Clone();
dtRetDif2 = dtRetDel.Clone();
int colCount = dt1.Columns.Count;
DataView dv1 = dt1.DefaultView;
DataView dv2 = dt2.DefaultView;
//先以第一個表為參照，看第二個表是修改了還是刪除了
foreach (DataRowView dr1 in dv1)
{
dv2.RowFilter = keyField + " = '" + dr1[keyField].ToString() + "'";
if (dv2.Count > 0)
{
if (!CompareUpdate(dr1, dv2[0]))//比較是否有不同的
{
dtRetDif1.Rows.Add(dr1.Row.ItemArray);//修改前
dtRetDif2.Rows.Add(dv2[0].Row.ItemArray);//修改後
dtRetDif2.Rows[dtRetDif2.Rows.Count - 1][keyid] = dr1.Row[keyid];//將ID賦給來自文件的表，因為它的ID全部==0
continue;
}
}
else
{
//已經被刪除的
dtRetDel.Rows.Add(dr1.Row.ItemArray);
}
}
//以第一個表為參照，看記錄是否是新增的
dv2.RowFilter = "";//清空條件
foreach (DataRowView dr2 in dv2)
{
dv1.RowFilter = keyField + " = '" + dr2[keyField].ToString() + "'";
if (dv1.Count == 0)
{
//新增的
dtRetAdd.Rows.Add(dr2.Row.ItemArray);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="dr1"></param>
/// <param name="dr2"></param>
/// <returns></returns>
private static bool CompareUpdate(DataRowView dr1, DataRowView dr2)
{
//行裡只要有一項不一樣，整個行就不一樣,無需比較其它
object val1;
object val2;
for (int i = 1; i < dr1.Row.ItemArray.Length; i++)
{
val1 = dr1[i];
val2 = dr2[i];
if (!val1.Equals(val2))
{
return false;
}
}
return true;
}
