自己设计的一个mysql数据库批量添加数据的基类。用于批量向mysql数据库添加数据,子类实现起来很简单,自测性能也还不错。
1、基类实现-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 /// 插入语句的头部10 /// </summary>11 protected abstract string InsertHead { get; }12 13 /// <summary>14 /// 出入语句的执行体15 /// </summary>16 protected List<string> InsertBodyList { get; set; } = new List<string>();17 /// <summary>18 /// 缓存的sql语句长度19 /// </summary>20 public int SqlCacheLengh { get; set; } = 1000 * 10;21 22 /// <summary>23 /// 批量添加的方法24 /// </summary>25 /// <param name="m"></param>26 public abstract void BatchAdd(T m);27 28 /// <summary>29 /// 执行添加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 /// 清楚缓存56 /// </summary>57 public void ClearInsertBody()58 {59 this.InsertBodyList.Clear();60 }61 }62 }
2、一个简单的子类实现-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、控制台项目,使用示例
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 + "号" });14 }15 16 personAddHelper.ExecuteBatchAdd();17 18 watch.Stop();19 20 Console.WriteLine($"成功插入 {amount} 条数据,用时:{watch.ElapsedMilliseconds} ms");21 22 Console.ReadKey();23 }
4、源码示例地址:http://files.cnblogs.com/files/renjing/MysqlBatchAdd.rar
原标题:mysql批量插入数据的基类
关键词:MYSQL