1.先填充数据集
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings.Get("Con"));
SqlCommand cmdSelect = new SqlCommand("select * from tbl_Copy",con);
SqlCommand cmdUpdate = new SqlCommand("update tbl_Copy set Val1=@Val1",con);
cmdUpdate.Parameters.Add("@Val1", SqlDbType.VarChar, 50, "Val1");
cmdUpdate.UpdatedRowSource = UpdateRowSource.None;//将输出参数和第一个返回行都映射到 System.Data.DataSet 中的已更改的行
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmdSelect;
adp.UpdateCommand = cmdUpdate;
DataSet ds=new DataSet();
adp.Fill(ds);
AdapterUpdate(adp, ds, 100);
2.更新的方法
void AdapterUpdate(SqlDataAdapter adp,DataSet ds,int records)
{
foreach(DataRow row in ds.Tables[0].Rows)
{
row["Val1"] = "批量更新";
}
try
{
DateTime sTime = DateTime.Now;
adp.UpdateBatchSize = records;//每次操作的记录条数
adp.Update(ds);
DateTime eTime = DateTime.Now;
TimeSpan ss = eTime.Subtract(sTime);
label1.Text = "更新10条记录,用时 :"+ss.Seconds+"."+ss.Milliseconds+" 秒";
}
catch(Exception ee)
{
label1.Text = ee.Message;
}
}
3.不过我
更新时老提示我超时,难道是数据量过大,才十万条记录而已,我设置 adp.UpdateCommand.CommandTimeout=10000000;好像也没用,郁闷中。。。