6W条数据做SQL插入,的三四分钟
用SQL Bulk秒入
使用:SqlHelper.BulkInsert<R_CalculationByContract>(Con, "R_CalculationByContractBulk", bulkList);
/// <summary>
/// 批量插入
/// </summary>
/// <typeparam name="T">泛型集合的类型</typeparam>
/// <param name="conStr">连接字符串</param>
/// <param name="tableName">将泛型集合插入到本地数据库表的表名</param>
/// <param name="list">要插入大泛型集合</param>
public static void BulkInsert<T>(string conStr, string tableName, IList<T> list)
{
SqlConnection conn = new SqlConnection(conStr);
conn.Open(); //打开Connection连接
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);
}
conn.Close(); //关闭Connection连接
}