Base class for batch data insertion in mysql, mysql insert

Base class for batch data insertion in mysql, mysql insert

You have designed a mysql database to add data base classes in batches. It is used to add data to the mysql database in batches. sub-classes are easy to implement and the self-testing performance is also good.

1. base class implementation-BatchAddBase

1 using System. collections. generic; 2 using System. text; 3 4 namespace MysqlBatchAdd 5 {6 public abstract class BatchAddBase <T> where T: class, new () 7 {8 /// <summary> 9 // insert statement header 10 /// </summary> 11 protected abstract string InsertHead {get ;} 12 13 /// <summary> 14 // The execution body of the inbound and outbound statements 15 /// </summary> 16 protected List <string> InsertBodyList {get; set ;} = new List <string> (); 17 // <summary> 18 // The cached SQL statement length is 19 /// </summary> 20 public int SqlCacheLengh {get; set ;}= 1000*10; 21 22 /// <summary> 23 // method of batch addition 24 /// </summary> 25 /// <param name = "m"> </param> 26 public abstract void BatchAdd (T m ); 27 28 /// <summary> 29 /// add 30 /// </summary> 31 public virtual void ExecuteBatchAdd () 32 {33 StringBuilder sqlCache = new StringBuilder (); 34 35 foreach (string insertBody in InsertBodyList) 36 {37 sqlCache. append (insertBody + ","); 38 39 if (sqlCache. length> = SqlCacheLengh) 40 {41 sqlCache. remove (sqlCache. length-1, 1); 42 MySqlHelper. executeNonQuery (this. insertHead + sqlCache. toString (); 43 sqlCache. clear (); 44} 45} 46 47 if (sqlCache. length> 0) 48 {49 sqlCache. remove (sqlCache. length-1, 1); 50 MySqlHelper. executeNonQuery (this. insertHead + sqlCache. toString (); 51 sqlCache. clear (); 52} 53} 54 // <summary> 55 // Clear cache 56 /// </summary> 57 public void ClearInsertBody () 58 {59 this. insertBodyList. clear (); 60} 61} 62}

2. A simple subclass implementation-PersonAddHelper

 1 namespace MysqlBatchAdd 2 { 3     public class PersonAddHelper : BatchAddBase<Person> 4     { 5         protected override string InsertHead 6         { 7             get 8             { 9                 return @"insert into person(10 name) values ";11             }12         }13 14         public override void BatchAdd(Person m)15         {16             this.InsertBodyList.Add($@" (17 '{m.name}')");18         }19     }20 }

3. Console project, use example

1 static void Main (string [] args) 2 {3 PersonAddHelper personAddHelper = new PersonAddHelper (); 4 5 Stopwatch watch = new Stopwatch (); 6 7 watch. start (); 8 9 int amount = 100000; 10 11 for (int I = 1; I <= amount; I ++) 12 {13 personAddHelper. batchAdd (new Person () {name = I + "Number"}); 14} 15 16 personAddHelper. executeBatchAdd (); 17 18 watch. stop (); 19 20 Console. writeLine ($ "successfully inserted {amount} data records, time: {watch. elapsedMilliseconds} ms "); 21 22 Console. readKey (); 23}

4, source code example address: http://files.cnblogs.com/files/renjing/MysqlBatchAdd.rar