When you are importing Excel data recently, verify that the data is duplicated:
1. Do you want to verify that the Excel data itself is duplicated?
2. Is the data in Excel duplicated with the data in the database?
First, the way to verify that data in Excel is duplicated is:
1. Filter the data in table using the SELECT statement (here is a little, refer to two).
2, use for loop to manually check, the code is as follows:
#region record repeating columns in Excel///<summary>//Record repeating columns in Excel///</summary>//< ;p Aram name= "DT" > table to get repeating columns </param>//<returns> prompt repeating information </returns> private string getdist Incttable (DataTable dt) {//datatable dtclone = dt; This is wrong, because in this case, the modification of the Dtclone and the 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 + = "+" + (i + 1). ToString () + "line \ r \ n"; Break }}} return repeatexcel; } #endregion
Small bet
Clone should be modified to copy
Second, check whether the data in Excel is duplicated with the data in the database are as follows:
1. Iterate through the table and verify the duplicates in the database with each of the data.
This applies to tables where there is less data (less than 100), and the database has a large comparison. Because this way to compare a piece of data, you have to connect to the database, and execute queries, very time-consuming.
Roughly tested, Excel has 2000 data, only in the database query, consumes 7 minutes 40 seconds is 4601000 milliseconds (ms), about one data time: 2300.5 milliseconds
In fact, the use of 2 to import 2000, time-consuming than the way 1 to import 100, less time-consuming.
2, the database to compare the table data to a dataset, traverse table and each of the data, in the dataset to verify the repetition, 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 "; }
The above is the C # Excel data weight and table data weight, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!