批量插入 (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();
- }
- }
- }
|