C # Excel verifies that data is duplicated and the table verifies that data is duplicated

Recently, when you are importing Excel data, verify that the data is duplicated:

1. Do you want to verify that the Excel data itself is duplicated?

2. Does the data in Excel duplicate the data in the database?

First, verify that the data in Excel are repeated in the following ways:

1. Use the SELECT statement to filter the data in the table (here, refer to the second).

2, use for loop to check manually, the code is as follows:

#region record repeating columns in Excel///<summary>///records duplicate columns in Excel///</summary>///<param name= "DT" &G t; needs to get repeating column table </param>///url:http://www.bianceng.cn/programming/csharp/201410/45747.htm///<returns> Prompt for duplicate information </returns> private string getdistincttable (DataTable dt) {//datatable dtclone = dt; This is the wrong way, because  
      While modifying the Dtclone, DT will also be modified. DataTable dtclone = dt.  
      Clone ();; String Vssubacctno = String.  
      Empty; String Vsacctno = String.  
      Empty; String repeatexcel = String.  
      Empty; String vstransdate = String.  
      Empty; for (int i = dtclone.rows.count-1 i >= 0; i--) {Vssubacctno = dtclone.rows[i][4]. ToString ().  
          Trim (); Vsacctno = dtclone.rows[i][1]. ToString ().  
          Trim (); Vstransdate = Dtclone.rows[i][8]. ToString ().  
          Trim (); Dtclone.rows[i].  
          Delete ();  
          Dtclone.acceptchanges ();  
    for (int j = dtclone.rows.count-1 J >= 0; j--)      {if (Vssubacctno = = Dtclone.rows[j][4]. ToString (). Trim () && Vsacctno = = dtclone.rows[j][1]. ToString (). Trim () && vstransdate = = Dtclone.rows[j][8]. ToString (). Trim ()) {//If repeated, record Repeatexcel + = "First" + (i + 1).  
                  ToString () + "line \ r \ n";  
              Break  
  }} return repeatexcel; } #endregion

Second, verify that the data in Excel and the data in the database duplication of ways are:

1, traversing the table and each of the data, in the database test repeat.

This approach applies to fewer (100) of the data in the table, while the database is large in comparison to the table. Because this way to each contrast a data, to connect the database, and execute the query, very time-consuming.

Roughly tested, Excel has 2000 data, only query in the database, it consumes 7 minutes 40 seconds, 4601000 milliseconds (ms), about one data time-consuming: 2300.5 milliseconds

In fact, the use of the Way 2 import 2000, time-consuming and more than Mode 1 import 100, less time-consuming.

2, in the database to compare the table data to a dataset, traversing the table and each of these data, in the dataset test duplicates, the code is as follows:

strtemp = "acctno= '" + obzh.tostring () + "' and transdate= '" + obrq.tostring () + "' and  subacctno= '" + obdfzh.tostring () + "'";  
          Rowstemp = Dstemp.tables[0]. Select (strtemp);  
          if (rowstemp.length>0)  
          {  
              //if repeated, record Repeatdj +  
              = "" "+ v.tostring () +" line \ r \ n ";  
          }