C#使用SqlBulk批量插入:ADO.NET篇
具体代码如下
class bulkInsert
{
static void Main() => InsertTwo();
static void InsertTwo()
{
Console.WriteLine("使用Bulk插入的实现方式");
Stopwatch sw = new Stopwatch();
DataTable dt = GetTableSchema();
string StrConnMsg = "";
using (SqlConnection conn = new SqlConnection(StrConnMsg))
{
#region
string sqlForCreatTable = "CREATE TABLE testForBulkInsert2" +
"( Id uniqueidentifier," +
" Name CHAR(50), Price CHAR(255))";
SqlCommand cmd = new SqlCommand(sqlForCreatTable, conn);
conn.Open();
#endregion
#region
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = "testForBulkInsert2";
bulkCopy.BatchSize = dt.Rows.Count;
sw.Start();
#endregion
#region
for (int i = 0; i < 100; i++)
{
DataRow dr = dt.NewRow();
dr[0] = Guid.NewGuid();
dr[1] = string.Format("商品", i);
dr[2] = (decimal)i;
dt.Rows.Add(dr);
}
#endregion
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
sw.Stop();
}
Console.WriteLine(string.Format("插入{0}条记录共花费{1}毫秒", 100, sw.ElapsedMilliseconds));
}
}
static DataTable GetTableSchema()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("Id",typeof(Guid)),
new DataColumn("Name",typeof(string)),
new DataColumn("Price",typeof(decimal))});
return dt;
}
}