C#中使用SqlBulk百万级数据秒级插入

本文转自这篇文章，提供了一种较快的数据插入的思路，转过来做个记录。 

#region 
static void Insert()
{
     Console.WriteLine("使用Bulk插入的实现方式");
     Stopwatch sw = new Stopwatch();
     DataTable dt = GetTableSchema(); 

      using (SqlConnection conn = new SqlConnection(StrConnMsg))
      {
          SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
          bulkCopy.DestinationTableName = "Product";
          bulkCopy.BatchSize = dt.Rows.Count;
          conn.Open();
          sw.Start();

          for (int i = 0; i < totalRow;i++ )
          {
              DataRow dr = dt.NewRow();
              dr[0] = Guid.NewGuid();
              dr[1] = string.Format("商品", i);
              dr[2] = (decimal)i;
              dt.Rows.Add(dr);
           }
           if (dt != null && dt.Rows.Count != 0)
           {
               bulkCopy.WriteToServer(dt);
               sw.Stop();
            }
      Console.WriteLine(string.Format("插入{0}条记录共花费{1}毫秒，{2}分钟", totalRow, sw.ElapsedMilliseconds, GetMinute(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;
}
#endregion

运行结果如下： 

插入100w条记录才8s多。打开Sqlserver Profiler跟踪，会发现执行的是如下语句： 

insert bulk Product ([Id] UniqueIdentifier, [NAME] VarChar(50) COLLATE Chinese_PRC_CI_AS, [Price] Decimal(18,2))