dataGridView1 数据来源于一个视图(有三个表),
在 dataGridView1 中修改数据,然后点击保存后如何成批更新到数据库?
我按下面的做法,出错信息为:不能对多表操作。 public void UpdateData(string SQLString, DataTable _dt)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.DeleteCommand = new SqlCommand("", connection);
da.InsertCommand = new SqlCommand("", connection);
da.UpdateCommand = new SqlCommand("", connection);
da.SelectCommand = new SqlCommand(SQLString , connection);
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(da);
da.DeleteCommand = myCommandBuilder.GetDeleteCommand();
da.InsertCommand = myCommandBuilder.GetInsertCommand();
da.UpdateCommand = myCommandBuilder.GetUpdateCommand();
da.Update(_dt);
}
传入 UpdateData("select * From 表",_dt) 正确
传入 UpdateData("select * From 视图",_dt) 出错
强调:dataGridView1 数据来源于一个视图(有三个表)
在 dataGridView1 中修改数据,然后点击保存后如何成批更新到数据库?
我按下面的做法,出错信息为:不能对多表操作。 public void UpdateData(string SQLString, DataTable _dt)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.DeleteCommand = new SqlCommand("", connection);
da.InsertCommand = new SqlCommand("", connection);
da.UpdateCommand = new SqlCommand("", connection);
da.SelectCommand = new SqlCommand(SQLString , connection);
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(da);
da.DeleteCommand = myCommandBuilder.GetDeleteCommand();
da.InsertCommand = myCommandBuilder.GetInsertCommand();
da.UpdateCommand = myCommandBuilder.GetUpdateCommand();
da.Update(_dt);
}
传入 UpdateData("select * From 表",_dt) 正确
传入 UpdateData("select * From 视图",_dt) 出错
强调:dataGridView1 数据来源于一个视图(有三个表)
dataGridView1.DataSource = _dt;
对于多个基表不支持动态 SQL 生成。
private DataSet ds = new DataSet();
private SqlDataAdapter sda = new SqlDataAdapter();
private SqlConnection myConn = new SqlConnection();
1、利用SqlCommandBuilder SqlCommandBuilder sb = new SqlCommandBuilder(sda);
sda.Update(ds.Tables[0]);
ds.Tables[0].AcceptChanges();
2、指定UpdateCommand
SqlParameter param = new SqlParameter();
string strSql = " update Good_PrtyInfo set maxstcs=@maxstcs , minstcs=@minstcs where 1=1 and GoodID=@GoodID ";
sda.UpdateCommand = new SqlCommand(strSql, myConn);
param = sda.UpdateCommand.Parameters.Add("@maxstcs", SqlDbType.VarChar, 50, "maxstcs");
param.SourceVersion = DataRowVersion.Current;
param = sda.UpdateCommand.Parameters.Add("@minstcs", SqlDbType.VarChar, 50, "minstcs");
param.SourceVersion = DataRowVersion.Current;
//param = myDA.UpdateCommand.Parameters.Add("@DeptID", SqlDbType.VarChar, 50, "DeptID");
//param.SourceVersion = DataRowVersion.Current;
param = sda.UpdateCommand.Parameters.Add("@GoodID", SqlDbType.VarChar, 50, "GoodID");
param.SourceVersion = DataRowVersion.Original;
sda.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
sda.UpdateBatchSize = 10;
//SqlCommandBuilder sb = new SqlCommandBuilder(sda);
sda.Update(ds.Tables[0]);
ds.Tables[0].AcceptChanges();