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

1.  
   using System;
2.  
   using System.Collections.Generic;
3.  
   using System.Linq;
4.  
   using System.Web;
5.  
   using System.Web.Mvc;
6.  
    
7.  
   namespace MvcApplication1.Controllers
8.  
   {
9.  
   using MvcApplication1.Models;
10.  
    using EntityFramework.Extensions;
11.  
    using System.ComponentModel;
12.  
    using System.Data;
13.  
    using System.Data.SqlClient;
14.  
    using System.Diagnostics;
15.  
    public class HomeController : Controller
16.  
    {
17.  
    public ActionResult Index()
18.  
    {
19.  
    Stopwatch sw = new Stopwatch(); //计时器
20.  
    sw.Start();//开始计时
21.  
     
22.  
    using (var db = new salesEntities())
23.  
    {
24.  
     
25.  
    List<location> entitys = db.location.ToList(); //构建集合，到时候会将这个集合数据批量插入到
26.  
     
27.  
    if (db.Database.Connection.State != ConnectionState.Open)
28.  
    {
29.  
    db.Database.Connection.Open(); //打开Connection连接
30.  
    }
31.  
     
32.  
    //调用BulkInsert方法,将entitys集合数据批量插入到数据库的tolocation表中
33.  
    BulkInsert<location>((SqlConnection)db.Database.Connection, "tolocation", entitys);
34.  
     
35.  
    if (db.Database.Connection.State != ConnectionState.Closed)
36.  
    {
37.  
    db.Database.Connection.Close(); //关闭Connection连接
38.  
    }
39.  
    }
40.  
     
41.  
    sw.Stop(); //结束计时
42.  
    string aa = sw.Elapsed.ToString();//批量插入了17597条数据。耗时1.7秒
43.  
    return View();
44.  
    }
45.  
     
46.  
     
47.  
    /// <summary>
48.  
    /// 批量插入
49.  
    /// </summary>
50.  
    /// <typeparam name="T">泛型集合的类型</typeparam>
51.  
    /// <param name="conn">连接对象</param>
52.  
    /// <param name="tableName">将泛型集合插入到本地数据库表的表名</param>
53.  
    /// <param name="list">要插入大泛型集合</param>
54.  
    public static void BulkInsert<T>(SqlConnection conn, string tableName, IList<T> list)
55.  
    {
56.  
    using (var bulkCopy = new SqlBulkCopy(conn))
57.  
    {
58.  
    bulkCopy.BatchSize = list.Count;
59.  
    bulkCopy.DestinationTableName = tableName;
60.  
     
61.  
    var table = new DataTable();
62.  
    var props = TypeDescriptor.GetProperties(typeof(T))
63.  
     
64.  
    .Cast<PropertyDescriptor>()
65.  
    .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
66.  
    .ToArray();
67.  
     
68.  
    foreach (var propertyInfo in props)
69.  
    {
70.  
    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
71.  
    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
72.  
    }
73.  
     
74.  
    var values = new object[props.Length];
75.  
    foreach (var item in list)
76.  
    {
77.  
    for (var i = 0; i < values.Length; i++)
78.  
    {
79.  
    values[i] = props[i].GetValue(item);
80.  
    }
81.  
     
82.  
    table.Rows.Add(values);
83.  
    }
84.  
     
85.  
    bulkCopy.WriteToServer(table);
86.  
    }
87.  
    }
88.  
     
89.  
    }
90.  
    }

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

1.  
   using System;
2.  
   using System.Collections.Generic;
3.  
   using System.Linq;
4.  
   using System.Web;
5.  
   using System.Web.Mvc;
6.  
    
7.  
   namespace MvcApplication1.Controllers
8.  
   {
9.  
   using MvcApplication1.Models;
10.  
    using EntityFramework.Extensions; //使用EF的EntityFramework.Extended插件需要引入此名称空间
11.  
    using System.Diagnostics;
12.  
     
13.  
    public class HomeController : Controller
14.  
    {
15.  
    public ActionResult Index()
16.  
    {
17.  
    salesEntities db = new salesEntities();
18.  
     
19.  
    Stopwatch sw = new Stopwatch(); //计时器
20.  
    sw.Start();
21.  
     
22.  
    //调用插件的Update方法进行批量更新（不需要我们手动的db.SaveChanges()了）
23.  
    //db.location.Update(r => new location { version = 123 });//批量将location表里version字段数据更新为123
24.  
     
25.  
    db.tolocation.Where(r => r.locId < 100).Update(c => new tolocation { version = 236 }); //也可以带条件批量修改
26.  
     
27.  
     
28.  
    sw.Stop();
29.  
    string aa = sw.Elapsed.ToString();//批量更新了17597条数据。耗时1.69秒
30.  
    return View();
31.  
    }
32.  
    }
33.  
    }

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

1.  
   using System;
2.  
   using System.Collections.Generic;
3.  
   using System.Linq;
4.  
   using System.Web;
5.  
   using System.Web.Mvc;
6.  
    
7.  
   namespace MvcApplication1.Controllers
8.  
   {
9.  
   using MvcApplication1.Models;
10.  
    using EntityFramework.Extensions; //使用EF的EntityFramework.Extended插件需要引入此名称空间
11.  
    using System.Diagnostics;
12.  
     
13.  
    public class HomeController : Controller
14.  
    {
15.  
    public ActionResult Index()
16.  
    {
17.  
    salesEntities db = new salesEntities();
18.  
     
19.  
    Stopwatch sw = new Stopwatch(); //计时器
20.  
    sw.Start();
21.  
     
22.  
    //调用插件的Delete方法进行批量删除（不需要我们手动的db.SaveChanges()了）
23.  
    //db.location.Delete(r => r.locId < 100000);
24.  
     
25.  
    db.location.Where(r => r.locId < 10000).Delete(); //当然我也可以这样写
26.  
     
27.  
     
28.  
    sw.Stop();
29.  
    string aa = sw.Elapsed.ToString();//批量删除了17597条数据。耗时1.76秒
30.  
    return View();
31.  
    }
32.  
    }
33.  
    }