你的位置:首页 > ASP.net教程

[ASP.net教程]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