The SqlCommandBuilder class batch update of excel or CSV data, csv Batch Transfer to excel

The SqlCommandBuilder class batch update of excel or CSV data, csv Batch Transfer to excel

When you operate data in batchesUpdate table1 from table2This method is the most concise.
But when you use this method to update massive data from an excel or CSV file, is the performance good? How many strings are concatenated. Does big data need to be updated by group?
I don't want to check the speed of various methods, because I personally agree with the following methods. You are welcome to criticize and correct them.
The main classes I need to use are:SqlCommandBuilder.

/// <Param name = "table"> new data of the able to be updated </param> /// <param name = "TableName"> name of the database table to be updated </param> /// <param name = "primaryKeyName"> the primary key name of the database table to be updated </param> /// <param name = "columnsName"> corresponds the column name set for the updated column </param> // <param name = "limitColumns"> specifies the condition string that must be specified in the SQL WHERE condition, can be empty. </Param> /// <param name = "onceUpdateNumber"> Number of rows processed each round-trip </param> /// <returns> return the number of rows updated </returns> public static int Update (DataTable table, string TableName, string primaryKeyName, string [] columnsName, string limitWhere, int onceUpdateNumber) {if (string. isNullOrEmpty (TableName) return 0; if (string. isNullOrEmpty (primaryKeyName) return 0; if (columnsName = null | columnsName. length <= 0) return 0; DataSet ds = new DataSet (); ds. tables. add (table); int result = 0; using (SqlConnection sqlconn = new SqlConnection (SqlHelper. connString) {sqlconn. open (); // use the enhanced read/write lock transaction SqlTransaction tran = sqlconn. beginTransaction (IsolationLevel. readCommitted); try {foreach (DataRow dr in ds. tables [0]. rows) {// all Rows are set to change status dr. setModified ();} // locate the target table SqlCommand cmd = new SqlCommand (string. format ("select * From {0} where {1} ", TableName, limitWhere), sqlconn, tran); SqlDataAdapter da = new SqlDataAdapter (cmd); SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder (da); da. acceptChangesDuringUpdate = false; string columnsUpdateSql = ""; SqlParameter [] paras = new SqlParameter [columnsName. length]; // the parameter of the column to be updated is "@ + column name" for (int I = 0; I <columnsName. length; I ++) {// name of the column to be updated and Its Parameter Value columnsUpdateSq L + = ("[" + columnsName [I] + "]" + "= @" + columnsName [I] + ","); paras [I] = new SqlParameter ("@" + columnsName [I], columnsName [I]);} if (! String. isNullOrEmpty (columnsUpdateSql) {// remove the last "," columnsUpdateSql = columnsUpdateSql. remove (columnsUpdateSql. length-1);} // where Condition Statement string limitSql = ("[" + primaryKeyName + "]" + "= @" + primaryKeyName) is generated here ); sqlCommand updateCmd = new SqlCommand (string. format ("UPDATE [{0}] SET {1} WHERE {2}", TableName, columnsUpdateSql, limitSql); // do not modify the source DataTable updateCmd. updatedRowSource = UpdateRowSource. none; da. updateCommand = updateCmd; da. updateCommand. parameters. addRange (paras); da. updateCommand. parameters. add ("@" + primaryKeyName, primaryKeyName); // The number of rows processed each round-trip is da. updateBatchSize = onceUpdateNumber; result = da. update (ds, TableName); ds. acceptChanges (); tran. commit ();} catch {tran. rollback ();} finally {sqlconn. dispose (); sqlconn. close () ;}} return result ;}

Note:Parameters to be passed in this method, including the primary key name and column name, should correspond to the actual name of the database.
You do not need to pass in the specified where condition. If you want to pass in, you only need to pass in: Name = "chamy" or Name = "jundy" without adding "where" or other characters, the restriction of the primary key cannot be passed in here. You only need to upload the name in the parameter "primary key name.

The above is the SqlCommandBuilder class for you to update excel or CSV data in batches, I hope it will help you learn.