批量插入 Datetable数据 通过sqlbulkcopy 插入1百万条数据 用时 10秒钟 (有兴趣的小伙伴可以去测试)
/// <summary>
///
/// </summary>
/// <param name="connectionStr">连接字符串</param>
/// <param name="dataTableName">数据库表名称</param>
/// <param name="sourceDataTable"></param>
/// <param name="batchSize"></param>
public static void SqlBulkCopyByDataTable(string connectionStr, string dataTableName, DataTable sourceDataTable, int batchSize = )
{
using (SqlConnection connection = new SqlConnection(connectionStr))
{
using (System.Data.SqlClient.SqlBulkCopy sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlBulkCopy.DestinationTableName = dataTableName;
sqlBulkCopy.BatchSize = batchSize;
for (int i = ; i < sourceDataTable.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);
}
sqlBulkCopy.WriteToServer(sourceDataTable);
}
catch (Exception ex)
{ throw ex;
}
}
}
}
调用：
DataTable dt = new DataTable("测试");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));
for (int i = ; i <= ; i++)
{
DataRow row = dt.NewRow();
row["Id"] = i;
row["Name"] = "名字" + i;
row["Age"] = i;
dt.Rows.Add(row);
}
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
Console.WriteLine("批量插入table时间："+stopWatch.Elapsed);
批量插入List<T> :
#region List<T> 批量插入
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="sqlconn"></param>
public static void Sqlbulkcopy_<T>(List<T> data, string sqlconn = null)
{
#region 待处理数据初始化处理
List<PropertyInfo> pList = new List<PropertyInfo>();//创建属性的集合
DataTable dt = new DataTable();
//把所有的public属性加入到集合 并添加DataTable的列
Array.ForEach<PropertyInfo>(typeof(T).GetProperties(), p =>
{
pList.Add(p);
dt.Columns.Add(p.Name, p.PropertyType);
}); //获得反射的入口（typeof（）） //要对 array 的每个元素执行的 System.Action。
foreach (var item in data)
{
DataRow row = dt.NewRow(); //创建一个DataRow实例
pList.ForEach(p => row[p.Name] = p.GetValue(item, null)); //给row 赋值
dt.Rows.Add(row); //加入到DataTable
}
#endregion
#region 批量插入数据库 SqlBulkCopy声明及参数设置
SqlBulkCopy bulk = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.UseInternalTransaction) //, SqlBulkCopyOptions.UseInternalTransaction
{
DestinationTableName = "Tongbu" /*设置数据库目标表名称*/
,
BatchSize = dt.Rows.Count /*每一批次中的行数*/
}; bulk.ColumnMappings.Add("Id", "Id"); //设置数据源中的列和目标表中的列之间的映射关系
// bulk.ColumnMappings.Add("FlightId", "FlightId");//ColumnMappings.Add("源数据表列名称", "目标表数据列名称");
bulk.ColumnMappings.Add("Name", "Name");
bulk.ColumnMappings.Add("Age", "Age"); //...
#endregion
bulk.WriteToServer(dt);
if (bulk != null)
{
bulk.Close();
}
}
#endregion
调用：
List<TestModel> T_list = new List<TestModel>();
for (int i = ; i <= ; i++)
{
T_list.Add(new TestModel() { Name = "Test" + i, Age = i });
} Stopwatch stopWatch = new Stopwatch();
stopWatch.Start(); Sqlbulkcopy_Tran<TestModel>(T_list); Console.WriteLine(stopWatch.Elapsed);