EF结合SqlBulkCopy实现高效的批量数据插入 |EF插件EntityFramework.Extended实现批量更新和删除

原文链接：http://blog.csdn.net/fanbin168/article/details/51485969

批量插入 （17597条数据批量插入耗时1.7秒）

using System;  using System.Collections.Generic;  using System.Linq;  using System.Web;  using System.Web.Mvc;    namespace MvcApplication1.Controllers  {      using MvcApplication1.Models;      using EntityFramework.Extensions;      using System.ComponentModel;      using System.Data;      using System.Data.SqlClient;      using System.Diagnostics;      public class HomeController : Controller      {          public ActionResult Index()          {              Stopwatch sw = new Stopwatch(); //计时器              sw.Start();//开始计时                using (var db = new salesEntities())              {                    List<location> entitys = db.location.ToList(); //构建集合，到时候会将这个集合数据批量插入到                    if (db.Database.Connection.State != ConnectionState.Open)                  {                      db.Database.Connection.Open(); //打开Connection连接                  }                    //调用BulkInsert方法,将entitys集合数据批量插入到数据库的tolocation表中                  BulkInsert<location>((SqlConnection)db.Database.Connection, "tolocation", entitys);                    if (db.Database.Connection.State != ConnectionState.Closed)                  {                      db.Database.Connection.Close(); //关闭Connection连接                  }              }                sw.Stop(); //结束计时              string aa = sw.Elapsed.ToString();//批量插入了17597条数据。耗时1.7秒              return View();          }              /// <summary>          /// 批量插入          /// </summary>          /// <typeparam name="T">泛型集合的类型</typeparam>          /// <param name="conn">连接对象</param>          /// <param name="tableName">将泛型集合插入到本地数据库表的表名</param>          /// <param name="list">要插入大泛型集合</param>          public static void BulkInsert<T>(SqlConnection conn, string tableName, IList<T> list)          {              using (var bulkCopy = new SqlBulkCopy(conn))              {                  bulkCopy.BatchSize = list.Count;                  bulkCopy.DestinationTableName = tableName;                    var table = new DataTable();                  var props = TypeDescriptor.GetProperties(typeof(T))                        .Cast<PropertyDescriptor>()                      .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))                      .ToArray();                    foreach (var propertyInfo in props)                  {                      bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);                      table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);                  }                    var values = new object[props.Length];                  foreach (var item in list)                  {                      for (var i = 0; i < values.Length; i++)                      {                          values[i] = props[i].GetValue(item);                      }                        table.Rows.Add(values);                  }                    bulkCopy.WriteToServer(table);              }          }        }  }  

使用EF扩展EntityFramework.Extended 对数据进行批量更新，和批量删除

首先去nuget上下载EntityFramework.Extended插件（搜索：EntityFramework.Extended） 安装后，在项目中引入using EntityFramework.Extensions; 名称空间

批量更新（17597条数据，批量更新耗时1.69秒）

using System;  using System.Collections.Generic;  using System.Linq;  using System.Web;  using System.Web.Mvc;    namespace MvcApplication1.Controllers  {      using MvcApplication1.Models;      using EntityFramework.Extensions; //使用EF的EntityFramework.Extended插件需要引入此名称空间      using System.Diagnostics;        public class HomeController : Controller      {          public ActionResult Index()          {              salesEntities db = new salesEntities();                Stopwatch sw = new Stopwatch(); //计时器              sw.Start();                //调用插件的Update方法进行批量更新（不需要我们手动的db.SaveChanges()了）              //db.location.Update(r => new location { version = 123 });//批量将location表里version字段数据更新为123                db.tolocation.Where(r => r.locId < 100).Update(c => new tolocation { version = 236 }); //也可以带条件批量修改                  sw.Stop();              string aa = sw.Elapsed.ToString();//批量更新了17597条数据。耗时1.69秒              return View();          }      }  }  

批量删除（17597条数据，批量删除耗时1.76秒）

using System;  using System.Collections.Generic;  using System.Linq;  using System.Web;  using System.Web.Mvc;    namespace MvcApplication1.Controllers  {      using MvcApplication1.Models;      using EntityFramework.Extensions; //使用EF的EntityFramework.Extended插件需要引入此名称空间      using System.Diagnostics;        public class HomeController : Controller      {          public ActionResult Index()          {              salesEntities db = new salesEntities();                Stopwatch sw = new Stopwatch(); //计时器              sw.Start();                //调用插件的Delete方法进行批量删除（不需要我们手动的db.SaveChanges()了）              //db.location.Delete(r => r.locId < 100000);                db.location.Where(r => r.locId < 10000).Delete(); //当然我也可以这样写                              sw.Stop();              string aa = sw.Elapsed.ToString();//批量删除了17597条数据。耗时1.76秒              return View();          }      }  }