none
datagridview 如何根据订单表 自动更新非手工录入订单明细表的插入,更改,删除 RRS feed

  • 问题

  • 例如套餐字典表 1000

          套餐明细字典表  1000,0,椅子

                               1000,1,桌子

                               1000,2,冰箱

    订单表 新增行   套餐字典编码 1000

    问题是订单明细表非手工录入, 如何根据订单表的数据对套餐明细字典数据对订单明细表进行新加,更新,和删除,排除数据库加触发器和存储过程,

    请教各位前辈,如何通过dataset,SqlDataAdapter实现这种需求,谢谢各位!

    2012年2月10日 9:23

答案

全部回复

  • 您好,您可以用 SQLCommandBuilder - GetUpdateCommand, GetInsertCommand and GetDeleteCommand Questions
    搭配 DataAdapter.Update ,來針對整個DataSet or DataTable更新到DB去哦!

    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    • 已标记为答案 doctor_zhou 2012年2月11日 11:47
    2012年2月10日 9:46
  • 亂馬客,你好!

    门诊费用明细表的数据时根据申请单内容进行自动更新,我这个是实现了一对一的,没有实现1对多的?请问还有其他办法吗?

    SqlConnection conn = new SqlConnection(DBhelper.ConnectionStr);
                SqlTransaction trans = null;
    
                SqlCommandBuilder ca = new SqlCommandBuilder(da);
                da.UpdateBatchSize = ds.Tables["view_jy_apply_record"].Rows.Count;
    
                DataTable tbModified = ds.Tables["view_jy_apply_record"].GetChanges(DataRowState.Modified);//获取修改过的行
    
                DataTable tbDeleted = ds.Tables["view_jy_apply_record"].GetChanges(DataRowState.Deleted);//获取标记为删除的行
    
                DataTable tbAdded = ds.Tables["view_jy_apply_record"].GetChanges(DataRowState.Added);//获取增加的行
    
               //问题主要集中在这了,申请单的套餐是标准字典,就是根据ds.Tables["view_jy_apply_record"]的数据对另外一张表的进行费用表更新!我这个方式可以实现,但是感觉不好!
    
                da.UpdateCommand = new SqlCommand(
                   @"UPDATE jy_apply_record  set patient_id = @patient_id ,
                                                 times = @times,
                                                  jy_item_code = @jy_item_code,
                                                  jy_item_name = @jy_item_name,
                                                  sample_code = @sample_code,
                                                  dept_code =  @dept_code ,
                                                  doctor_code = @doctor_code,
                                                  apply_date = @apply_date ,
                                                  status_code =  @status_code ,
                                                  price_flag = @price_flag ,
                                                  st_code = @st_code ,
                                                  icd_code = @icd_code,
                                                  comment = @comment,
                                                  charge_price = @charge_price ,
                                                  response_type=@response_type,
                                                  bill_item_code=@bill_item_code
                                                  WHERE jy_no = @jy_no");
                da.UpdateCommand.Parameters.Add("@patient_id", SqlDbType.NVarChar, 12, "patient_id");
                da.UpdateCommand.Parameters.Add("@times", SqlDbType.NVarChar, 3, "times");
                da.UpdateCommand.Parameters.Add("@jy_item_code", SqlDbType.NVarChar, 7, "jy_item_code");
                da.UpdateCommand.Parameters.Add("@jy_item_name", SqlDbType.NVarChar, 50, "jy_item_name");
                da.UpdateCommand.Parameters.Add("@bill_item_code", SqlDbType.NVarChar, 3, "bill_item_code");
                da.UpdateCommand.Parameters.Add("@sample_code", SqlDbType.NVarChar, 5, "sample_code");
                da.UpdateCommand.Parameters.Add("@dept_code", SqlDbType.NVarChar, 7, "dept_code");
                da.UpdateCommand.Parameters.Add("@doctor_code", SqlDbType.NVarChar, 15, "doctor_code");
                da.UpdateCommand.Parameters.Add("@apply_date", SqlDbType.DateTime, 15, "apply_date");
                da.UpdateCommand.Parameters.Add("@status_code", SqlDbType.NVarChar, 1, "status_code");
                da.UpdateCommand.Parameters.Add("@price_flag", SqlDbType.NVarChar, 1, "price_flag");
                da.UpdateCommand.Parameters.Add("@st_code", SqlDbType.Bit, 1, "st_code");
                da.UpdateCommand.Parameters.Add("@icd_code", SqlDbType.NVarChar, 10, "icd_code");
                da.UpdateCommand.Parameters.Add("@comment", SqlDbType.NVarChar, 15, "comment");
                da.UpdateCommand.Parameters.Add("@charge_price", SqlDbType.Decimal, 15, "charge_price");
                da.UpdateCommand.Parameters.Add("@response_type", SqlDbType.Char, 2, "response_type");
                da.UpdateCommand.Parameters.Add("@jy_no", SqlDbType.Int, 20, "jy_no");
    
               
                da.InsertCommand = new SqlCommand(
                   @"insert into jy_apply_record  (   jy_no ,
                                                      patient_id ,
                                                      times ,
                                                      jy_item_code ,
                                                      jy_item_name,
                                                      sample_code ,
                                                      dept_code ,
                                                      doctor_code ,
                                                      apply_date ,
                                                      status_code ,
                                                      price_flag ,
                                                      st_code ,
                                                      icd_code ,
                                                      comment ,
                                                      charge_price,
                                                      response_type,
                                                     bill_item_code)      
                                              VALUES( @jy_no,
                                                   @patient_id ,
                                                   @times,
                                                   @jy_item_code,
                                                   @jy_item_name,
                                                   @sample_code,
                                                    @dept_code ,
                                                   @doctor_code,
                                                   @apply_date ,
                                                   @status_code ,
                                                   @price_flag ,
                                                   @st_code ,
                                                   @icd_code,
                                                   @comment,
                                                   @charge_price ,
                                                   @response_type,
                                                   @bill_item_code
                                                   );");
                 
                                                       
                da.InsertCommand.Parameters.Add("@jy_no", SqlDbType.Int, 20, "jy_no");
                da.InsertCommand.Parameters.Add("@patient_id", SqlDbType.NVarChar, 12, "patient_id");
                da.InsertCommand.Parameters.Add("@times", SqlDbType.NVarChar, 3, "times");
                da.InsertCommand.Parameters.Add("@jy_item_code", SqlDbType.NVarChar, 7, "jy_item_code");
                da.InsertCommand.Parameters.Add("@jy_item_name", SqlDbType.NVarChar, 50, "jy_item_name");
                da.InsertCommand.Parameters.Add("@bill_item_code", SqlDbType.NVarChar, 3, "bill_item_code");
                da.InsertCommand.Parameters.Add("@sample_code", SqlDbType.NVarChar, 5, "sample_code");
                da.InsertCommand.Parameters.Add("@dept_code", SqlDbType.NVarChar, 7, "dept_code");
                da.InsertCommand.Parameters.Add("@doctor_code", SqlDbType.NVarChar, 15, "doctor_code");
                da.InsertCommand.Parameters.Add("@apply_date", SqlDbType.DateTime, 15, "apply_date");
                da.InsertCommand.Parameters.Add("@status_code", SqlDbType.NVarChar, 1, "status_code");
                da.InsertCommand.Parameters.Add("@price_flag", SqlDbType.NVarChar, 1, "price_flag");
                da.InsertCommand.Parameters.Add("@st_code", SqlDbType.Bit, 1, "st_code");
                da.InsertCommand.Parameters.Add("@icd_code", SqlDbType.NVarChar, 10, "icd_code");
                da.InsertCommand.Parameters.Add("@comment", SqlDbType.NVarChar, 15, "comment");
                da.InsertCommand.Parameters.Add("@charge_price", SqlDbType.Decimal, 15, "charge_price");
                da.InsertCommand.Parameters.Add("@response_type", SqlDbType.Char, 2, "response_type");
    
    
                da.DeleteCommand = new SqlCommand(
                   "update jy_apply_record set delete_flag='1'" +
                   "WHERE jy_no = @jy_no");
    
                da.DeleteCommand.Parameters.Add(
                  "@jy_no", SqlDbType.Int, 20, "jy_no");
    
                da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
    
                SqlCommandBuilder ca3 = new SqlCommandBuilder(mz_detail_charge_da);
    
                mz_detail_charge_da.UpdateCommand = new SqlCommand(
                             @"UPDATE mz_detail_charge
                                          SET 
                                          --     [patient_id] = @patient_id, 
                                         --     ,[times] = @times
                                          --    ,[ledger_sn] = @ledger_sn 
                                         --     ,[yz_type] = @yz_type
                                        --      ,[Rx_no] = @Rx_no
                                         --     ,[serial] = @serial
                                         --     , [name] = @name
                                               [standard_code] = @standard_code
                                              ,[item_name] = @item_name
                                              ,[charge_status] = @charge_status
                                              ,[bill_code] = @bill_code
                                              ,[audit_code] = @audit_code 
                                              ,[price] = @price
                                              ,[charge_price] = @charge_price
                                              ,[charge_amount] = @charge_amount 
                                              ,[back_amount] = @back_amount 
                                              ,[happen_date] = @happen_date
                                              ,[price_date] = @price_date 
                                              ,[price_opera] = @price_opera
                                              ,[self_flag] = @self_flag
                                              ,[report_date] = @report_date
                                              ,[comment] = @comment
                                            --  ,[response_type] = @response_type
                                              ,[confirm] = @confirm
                                              ,[receipt_sn] = @receipt_sn
                                              ,[dept_code] = @dept_code 
                                              ,[doctor_code] = @doctor_code 
                                              ,[exec_dept] = @exec_dept
                                              ,[apply_date] = @apply_date
                                              ,[amount_unit] = @amount_unit                                      
                                                  WHERE Rx_no = @Rx_no 
                                                   and     yz_type=@yz_type
                                                    and    serial=@serial");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@patient_id", SqlDbType.NVarChar, 12).Value = p.patient_id;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@times", SqlDbType.Int).Value = p.times;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@ledger_sn", SqlDbType.Int).Value = p.ledger_sn;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@yz_type", SqlDbType.Int).Value = 2;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@Rx_no", SqlDbType.Int, 20, "jy_no");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@serial", SqlDbType.Int, 10).Value = 0;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@name", SqlDbType.NVarChar, 20).Value = p.pateint_name;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@standard_code", SqlDbType.NChar, 10, "jy_item_code");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@item_name", SqlDbType.NVarChar, 20, "jy_item_name");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@charge_status", SqlDbType.NVarChar, 1).Value = "1";
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@bill_code", SqlDbType.NVarChar, 3, "bill_item_code");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@audit_code", SqlDbType.NVarChar, 3, "bill_item_code");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@price", SqlDbType.Decimal).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@charge_price", SqlDbType.Decimal, 10, "charge_price");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@charge_amount", SqlDbType.Float, 2).Value = 1;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@amount_unit", SqlDbType.NChar, 2).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@back_amount", SqlDbType.Float).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@happen_date", SqlDbType.DateTime).Value = DateTime.Now;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@price_date", SqlDbType.DateTime).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@price_opera", SqlDbType.NVarChar, 5).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@self_flag", SqlDbType.Bit, 1).Value=0;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@report_date", SqlDbType.DateTime).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@comment", SqlDbType.NVarChar, 50, "comment");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@response_type", SqlDbType.Char, 2).Value = p.response_type;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@confirm", SqlDbType.Int).Value = 1;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@receipt_sn", SqlDbType.Int).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@dept_code", SqlDbType.NVarChar, 7).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@doctor_code", SqlDbType.NVarChar, 5).Value = DBNull.Value;
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@exec_dept", SqlDbType.NVarChar, 7, "dept_code");
                mz_detail_charge_da.UpdateCommand.Parameters.Add("@apply_date", SqlDbType.DateTime).Value = DBNull.Value;
    
                mz_detail_charge_da.InsertCommand = new SqlCommand(
                   @"insert into mz_detail_charge  (       patient_id
                                                           ,times
                                                           ,ledger_sn
                                                           ,yz_type
                                                           ,Rx_no
                                                           ,serial
                                                           ,name
                                                           ,standard_code
                                                           ,item_name
                                                           ,charge_status
                                                           ,bill_code
                                                           ,audit_code
                                                           ,price
                                                           ,charge_price
                                                           ,charge_amount
                                                           ,amount_unit
                                                           ,back_amount
                                                           ,happen_date
                                                           ,price_date
                                                           ,price_opera
                                                           ,self_flag
                                                           ,report_date
                                                           ,comment
                                                           ,response_type
                                                           ,confirm
                                                           ,receipt_sn
                                                           ,dept_code
                                                           ,doctor_code
                                                           ,exec_dept
                                                           ,apply_date
                                              ) 
                                       VALUES(
                                                            @patient_id
                                                           ,@times
                                                           ,@ledger_sn
                                                           ,@yz_type
                                                           ,@Rx_no
                                                           ,@serial
                                                           ,@name
                                                           ,@standard_code
                                                           ,@item_name
                                                           ,@charge_status
                                                           ,@bill_code
                                                           ,@audit_code
                                                           ,@price
                                                           ,@charge_price
                                                           ,@charge_amount
                                                           ,@amount_unit
                                                           ,@back_amount
                                                           ,@happen_date
                                                           ,@price_date
                                                           ,@price_opera
                                                           ,@self_flag
                                                           ,@report_date
                                                           ,@comment
                                                           ,@response_type
                                                           ,@confirm
                                                           ,@receipt_sn
                                                           ,@dept_code
                                                           ,@doctor_code
                                                           ,@exec_dept
                                                           ,@apply_date
                                                     
                                              )");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@patient_id", SqlDbType.NVarChar, 12).Value = p.patient_id;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@times", SqlDbType.Int).Value = p.times;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@ledger_sn", SqlDbType.Int).Value = p.ledger_sn;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@yz_type", SqlDbType.Int).Value = 2;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@Rx_no", SqlDbType.Int, 20, "jy_no");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@serial", SqlDbType.Int, 10).Value = 0;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 20).Value = p.pateint_name;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@standard_code", SqlDbType.NChar, 10, "jy_item_code");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@item_name", SqlDbType.NVarChar, 20, "jy_item_name");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@charge_status", SqlDbType.NVarChar, 1).Value = "1";
                mz_detail_charge_da.InsertCommand.Parameters.Add("@bill_code", SqlDbType.NVarChar, 3, "bill_item_code");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@audit_code", SqlDbType.NVarChar, 3, "bill_item_code");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@price", SqlDbType.Decimal).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@charge_price", SqlDbType.Decimal, 10, "charge_price");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@charge_amount", SqlDbType.Float, 2).Value = 1;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@amount_unit", SqlDbType.NChar, 2).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@back_amount", SqlDbType.Float).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@happen_date", SqlDbType.DateTime).Value = DateTime.Now;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@price_date", SqlDbType.DateTime).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@price_opera", SqlDbType.NVarChar, 5).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@self_flag", SqlDbType.Bit, 1).Value = 0;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@report_date", SqlDbType.DateTime).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@comment", SqlDbType.NVarChar, 50, "comment");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@response_type", SqlDbType.Char, 2).Value = p.response_type;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@confirm", SqlDbType.Int).Value = 1;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@receipt_sn", SqlDbType.Int).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@dept_code", SqlDbType.NVarChar, 7).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@doctor_code", SqlDbType.NVarChar, 5).Value = DBNull.Value;
                mz_detail_charge_da.InsertCommand.Parameters.Add("@exec_dept", SqlDbType.NVarChar, 7, "dept_code");
                mz_detail_charge_da.InsertCommand.Parameters.Add("@apply_date", SqlDbType.DateTime).Value = DBNull.Value;
    
                mz_detail_charge_da.DeleteCommand = new SqlCommand(
                   @"delete mz_detail_charge " +
                   "WHERE Rx_no = @Rx_no and  yz_type=2 and serial=0 ");
    
    
                mz_detail_charge_da.DeleteCommand.Parameters.Add(
                  "@Rx_no", SqlDbType.Int, 20, "jy_no");
    
               
    
    
                  try
                {
    
                    conn.Open();
    
                    da.DeleteCommand.Connection = conn;
                    da.InsertCommand.Connection = conn;
                    da.UpdateCommand.Connection = conn;
    
                    mz_detail_charge_da.InsertCommand.Connection = conn;
                    mz_detail_charge_da.DeleteCommand.Connection = conn;
                    mz_detail_charge_da.UpdateCommand.Connection = conn;
    
                    trans = conn.BeginTransaction();
    
    
                    da.DeleteCommand.Transaction = trans;
                    da.InsertCommand.Transaction = trans;
                    da.UpdateCommand.Transaction = trans;
    
                  
    
                    mz_detail_charge_da.DeleteCommand.Transaction = trans;
                    mz_detail_charge_da.InsertCommand.Transaction = trans;
                    mz_detail_charge_da.UpdateCommand.Transaction = trans;
    
     
    
                    da.Update(ds, "view_jy_apply_record");
                 
    
                    if (tbDeleted != null)
                    { mz_detail_charge_da.Update(tbDeleted); }
                    if (tbModified != null)
                    { mz_detail_charge_da.Update(tbModified); }
                    if (tbAdded != null)
                    { mz_detail_charge_da.Update(tbAdded); }
    
                    trans.Commit();
                    lblInfo = "";
                }
    
                catch (Exception ex)
                {
    
                    trans.Rollback();
                    lblInfo = "处方保存错误:" + ex.Message;
                    //throw new Exception("更新数据出错", ex);
    
                }
    
                finally
                {
    
                    if (conn.State != ConnectionState.Closed)
    
                        conn.Close();
    
                }
    
    

    2012年2月10日 10:45
  • Hi doctor_zhou,

    欢迎来到MSDN论坛!

    您可以考虑下在相关表之间建立外键。

    谢谢!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    2012年2月14日 5:38
    版主
  • 您上面的Insert, Update, Delete的command,可透過CommandBuilder來產生,而不用自已去Assign,而且SqlDataAdapter可直接針對整個DataTable做更新到DB去哦!
    如下的Code,給您參考,謝謝!
    SqlCommandBuilder ca = new SqlCommandBuilder(da);
    ca.ConflictOption = ConflictOption.OverwriteChanges; //Where 以PK更新
    da.SelectCommand = new SqlCommand(@"select * from view_jy_apply_record_talbe", conn);
    da.UpdateCommand = ca.GetUpdateCommand();
    da.InsertCommand = ca.GetInsertCommand();
    da.DeleteCommand = ca.GetDeleteCommand();
    //直接update到db去
    da.Update(ds.Tables["view_jy_apply_record"]);

    http://www.dotblogs.com.tw/rainmaker/archive/2012/02/03/67650.aspx

    您可透過 DbDataAdapter.Update 方法 (DataSet) 方式做批次更新!

    或是參考使用ORM的方式!

    如,

    LINQ to SQL

    实体框架概述


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    2012年2月14日 6:09