C # Excel data verification and table data verification

When importing Excel Data recently, check whether the data is repeated:
1. Check whether Excel Data is repeated?

2. Is the data in Excel duplicated with the data in the database?

1. Check whether data in Excel is repeated:
1. Use the SELECT statement to filter data in the table (For details, refer to section 2 ).
2. Use the for loop for manual check. The Code is as follows:

# Region record duplicate columns in Excel /// <summary> // record duplicate columns in Excel /// </Summary> /// <Param name = "DT"> required obtain the table with duplicate columns </param> /// <returns> indicates duplicate information </returns> private string getdistincttable (datatable DT) {// datatable dtclone = DT; this method is incorrect. In this case, DT will be modified when you modify dtclone. 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 () {// repeatexcel + = "" + (I + 1 ). tostring () + "Row \ r \ n"; break ;}}return repeatexcel ;}# endregion

2. Check whether the Excel Data is duplicated with the data in the database:
1. traverse the table and check the duplicate data entries in the database.
This method is suitable for tables with less data (less than 100), while databases need to compare large tables. This method requires connecting to the database for every comparison of a piece of data and performing queries, which is very time-consuming.
A general test of the, Excel has 2000 pieces of data, only query in the database, it consumes 7 minutes 40 seconds is 4601000 milliseconds (MS), about a piece of data time: 2300.5 milliseconds
In fact, if you use method 2 to import 2000 entries, it takes less time than method 1 to import 100 entries.
2. Retrieve the table data to be compared in the database to a dataset, traverse the table, and check the duplicate data in the dataset. 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) {// repeatdj + = "th" + v. tostring () + "Row \ r \ n ";}