批量插入数据

主要命名空间

using System.Data.SqlClient;

　|-SqlBulkCopy

　|-SqlConnection

　|-SqlTransaction

--------------------------BatchInsert<T>(List<T> toList, SqlRowsCopiedEventHandler resulthandler = null)-------------------

批量插入主要用的就是SqlBulkCopy类。

--------------------------------------------------------------------------------------------------------------------------------

 1 public static void BatchInsert<T>(List<T> toList, SqlRowsCopiedEventHandler resulthandler = null)
 2 {
 3     using (var conn = Connection)
 4     {
 5         using (var tran = conn.BeginTransaction())
 6         {
 7             using (var bi = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran))
 8             {
 9                 var tp=typeof (T);
10                 bi.BulkCopyTimeout = 1000;
11                 var name = tp.Name.EndsWith("Model") ? tp.Name.Substring(0, tp.Name.Length - 5):tp.Name;
12                 bi.BatchSize = toList.Count;
13                 bi.DestinationTableName = name;
14                 foreach (var propertyInfo in tp.GetProperts())
15                 {
16                     var attribute = (IgnoreAttribute)System.Attribute.GetCustomAttribute(propertyInfo, typeof(IgnoreAttribute));
17                     if (null == attribute || !attribute.Ignore)
18                     {
19                         bi.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
20                     }
21                 }
22                 var dtb = DataTableHelper.GetTable(toList);
23                 dtb.TableName = name;
24                 if (resulthandler != null)
25                     bi.SqlRowsCopied += resulthandler;//成功的回调事件！
26                 bi.WriteToServer(dtb);
27                 tran.Commit();//这里用了事务，如果数据量较大，建议开启；因为插入时会锁表，所以建议读写分离。
28             }
29         }
30     }
31 }

-------------------------------------------DataTableHelper.GetTable<T>(IList<T> ts)--------------------------------------

利用反射，获取需要的字段的名称和类型

---------------------------------------------------------------------------------------------------------------------------

DateTable dt=new DataTable();

var t=typeof(T);

var ptys=t.GetPropertys().Where(p=>p.GetAttribute<Ignore>()==null);

//将Column添加到DataTable

ptys.Foreach(p=>dt.Columns.Add(p.Name,p.Nullable.GetUnderlingType(p)||p.PropertyType))

//将数据添加到table

foreach(var item in ts){

var row=dt.NewRow()

ptys.Foreach(p=>row[p.Name]=p.GetValue());

}