C# SqlBulkCopy使用心得
方法一:private void button1_Click(object sender, EventArgs e)
{
string constring = System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
string sql = "SELECT s_saledt,branch,f_execamt from cntpy order by branch";
long sLocct = 0;
DataTable dt = Common.SqlServerHelper.ExecuteTable(sql); //获取解析日志数据
if (dt != null)
{
System.Diagnostics.Stopwatch sw01 = new System.Diagnostics.Stopwatch();
sw01.Start();
ExecuteSqlBulkCopy(dt, "cntpy_do", constring); //批量插入(本人一次插入20万,30万没有试过,有兴趣自己研究)
sw01.Stop();
sLocct = sw01.ElapsedMilliseconds; //这里是自己加的 用来记录插入数据所花时间
}
this.label1.Text = "用时：" + sLocct.ToString() + "秒";
}
public void ExecuteSqlBulkCopy(DataTable dt, string strTableName, string strConn)
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
try
{
SqlBulkCopy bulk = new SqlBulkCopy(conn);
bulk.DestinationTableName = strTableName;//表名
bulk.BatchSize = dt.Rows.Count;
bulk.BulkCopyTimeout = 180;
bulk.ColumnMappings.Add("s_saledt", "s_saledt"); //本地表和目标表列名
bulk.ColumnMappings.Add("branch", "branch"); //如果表有多个字段,下面继续添加
bulk.ColumnMappings.Add("f_execamt", "f_execamt"); //如果表有多个字段,下面继续添加
bulk.WriteToServer(dt);
bulk.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed) conn.Close();
}
}
}
方法二:
private void Main_Load(object sender, EventArgs e)
{
string constring = System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
string sql = "SELECT s_saledt,f_execamt,branch from cntpy order by branch";
DataTable dt = Common.SqlServerHelper.ExecuteTable(sql);
this.dataGridView1.DataSource = dt;
}
private void button2_Click(object sender, EventArgs e)
{
if (this.dataGridView1.DataSource != null)
{
long sLocct = 0;
System.Diagnostics.Stopwatch sw01 = new System.Diagnostics.Stopwatch();
sw01.Start();
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
string cmdInsert = String.Empty;
cmdInsert = "insert into cntpy_do(s_saledt,f_execamt,branch)";
cmdInsert += "VALUES('" + this.dataGridView1.Rows.Cells.Value + "'";
cmdInsert += ",'" + this.dataGridView1.Rows.Cells.Value + "'";
cmdInsert += ",'" + this.dataGridView1.Rows.Cells.Value + "')";
Common.SqlServerHelper.ExecuteSql(cmdInsert);
}
sw01.Stop();
sLocct = sw01.ElapsedMilliseconds;
this.label1.Text = "用时：" + sLocct.ToString() + "秒";
}
}
第二种方法执行的太慢了没有第一种方法好用,但第二种可以自由变换数据.
比如:
private void button2_Click(object sender, EventArgs e)
{
if (this.dataGridView1.DataSource != null)
{
string str="百货集团";
long sLocct = 0;
System.Diagnostics.Stopwatch sw01 = new System.Diagnostics.Stopwatch();
sw01.Start();
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
string cmdInsert = String.Empty;
cmdInsert = "insert into cntpy_do(s_saledt,f_execamt,branch)";
cmdInsert += "VALUES('" + this.dataGridView1.Rows.Cells.Value + "'";
cmdInsert += ",'" + this.dataGridView1.Rows.Cells.Value + "'";
cmdInsert += ",'" + str+ "')";
Common.SqlServerHelper.ExecuteSql(cmdInsert);
}
sw01.Stop();
sLocct = sw01.ElapsedMilliseconds;
this.label1.Text = "用时：" + sLocct.ToString() + "秒";
}
}
那这样第三列就是你想要得到的名称.
我用过，确实速度很快，那你知道怎样快速批量更新吗？ 很好的文章分享!!!
页:
[1]