你的位置:首页 > ASP.net教程

[ASP.net教程]SqlBulkCopy高效能批量插入SQL SERVER


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();          }        }      }    }