A feature of the latest project is to collect and update records of saleable goods in real time. The information of saleable goods comes from the interface of another sales system. Generally, there are about 80 thousand records of saleable items. The collected data does not provide a primary key. It is identified based on a series of fields. Many fields are null values. (So primary key comparison is not possible) The business rule for collection and storage is: from the collected data against the database, the collected data exists and the database does not exist in the product, it is a newly added product record and needs to be added to the database. If the database exists but the collected data does not exist, it is a sold product and needs to be deleted from the database. Therefore, I checked a lot of xdjm code and provided several solutions. It is a pity that the data volume is small. When the actual data is used, the comparison time cannot be tolerated. Therefore, you can only think hard and write out a method for your research. If any bug is found, please let us know in time.
-10 PS
I am very grateful to my friends who have been paying attention to this post. The original code is not perfect and has never been updated since it was modified. I have changed my mind and reconstructed the Code. If any error is found, please leave a message.
The new code solves several bugs and accepts suggestions from some friends. If this code is not tested, check it and use it.
View Code 1 // <summary>
2 // compare two able data (with the same structure and different field names)
3 /// </summary>
4 /// <param name = "dtDest"> DataTable from the database </param>
5 // <param name = "dtSrc"> DataTable from the file </param>
6 /// <param name = "dtRetAdd"> add data (data in dt2) </param>
7 // <param name = "dtRetDel"> deleted data (data in dt2) </param>
8 /// <param name = "srcKeyFields"> source key field name </param>
9 // <param name = "destKeyFields"> target key field name, corresponding to the source key field name </param>
10 public static void CompareDt (DataTable dtSrc, DataTable dtDest, out DataTable dtRetAdd, out DataTable dtRetDel, string srcKeyFields, string destKeyFields)
11 {
12 // exit if either the source record set or the target record set is null.
13 if (dtSrc = null | dtDest = null)
14 {
15 dtRetDel = null;
16 dtRetAdd = null;
17 return;
18}
19 // define the returned record table
20 dtRetDel = dtSrc. Clone ();
21 dtRetAdd = dtRetDel. Clone ();
22 // exit if the reference column is empty
23 if (string. IsNullOrEmpty (srcKeyFields) | string. IsNullOrEmpty (destKeyFields ))
24 return;
25 // obtain the reference column list
26 string [] srcFields = srcKeyFields. Split (','); // array of column names
27 string [] destFields = destKeyFields. Split (','); // array of column names
28 // exit if the number of reference columns is inconsistent
29 if (srcFields. Length! = DestFields. Length)
30 return;
31 // sort the source table and target table by reference column
32 DataRow [] drSrc = dtSrc. Select ("", srcKeyFields );
33 DataRow [] drDest = dtDest. Select ("", destKeyFields );
34 // define the length of the source table and target table
35 int iSrcCount = drSrc. Length;
36 int iDestCount = drDest. Length;
37 // if the source table is empty, all the target tables are added to the delete queue and return
38 if (iSrcCount = 0)
39 {
40 foreach (DataRow row in drDest)
41 {
42 dtRetDel. Rows. Add (row. ItemArray );
43}
44 return;
45}
46 // if the target table is empty, all the source tables are added to the new queue and return
47 if (iDestCount = 0)
48 {
49 foreach (DataRow row in drSrc)
50 {
51 dtRetAdd. Rows. Add (row. ItemArray );
52}
53 return;
54}
55 // define the source table and target table pointer
56 int iSrc = 0;
57 int iDest = 0;
58 // start cyclic comparison
59 while (iSrc <iSrcCount & iDest <iDestCount)
60 {
61 // define the column comparison result
62 int result = 0;
63 object oSrc;
64 object oDest;
65 // recyclically compare column values
66 for (int colIndex = 0; colIndex <srcFields. Length; colIndex ++)
67 {
68 // obtain the column Value
69 oSrc = drSrc [iSrc] [srcFields [colIndex];
70 oDest = drDest [iDest] [destFields [colIndex];
71 // compare column values. Exit the loop if they are not equal
72 if (oSrc = DBNull. Value)
73 {
74 result = oDest = DBNull. Value? 0:-1;
75}
76 else
77 {
78 result = oDest = DBNull. Value? 1: string. Compare (oSrc. ToString (), oDest. ToString (), false );
79}
80 if (result! = 0)
81 break;
82}
83 // check the row comparison result
84 switch (result)
85 {
86 // if the source table is small, add the source table row to the new queue and move the source table pointer.
87 case-1:
88 dtRetAdd. Rows. Add (drSrc [iSrc]. ItemArray );
89 iSrc ++;
90 break;
91 //// move the pointer to the same table at the same time
92 case 0:
93 iSrc ++;
94 iDest ++;
95 break;
96 /// if the target table is small, add the row of the target table to the delete queue and move the target table pointer.
97 case 1:
98 dtRetDel. Rows. Add (drDest [iDest]. ItemArray );
99 iDest ++;
100 break;
Default 101:
102 break;
103}
104}
105 // when the source table reaches the last entry and the target table does not arrive, all the remaining rows in the target table are added to the delete queue.
106 if (iDest <iDestCount)
107 {
108 for (int index = iDest; index <iDestCount; index ++)
109 {
110 dtRetDel. Rows. Add (drDest [index]. ItemArray );
111}
112}
113 // when the target table reaches the last entry and the source table does not arrive, all the remaining rows in the source table are added to the new queue.
114 else if (iSrc <iSrcCount)
115 {
116 for (int index = iSrc; index <iSrcCount; index ++)
117 {
118 dtRetAdd. Rows. Add (drSrc [index]. ItemArray );
119}
120}
121
