C#使用SqlBulk批量插入：ADO.NET篇

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
                /*sql里面没有unicode，unicode对应的类型应该是uniqueidentifier
                 * 备注：bool对应bit
                 * */
                string sqlForCreatTable = "CREATE TABLE testForBulkInsert2" +
                                          "( Id uniqueidentifier," +
                                          " Name CHAR(50), Price CHAR(255))";
                SqlCommand cmd = new SqlCommand(sqlForCreatTable, conn);
                conn.Open();
                //cmd.ExecuteNonQuery();
                #endregion
                #region
                /*给SqlBulkCopy指定数据库中的表
                 * SqlBulkCopy的大小由datatable决定
                 * */
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = "testForBulkInsert2";
                bulkCopy.BatchSize = dt.Rows.Count;
               //conn.Open();
                sw.Start();
                #endregion
                #region//生成datatable用于插入数据库
                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;
        }
    }