Two methods of C#mysql batch update

Overall update upload speed is slow.

1: Simple insert speed is slightly slower than mysqldataadapter

With Dapper

Configuration file

<?xml version="1.0"encoding="Utf-8"?><configuration> <startup> <supportedruntime version="v4.0"sku=". netframework,version=v4.5.2"/> </startup> <connectionStrings> <add name="sqlConnectionString"connectionstring="server=127.0.0.1;database=mydatabase; user=sa;password=123456; Connect timeout=1000000"/> <!--<add name="mysqlconnectionstring"connectionstring="Database=terminal_info;data source=127.0.0.1; User Id=root; password=123456; charset=utf8;port=3306"/>--> <add name="mysqlconnectionstring"connectionstring="Database=terminal_info;data source=192.168.1.220; User Id=root; Password=root; charset=utf8;port=3306"/> </connectionStrings></configuration>

string connectionString = configurationmanager.connectionstrings["mysqlconnectionstring "]. ToString ();

  Public intUpdate (basetable model) {#regionSQL statementsConst stringsql =@"UPDATE terminal_base SET license_number2 = @license_number2 WHERE license_number = @license _number"; #endregion            using(Mysqlconnection connection =Newmysqlconnection (connectionString)) {                returnconnection.            Execute (SQL, model); }        }

  Public classbasetable { Public stringLicense_number {Get;Set; }//Customer Code         Public stringShop_name {Get;Set; }//Shop Name         Public stringUser_name {Get;Set; }//name         Public stringPhone_number {Get;Set; }//Phone number    }

Update 50,000 data it took 10 minutes. Single-Thread processing

2 with mysqldataadapter upload this pit a lot of, on-line data is also very few, fill is from the current database query to data populated to the dataset, update is to upload the changed dataset to MySQL.

/// <summary>        ///batch update data using Mysqldataadapter/// </summary>        /// <param name= "connectionString" >Database connection String</param>        /// <param name= "table" >Data Sheet</param>         Public voidBatchUpdate (list<basetable>model) {DataTable table=listtodatatable.todatatable (model); Table. TableName="Terminal_base"; Table. Extendedproperties.add ("SQL","Select License_number,license_number2 from Terminal_base"); Mysqlconnection Connection=Newmysqlconnection (connectionString); Connection.            Open (); //Creating a data adapter            stringSqlcommd="Select License_number,license_number2 from Zyzs_terminal_base"; Mysqldataadapter adapter =NewMysqldataadapter (SQLCOMMD, connection); DataSet DS=NewDataSet ();//Create a data setAdapter. Fill (DS,"Terminal_base"); //ds.            Tables.clear (); //The UpdateCommand property for SqlDataAdapter specifies the SQL statement that performs the update operation SELECT * FROM Zyzs_terminal_baseAdapter. UpdateCommand =NewMysqlcommand ("Update terminal_base Set license_number2 = @license_number2 where [email protected]_number", connection); //adding parameters and assigning valuesAdapter. UPDATECOMMAND.PARAMETERS.ADD ("@license_number2", Mysqldbtype.varchar, -,"License_number2"); Mysqlparameter prams_id= Adapter. UPDATECOMMAND.PARAMETERS.ADD ("@license_number", Mysqldbtype.varchar); prams_id. SourceColumn="License_number"; prams_id. SourceVersion=datarowversion.original; //Populating data sets//call the Update method to commit the updated DataSet DS and update the database data synchronouslyadapter. Update (DS,"Terminal_base"); //Transaction.commit (); //mysqlcommand command = connection.            CreateCommand (); //command.commandtimeout = 4000; //command.commandtype = CommandType.Text; //Mysqldataadapter adapter = new Mysqldataadapter (command); //Mysqlcommandbuilder commandbulider = new Mysqlcommandbuilder (adapter); //commandbulider.conflictoption = conflictoption.overwritechanges; //mysqltransaction transaction = null; //Try//{            //connection.            Open (); //transaction = connection.            BeginTransaction (); //    //set the number of processing bars per batch update//adapter.            updatebatchsize = 1000; //    //set things up//adapter.            Selectcommand.transaction = Transaction; //if (table. extendedproperties["SQL"]! = NULL)//    {            //adapter.SelectCommand.CommandText = table. extendedproperties["SQL"].            ToString (); //    }                        //adapter.            Fill (ds, "Zyzs_terminal_base"); //adapter.            Update (table); //transaction.commit ();/////COMMIT TRANSACTION//  }            //catch (Mysqlexception ex)//{            //if (transaction! = NULL) transaction.            Rollback (); //throw ex; //}            //finally//{            //connection.            Close (); //connection.            Dispose (); //}        }        //public int Update (Model.model Model)//{        //#region SQL statements//const String sql = @ "//UPDATE [dbo]. [Contacts]//SET//[UserName] = @UserName//, [Tel] = @Tel//, [Tel1] = @Tel1//, [Address] = @Address//WHERE [Id] = @Id "; //#endregion//using (SqlConnection connection = new SqlConnection (connstr))//    {        //return connection.        Execute (SQL, model); //    }        //}#endregion

Two methods of C#mysql batch update