. NET to update data in batches (no data is added)

Method One: Use SqlBulkCopy to implement bulk update or add data.

SqlBulkCopy class can only be used to bulk insert data into the database, if the data table set the primary key, duplicate data will be error, if not set the primary key, then the same data will be added, resulting in data duplication. There are two scenarios where you can implement a scenario that allows you to update data to a data table in bulk. Update the data if it exists, or add a new data if it does not exist

Both scenarios require the addition of a new table for temporary storage of data, and if there are two tables Batchtabletemp and batchtable, the SqlBulkCopy class first batches the data into batchtabletemp. The data that does not exist in the batchtable table is then added to the Batchtable table, and the existing data is updated to the Batchtable table

Scenario One: After executing the sqlbulkcopy batch to add data to Batchtabletemp, execute the stored procedure, add insert to the data that does not exist in the Batchtable table, the existing data updates update

SqlHelper Code:

//<summary>//returns the number of rows affected by the operation//</summary> Public Static intExcutenonquery (CommandType Cmdtype,stringCmdtext, sqlparameter[] cmdparms) {SqlCommand cmd=Preparesqlcommand (Cmdtype, Cmdtext, cmdparms); intValue =cmd.    ExecuteNonQuery (); Cmd.    Parameters.clear (); Cmd.    Connection.close (); returnvalue;}/// <summary>///Bulk Add Data/// </summary>/// <param name= "DT" ></param>/// <returns></returns> Public Static BOOLexcutenonquery (DataTable dt) {SqlConnection connection=NewSqlConnection (connstring); Connection.    Open (); SqlBulkCopy SqlBulkCopy=NewSqlBulkCopy (connection); SqlBulkCopy. Bulkcopytimeout= -;//the number of seconds allowed for the operation to complete before timing outSqlBulkCopy. batchsize = dt. Rows.Count;//number of rows in each batchSqlBulkCopy. DestinationTableName = dt. TableName;//name of the destination table on the server     for(inti =0; i < dt. Columns.count; i++) {SqlBulkCopy.  Columnmappings.add (i, I); //mappings define relationships between columns in the data source and columns in the target table} sqlbulkcopy.  WriteToServer (DT); //uploading DataTable data to a data tableconnection.    Close (); return true;}

C # executes Excutenonquery (), bulk adds to Batchtabletemp, and then executes the stored procedure Code

DataTable table = getdatatable ("batchtabletemp"// Gets the DataTable to be updated BOOL value = sqlhelper.excutenonquery (table); if (value) {    "insertorupdate"null);}

To update the batchtabletemp synchronously to the stored procedure code in the Batchtable table:

ALTER procedure [dbo].[insertorupdate] as        begin        UpdatebatchtableSetBatchtable.column1=Batchtabletemp.column1, Batchtable.column2=Batchtabletemp.column2, Batchtable.column3=Batchtabletemp.column3, Batchtable.column4=Batchtabletemp.column4, Batchtable.column5=Batchtabletemp.column5 frombatchtable,batchtabletempwhereBatchtable.gradation=batchtabletemp.gradationInsert  intobatchtableSelect *  frombatchtabletempwhere  not exists             (SelectGradation fromBatchtablewhereBatchtable.gradation=batchtabletemp.gradation)DeletebatchtabletempEnd

Programme II: To Be Continued

Method Two: Bulk update (or insert) data using table-valued parameters (TVPs)

Cond

. NET to update data in batches (no data is added)