获取及更新datatable中的数据

如果一个表中含有多数据库中表的数据,要更新的时候,最好的办法是用instead of 触发器. 如果这样的话,就不能用adapter.update(datatble)的形式来更新表,每个表的更新都要手动来写.

 

DataTable dtInserted  =  dtCST.GetChanges(DataRowState.Added);
            DataTable dtDeleted  =  dtCST.GetChanges(DataRowState.Deleted);
            DataTable dtUpdated  =  dtCST.GetChanges(DataRowState.Modified);
             try
             ... {
                if (dtInserted!=null)
                ...{
                    for (int i = 0; i < dtInserted.Rows.Count; i++)
                    ...{
                        string cmd = "insert vCST (ID,CID,TCode,Quantity,Item,UserName,UserDate) values( "
                        + "'" + dtInserted.Rows[i][0].ToString() + "',"
                        + dtInserted.Rows[i][1].ToString()
                        + ",'" + dtInserted.Rows[i][2].ToString() + "',"
                        + dtInserted.Rows[i][3].ToString() + ","
                        + dtInserted.Rows[i][4].ToString() + ","
                        + "'" + dtInserted.Rows[i][5].ToString() + "',"
                        + "'" + dtInserted.Rows[i][6].ToString() + "'"
                        + ")";
                        try
                        ...{
                            DAL.SqlHelper.ExecuteNonQuery(General.connstr, CommandType.Text, cmd);
                        }
                        catch (Exception err)
                        ...{
                            MessageBox.Show(err.Message, "出错了!");
                        }
                    }
                }

                if (dtUpdated!=null)
                ...{
                    for (int i = 0; i < dtUpdated.Rows.Count; i++)
                    ...{
                        string cmd = "update vCST set  CID=" + dtUpdated.Rows[i][1].ToString() + ","
                            +"TCode='" + dtUpdated.Rows[i][2].ToString() + "',"
                            + "Quantity=" + dtUpdated.Rows[i][3].ToString() + ","
                            + "Item=" + dtUpdated.Rows[i][4].ToString() + ","
                            + "UserName='" + dtUpdated.Rows[i][5].ToString() + "',"
                            + "UserDate='" + dtUpdated.Rows[i][6].ToString() + "'"
                        + " where ID='" + dtUpdated.Rows[i][0].ToString() + "'";

                        try
                        ...{
                            DAL.SqlHelper.ExecuteNonQuery(General.connstr, CommandType.Text, cmd);
                        }
                        catch (Exception err)
                        ...{
                            MessageBox.Show(err.Message, "出错了!");
                        }
                    }
                }

                if (dtDeleted!=null)
                ...{
                    foreach (DataRow dr in dtDeleted.Rows) 
                    ...{
                        string cmd = "delete from vCST where ID="
                        + "'" + dr[0,DataRowVersion.Original].ToString() + "'";
                       
                        try
                        ...{
                            DAL.SqlHelper.ExecuteNonQuery(General.connstr, CommandType.Text, cmd);
                        }
                        catch (Exception err)
                        ...{
                            MessageBox.Show(err.Message, "出错了!");
                        }
                    }
                }
                dtCST.AcceptChanges();
            }
             catch  (Exception err)
             ... {
                MessageBox.Show(err.Message, "出错了!");
            }
             finally
             ... {
                if (dtInserted != null)
                    dtInserted.Dispose();
                if (dtDeleted != null)
                    dtDeleted.Dispose();
                if (dtUpdated != null)
                    dtUpdated.Dispose();
                MessageBox.Show("更新成功!", "系统提示");
            }

 

注意,访问dtdeleted中的数据要用dr[0,DataRowVersion.Original].ToString(),否则会出现错误.