SqlBulkCopy高效能批量插入SQL SERVER

2018-06-18 04:55:03来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

what

SqlBulkCopy是.NET提供的用来批量插入数据的一个类,特别是将内存中的数据一次性插入到数据库,目前只能插入到SQL SERVER数据库,数据源可以是DataTable、IDataReader

why

SqlBulkCopy插入与循环一条条插入相比,性能有巨大提升,数据越多,性能优势越明显。

测试结果:一万条数据,一条条插入要6秒,Bulk只需要0.1秒。理论上插入百万条记录也只需要1分钟以内

how

以下是测试代码:

       string sourceConStr = "Data Source=192.168.1.100;Initial Catalog=A; uid=sa;pwd=sa";
        string destConStr = "Data Source=.;Initial Catalog=B; uid=sa;pwd=sa";

        DataTable dt = new DataTable();

        public Form1()
        {
            InitializeComponent();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            dt = GetTable();
            MessageBox.Show("get finish");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string str = "queue start...!  \n";
            Stopwatch sw = new Stopwatch();
            sw.Start();
            CopyData(dt);
            sw.Stop();
            str += "queue cost time is " + sw.ElapsedMilliseconds + "\n";
            richTextBox1.Text = str;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string str = "bulk start...!  \n";
            Stopwatch sw = new Stopwatch();
            sw.Start();
            CopyDataBulk(dt);
            sw.Stop();
            str += "bulk cost time is " + sw.ElapsedMilliseconds + "\n";
            richTextBox2.Text = str;
        }


        //从数据源获取要插入的数据
        private DataTable GetTable()
        {
            DataTable dt = new DataTable();
            using (SqlConnection sourceConnection = new SqlConnection(sourceConStr))
            {
                sourceConnection.Open();
                SqlCommand cmd = new SqlCommand("SELECT TOP 10000 CName,PersonID,Sex,Age FROM Customer order by cid asc;", sourceConnection);
                cmd.CommandTimeout = 600000;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            return dt;
        }

        //一条条插入
        private void CopyData(DataTable dt)
        {
            using (SqlConnection destinationConnection = new SqlConnection(destConStr))
            {
                destinationConnection.Open();

                foreach(DataRow reader in dt.Rows)
                { 
                    string sql = "INSERT INTO Customer(Name,PersonID,Sex,Age) VALUES('" + reader["Cname"].ToString() + "','" + reader["PersonID"].ToString() + "','" + reader["Sex"].ToString() + "','" + reader["Age"].ToString() + "')";

                    SqlCommand cmd = new SqlCommand(sql, destinationConnection);
                    try
                    {
                        int re = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

        //Bulk插入
        private void CopyDataBulk(DataTable dt)
        {
            using (SqlConnection destinationConnection = new SqlConnection(destConStr))
            {
                destinationConnection.Open();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {

                    //写对应关系。如旧表的CName列的数据,对应新表Name列 
                    bulkCopy.ColumnMappings.Add("CName", "Name");

                    //设置目标表名
                    bulkCopy.DestinationTableName = "Customer";

                    try
                    {
                        bulkCopy.WriteToServer(dt);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                       // reader.Close();
                    }
                }

            }
        }

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:readonly与const的区别

下一篇:.NET 用 Unity 依赖注入——概述注册和解析类型(1)