SqlServer 批量插入的方式比较常用的插入方式有Insert、BatchInsert、SqlBulkCop
1.普通的Insert插入方法
public static void Insert(IEnumerablepersons)
{
using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
{
con.Open();
foreach (var person in persons)
{
using (var com = new SqlCommand(
"INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES(@Id,@Name,@Age,@CreateTime,@Sex)",
con))
{
com.Parameters.AddRange(new[]
{
new SqlParameter("@Id", SqlDbType.BigInt) {Value = person.Id},
new SqlParameter("@Name", SqlDbType.VarChar, 64) {Value = person.Name},
new SqlParameter("@Age", SqlDbType.Int) {Value = person.Age},
new SqlParameter("@CreateTime", SqlDbType.DateTime)
{Value = person.CreateTime ?? (object) DBNull.Value},
new SqlParameter("@Sex", SqlDbType.Int) {Value = (int)person.Sex},
});
com.ExecuteNonQuery();
}
}
}
}
2.拼接BatchInsert插入语句
public static void BatchInsert(Person[] persons)
{
using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
{
con.Open();
var pageCount = (persons.Length - 1) / 1000 + 1;
for (int i = 0; i < pageCount; i++)
{
var personList = persons.Skip(i * 1000).Take(1000).ToArray();
var values = personList.Select(p =>
$"({p.Id},'{p.Name}',{p.Age},{(p.CreateTime.HasValue ? $"'{p.CreateTime:yyyy-MM-dd HH:mm:ss}'" : "NULL")},{(int) p.Sex})");
var insertSql =
$"INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES{string.Join(",", values)}";
using (var com = new SqlCommand(insertSql, con))
{
com.ExecuteNonQuery();
}
}
}
}
3.SqlBulkCopy插入方案
public static void BulkCopy(IEnumerablepersons)
{
using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
{
con.Open();
var table = new DataTable();
table.Columns.AddRange(new []
{
new DataColumn("Id", typeof(long)),
new DataColumn("Name", typeof(string)),
new DataColumn("Age", typeof(int)),
new DataColumn("CreateTime", typeof(DateTime)),
new DataColumn("Sex", typeof(int)),
});
foreach (var p in persons)
{
table.Rows.Add(new object[] {p.Id, p.Name, p.Age, p.CreateTime, (int) p.Sex});
}
using (var copy = new SqlBulkCopy(con))
{
copy.DestinationTableName = "Person";
copy.WriteToServer(table);
}
}
}
4.三种方案速度对比
三者插入效率对比,Insert明显比SqlBulkCopy要慢太多,大概20~40倍性能差距,下面我们将SqlBulkCopy封装一下,让批量插入更加方便。
1.初始化数据源类型(此处我使用DataTable类型),一般是使用表中的列名作为数据源的列名
DataTable CopyData= new DataTable("TestTable");
CopyData.Columns.Add("id", typeof(Int));
CopyData.Columns.Add("Name", typeof(string));
CopyData.Columns.Add("Time", typeof(DateTime));
CopyData.Columns.Add("Content", typeof(string));
2.给数据填充数据
foreach (var dr in dt.Tables[0].Rows)
{
DataRow row = importDataTable.NewRow();
row["id"] = Convert.ToInt(id);
row["Name"] = RealName;//操作人
row["Time"] = DateTime.Now;//操作时间
row["Content"] = String.Join("=", dr.ItemArray);//内容
CopyData.Rows.Add(row);
}
3.打开数据库连接插入数据
string connectionStr = “数据库连接字符串”;
SqlConnection connection = new SqlConnection(connectionStr );
using (connection)
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlBulkCopy.DestinationTableName = “TestTable”;
sqlBulkCopy.BatchSize = CopyData.Rows.Count;
for (int i = 0; i < CopyData.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(CopyData.Columns[i].ColumnName, CopyData.Columns[i].ColumnName);
}
sqlBulkCopy.WriteToServer(CopyData);
return "成功";
}
catch (Exception ex)
{
return "失败," + ex;
}
}
}
这样就批量插入完毕了,执行起来速度很快。
sqlBulkCopy.DestinationTableName------->指定数据库表名
sqlBulkCopy.BatchSize--------->每一批次的行数
sqlBulkCopy.ColumnMappings.Add------->指定数据库与数据源之间的对应关系