1. 有时候,会遇到datagridview中cell的值更新了,但是通过SqlDataAdapter.Update(dt)却不能够更新数据,比如在datagridview的cellvaluechanged的事件中直接使用,是不能立即更新的,在cellendedit事件中也是一样,以下是解决办法:
在cellendedit事件中:
this.Validate();//有效性,此句很重要
SqlCommandBuilder com = new SqlCommandBuilder(cAdapter);
com.ConflictOption = System.Data.ConflictOption.OverwriteChanges;
cAdapter.UpdateCommand = com.GetUpdateCommand();
cAdapter.DeleteCommand = com.GetDeleteCommand();
cAdapter.UpdateCommand.Connection = conn;
cAdapter.DeleteCommand.Connection = conn;
conn.Open();
cAdapter.Update(dt);
dt.AcceptChanges();
DataSet ds = new DataSet();
cAdapter.Fill(ds);
return ds.Tables[0];
2.上述的代码在更改后焦点转移到其他控件时也会出现无法更新的状况,我们就需要在没更新成功时,datagridview能够实时的和数据库保持一致,这样,我们能够知道是否是已经更新成功了,于是下面的函数完好的解决了这个问题
public static DataTable UpdateData(DataTable dt, SqlDataAdapter cAdapter)
{
SqlConnection conn = new SqlConnection(Properties.Settings.Default.sqlsource);
try
{
SqlCommandBuilder com = new SqlCommandBuilder(cAdapter);
com.ConflictOption = System.Data.ConflictOption.OverwriteChanges;
cAdapter.UpdateCommand = com.GetUpdateCommand();
cAdapter.DeleteCommand = com.GetDeleteCommand();
cAdapter.InsertCommand = com.GetInsertCommand();
cAdapter.UpdateCommand.Connection = conn;
cAdapter.DeleteCommand.Connection = conn;
cAdapter.InsertCommand.Connection = conn;
conn.Open();
cAdapter.Update(dt);
dt.AcceptChanges();
DataSet ds = new DataSet();
cAdapter.Fill(ds);
return ds.Tables[0];
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show("\n更新数据库出错,可能是数据库出现异常,请稍后再试,或者联系管理员解决" + "\n" + ex.Message + "\n" + ex.StackTrace);
return null;
}
finally
{
conn.Close();
}
}
调用时直接使用datagridview.datasource=DataTable UpdateData(DataTable dt, SqlDataAdapter cAdapter)即可。