SQL Server 大数据量批量插入

      private void AddShuJu_Click(object sender, RoutedEventArgs e)
        {
            Stopwatch wath = new Stopwatch();
            wath.Start();
            for (int i = 0; i < 10; i++)
            {
                //创建datatable实例
                DataTable data = new DataTable();
                //填充字段
                data = GetFiled(data);
                for (int count = i * 100000; count < (i + 1) * 100000; count++)
                {

                    DataRow r = data.NewRow();
                    r[1] = string.Format("太妙-{0}", count * i);
                    r[2] = string.Format("{0}", "男");
                    r[3] = i;
                    r[4] = "朝阳";
                    r[5] = DateTime.Now;
                    data.Rows.Add(r);
                }
                SQLHelp.SqlBulk(data);
            }
            wath.Stop();
            MessageBox.Show("插入完成，共用时间为+" + wath.ElapsedMilliseconds);
        }

        //添加必须填写的字段
        private DataTable GetFiled(DataTable data)
        {
            data.Columns.AddRange(new DataColumn[]
            {
                new DataColumn("Id", typeof (int)),
                new DataColumn("Name", typeof (string)),
                new DataColumn("Sex", typeof (string)),
                new DataColumn("Age", typeof (int)),
                new DataColumn("Address", typeof (string)),
                new DataColumn("AddTime", typeof (DateTime))
            });
            return data;
        }

        //批量插入
        public static void SqlBulk(DataTable dt)
        {
            var con = new SqlConnection(conect);        
            SqlBulkCopy bulk = new SqlBulkCopy(con);
            //表的名字
            bulk.DestinationTableName = "xiao_student";
            bulk.BatchSize = dt.Rows.Count;
            try
            {
                con.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    bulk.WriteToServer(dt);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
                if (bulk!=null)
                {
                    bulk.Close();
                }
            }
        }

 

转载于:https://www.cnblogs.com/xiaoyaodijun/p/4515403.html