mysql批量插入數據的基類，mysql插入

mysql批量插入數據的基類，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