Csharp: Compare two DataTables to rows in one but not the other, csharpdatatables

Csharp: Compare two DataTables to rows in one but not the other, csharpdatatables

/// <Summary> /// Accounting of book data /// </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", "tu juwen", false, 4500, ""); dt. rows. add (2, "L0002", "Liu Jie", false, 4300, "1972-04-04"); dt. rows. add (3, "L0003", "", false, 4500, "1974-04-04"); dt. rows. add (4, "L0005", "Ningxia", false, 4500, ""); dt. rows. add (6, "L0009", "Jiangdong", true, 5500, "1975-04-04"); dt. rows. add (6, "L0010", "Li Yanyun", true, 9500, ""); dt. rows. add (7, "L0020", "", false, 14500, "1977-04-04"); return dt ;} /// <summary> /// check data 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", "tu juwen", false, 4500, ""); dt. rows. add (11, "L0002", "Liu Jie", false, 4300, "1972-04-04"); dt. rows. add (12, "L0009", "Jiangdong", true, 5500, ""); dt. rows. add (13, "L0010", "Li Yanyun", true, 9500, "1974-04-04"); dt. rows. add (14, "L0020", "", false, 14500, ""); dt. rows. add (15, "L0032", "Xu RuO", false, 4300, ""); dt. rows. add (16, "L0056", "Bao Zhilin", true, 4200, "1977-04-04"); dt. rows. add (17, "L0042", "He Yanhua", false, 4100, ""); dt. rows. add (18, "L0052", "daylily", false, 4400, ""); dt. rows. add (19, "L0012", "avier", true, 5500, ""); dt. rows. add (20, "L0018", "Fu Yiwei", false, 6500, ""); dt. rows. add (21, "L0028", "Li Shimin", 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> /// comparison result ///// </summary> /// <param name = "sender"> </param> // <param name = "e"> </param> private void button#click (object sender, eventArgs e) {try {DataTable datadiff1 = new DataTable (); DataTable datadiff2 = new DataTable (); DataTable dataOverage = new DataTable (); // able dataInventoryLoss = new DataTable (); // able ableable1 = 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 variable tab = qry1.0000t (qry2); // DataTable dtMisMatch = (from a in datatable1.AsEnumerable () // join AB in each tab on a ["empno"]. toString () equals AB. mobileNo // select ). copyToDataTable (); // detect row deletes-a row is in datatable1 blocks t missing from datatable2 // var tables tab1 = qry1.t T (qry2 ); // dataInventoryLoss = (from a in datatable1.AsEnumerable () // join AB in tables tab1 on a ["empno"]. toString () equals AB. mobileNo // select ). copyToDataTable (); // detect row inserts-a row is in datatable2 blocks t missing from datatable1 // var tables tab2 = qry2.Except (qry1 ); // dataOverage = (from a in datatable2.AsEnumerable () // join AB in exceptAB2 on a ["empno"]. toString () equals AB. mobileNo // select ). 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> // compare two ableablecompare data (with the same structure) /// tu juwen /// 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 ableable2, 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 blocks t missing from datatable2 var blocks tab1 = qry1.t T (qry2); dataInventoryLoss = (from a in datatable1.AsEnumerable () join AB in each tab1 on a [keyField]. toString () equals AB. idNo select ). copyToDataTable (); // detect row inserts-a row is in datatable2 blocks t missing from datatable1 var blocks tab2 = qry2.t T (qry1); dataOverage = (from a in datatable2.AsEnumerable () join AB in each tab2 on a [keyField]. toString () equals AB. idNo select ). copyToDataTable () ;}/// <summary> /// compare two DataTableCompare data (with the same structure) /// Source: http://www.cnblogs.com/houlinbo/archive/2010/02/10/1667189.html /// </Summary> /// <param name = "dt1"> DataTable from the database </param> /// <param name = "dt2"> from the file dataTable </param> /// <param name = "keyField"> key field names to be compared </param> /// <param name = "keyid"> do not need to be compared field name id </param> // <param name = "dtRetAdd"> add data (data in dt2) </param> /// <param name = "dtRetDif1"> different data (data in the database) </param> /// <param name = "dtRetDif2"> different data (data in dt2, modified) </param> /// <param name = "dtRetDel"> deleted data (data in dt2) </param> Public static void compareable able (DataTable dt1, DataTable dt2, string keyField, string keyid, out DataTable dtRetAdd, out DataTable dtRetDif1, out DataTable dtRetDif2, out DataTable dtRetDel) {// copy table structure dtRetDel = dt1.Clone () for three tables; dtRetAdd = dtRetDel. clone (); dtRetDif1 = dtRetDel. clone (); dtRetDif2 = dtRetDel. clone (); int colCount = dt1.Columns. count; DataView dv1 = dt1.DefaultView; DataView dv2 = Dt2.DefaultView; // use the first table as the reference to see if the second table is modified or foreach is deleted (DataRowView dr1 in dv1) {dv2.RowFilter = keyField + "= '" + dr1 [keyField]. toString () + "'"; if (dv2.Count> 0) {if (! CompareUpdate (dr1, dv2 [0]) // compare whether there are different {dtRetDif1.Rows. add (dr1.Row. itemArray); // dtRetDif2.Rows before modification. add (dv2 [0]. row. itemArray); // after modification, dtRetDif2.Rows [dtRetDif2.Rows. count-1] [keyid] = dr1.Row [keyid]; // assign the ID to the table from the file because all its IDs are = 0 continue ;}} else {// dtRetDel that has been deleted. rows. add (dr1.Row. itemArray) ;}/// use the first table as a reference to check whether the record is a newly added dv2.RowFilter = ""; // clear the condition foreach (DataRowView dr2 in dv2) {dv1.RowFilter = keyField + "= '" + Dr2 [keyField]. toString () + "'"; if (dv1.Count = 0) {// the newly added 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) {// as long as one item in the row is different, the entire row is different. You do not need to compare other objects val1; object val2; for (int I = 1; I <dr1.Row. itemArray. leng Th; I ++) {val1 = dr1 [I]; val2 = dr2 [I]; if (! Val1.Equals (val2) {return false ;}} return true ;}