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