sql server 批量插入数据

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace winform测试插入
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        string ConnectionString = string.Format("Data Source={0};database={1};uid=sa;pwd=123456", "192.168.200.101", "BulkTestDB");
        string erro = string.Empty;

        private void button1_Click(object sender, EventArgs e)
        {

            var t = MSSQLHelper.TestConnection(out erro, ConnectionString);
            // inset_1000_data(); 14秒
            bulk_1000_data(); ////111.8871 总毫秒数

        }


        public void bulk_1000_data()
        {

            //条件  构建的  datatable 必须和              
            //var dt = select top 0 * from B_ProductCode;
            //查询的结构一样
            //如果 插入的数据为 主键 5 -10; 第二次插入的主键是9-11; 将不会插入 9-11数据;
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[] {
                new DataColumn("Id",typeof(int)),
                new DataColumn("UserName",typeof(string)),
                //new DataColumn("Pwd",typeof(string))
            });
            for (int i = 0; i < 1000; i++)
            {
                DataRow r = dt.NewRow();
                r[0] = i;
                r[1] = string.Format("User-{0}", i);
              //  r[2] = string.Format("Pwd-{0}", i);
                dt.Rows.Add(r);
            }

            double test_time;  //总数据数 5 553 345； 五百万条 数据
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();  //开始监视代码运行时间
            //-----------------------------------------
            //|
            //|

            BulkToDB(dt);

            //|
            //|
            //-------------------------------------------
            watch.Stop();  //停止监视
            TimeSpan timespan = watch.Elapsed;  //获取当前实例测量得出的总时间

            test_time = timespan.TotalMilliseconds; //总毫秒数   
            MessageBox.Show(test_time.ToString()); //111.8871 总毫秒数

        }

        public void inset_1000_data()
        {
            double test_time;  //总数据数 5 553 345； 五百万条 数据
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();  //开始监视代码运行时间
            //-----------------------------------------
            //|
            //|

            for (int i = 0; i < 1000; i++)
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(" USE [BulkTestDB] ");
                sb.AppendFormat(" INSERT INTO [dbo].[BulkTestTable] ");
                sb.AppendFormat("            ([Id] ");
                sb.AppendFormat("            ,[UserName] ");
                sb.AppendFormat("            ,[Pwd]) ");
                sb.AppendFormat("      VALUES ");
                sb.AppendFormat("            ({0} ", i);
                sb.AppendFormat("            ,'name{0}'", i);
                sb.AppendFormat("            ,'pwd{0}')", i);

                MSSQLHelper.ExecuteNonQuery(sb.ToString(), out erro, ConnectionString);

                if (erro != string.Empty)
                {
                    MessageBox.Show(erro);
                    break;
                }
            }


            //|
            //|
            //-------------------------------------------
            watch.Stop();  //停止监视
            TimeSpan timespan = watch.Elapsed;  //获取当前实例测量得出的总时间

            test_time = timespan.TotalMilliseconds; //总毫秒数   
            MessageBox.Show(test_time.ToString()); //14659.1165 总毫秒数
        }


        //使用Bulk插入的情况 [ 较快 ]
        #region [ 使用Bulk插入的情况 ]
        void BulkToDB(DataTable dt)
        {
            Stopwatch sw = new Stopwatch();
            SqlConnection sqlconn = new SqlConnection(ConnectionString);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn);

            bulkCopy.DestinationTableName = "BulkTestTable"; //表名
            bulkCopy.BatchSize = dt.Rows.Count;              //数据量 
            try
            {
                sqlconn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    bulkCopy.WriteToServer(dt);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                sqlconn.Close();
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }
            }
        }
       
        #endregion

    }
}

转自 https://www.cnblogs.com/zoro-zero/p/7743164.html

 

   public bool BulkToDB(DataTable dt,string tablename,out string erro)
        {
            erro = string.Empty;
            SqlConnection sqlconn = new SqlConnection(SQLHelper.ConnectionString);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn);

            bulkCopy.DestinationTableName = tablename; //表名
            bulkCopy.BatchSize = dt.Rows.Count;              //数据量 
            try
            {
                sqlconn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    bulkCopy.WriteToServer(dt);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                erro = ex.Message;
                // Console.WriteLine(ex.Message);
                return false;
            }
            finally
            {
                sqlconn.Close();
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }
            }

        }