- 积分
- 33
- 在线时间
- 53 小时
- 主题
- 28
- 注册时间
- 2013-8-31
- 帖子
- 132
- 最后登录
- 2019-10-21
- 帖子
- 132
- 软币
- 3320
- 在线时间
- 53 小时
- 注册时间
- 2013-8-31
|
方法一:
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[0].Value + "'";
cmdInsert += ",'" + this.dataGridView1.Rows.Cells[1].Value + "'";
cmdInsert += ",'" + this.dataGridView1.Rows.Cells[2].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[0].Value + "'";
cmdInsert += ",'" + this.dataGridView1.Rows.Cells[1].Value + "'";
cmdInsert += ",'" + str+ "')";
Common.SqlServerHelper.ExecuteSql(cmdInsert);
}
sw01.Stop();
sLocct = sw01.ElapsedMilliseconds;
this.label1.Text = "用时:" + sLocct.ToString() + "秒";
}
}
那这样第三列就是你想要得到的名称.
|
评分
-
查看全部评分
|