mysql批量插入数据的基类

自己设计的一个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