你的位置:首页 > ASP.net教程

[ASP.net教程]csharp:Compare two DataTables to rows in one but not the other


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