c#同时更新主表和明细表，如果保证两个表的数据同时更新成功

主表存储过程usp_sys_cw_addpay

ALTER PROCEDURE [dbo].[usp_sys_cw_addpay](
	@appname VARCHAR(50)='',
	@apptotal DECIMAL
)
AS
BEGIN
SET NOCOUNT ON;
  -- routine body goes here, e.g.
  -- SELECT 'Navicat for SQL Server'
		DECLARE @docentry INT
    insert into CW_pay(appname,apptotal,appdate,status,remark,ckname)  
          values(@appname,@apptotal,GETDATE(),-1,'','')  
    select @docentry=@@identity  
     RETURN @docentry
END

 字表存储过程usp_sys_cw_addpay_dt1

ALTER PROCEDURE [dbo].[usp_sys_cw_addpay_dt1](
@docentry INT,
@docnum INT,
@cardcode VARCHAR(50)='',
@cardname VARCHAR(200)='',
@pymntgroup VARCHAR(200)='',
@docduedate VARCHAR(50)='',
@doctotal DECIMAL,
@paidtodate DECIMAL,
@apppay DECIMAL,
@u_name VARCHAR(50)=''
)
AS
BEGIN
SET NOCOUNT ON;
    insert into CW_paydt1(docentry,docnum,cardcode,cardname,pymntgroup,docduedate,doctotal,paidtodate,apppay,u_name)  
          values(@docentry,@docnum,@cardcode,@cardname,@pymntgroup,@docduedate,@doctotal,@paidtodate,@apppay,@u_name)
END

 写入数据库，当主表和子表都更新成功后才更新到数据库，如果失败或者异常则回滚

 1 private void apppay_Click(object sender, EventArgs e)
 2          {
 3              SqlConnection connection = new SqlConnection(allenSingleton.strCon);
 4              SqlCommand command = connection.CreateCommand();
 5              //事务处理，保证数据完整性
 6              SqlTransaction transaction;
 7              connection.Open();
 8              transaction = connection.BeginTransaction();
 9              command.Transaction = transaction;
10              command.CommandType = CommandType.StoredProcedure;
11 
12              try
13              {
14                  //写入主表
15                  command.CommandText = "usp_sys_cw_addpay";
16                  SqlParameter[] parms = new SqlParameter[] { 
17                 new SqlParameter("@appname",SqlDbType.VarChar),
18                 new SqlParameter("@apptotal",SqlDbType.Decimal),
19                 new SqlParameter("@return",SqlDbType.Int)  
20                 };
21                  parms[0].Value = allenSingleton.UserName;
22                  parms[1].Value = Convert.ToDecimal(dt.Compute("Sum(apppay)", "true").ToString().Trim());
23                  parms[2].Direction = ParameterDirection.ReturnValue;
24                  command.Parameters.AddRange(parms);
25                  command.ExecuteNonQuery();
26 
27                  string docentry = parms[2].Value.ToString().Trim();
28                  MessageBox.Show(docentry.ToString());
29                  //写入子表
30                  for (int i = 0; i < dt.Rows.Count; i++)
31                  {
32                      command.CommandText = "usp_sys_cw_addpay_dt1";
33                      command.Parameters.Clear();
34                      parms = new SqlParameter[] { 
35                     new SqlParameter("@docentry",int.Parse(docentry)),
36                     new SqlParameter("@docnum",int.Parse(dt.Rows[i]["docnum"].ToString().Trim())),
37                     new SqlParameter("@cardcode",dt.Rows[i]["cardcode"].ToString().Trim()),
38                     new SqlParameter("@cardname",dt.Rows[i]["cardname"].ToString().Trim()),
39                     new SqlParameter("@pymntgroup",dt.Rows[i]["pymntgroup"].ToString().Trim()),
40                     new SqlParameter("@docduedate",dt.Rows[i]["docduedate"].ToString().Trim()),
41                     new SqlParameter("@doctotal",decimal.Parse(dt.Rows[i]["doctotal"].ToString().Trim())),
42                     new SqlParameter("@paidtodate",decimal.Parse(dt.Rows[i]["paidtodate"].ToString().Trim())),
43                     new SqlParameter("@apppay",decimal.Parse(dt.Rows[i]["apppay"].ToString().Trim())),
44                     new SqlParameter("@u_name",dt.Rows[i]["u_name"].ToString().Trim())};
45                      command.Parameters.AddRange(parms);
46                      command.ExecuteNonQuery();
47                  }
48                  transaction.Commit();
49              }
50              catch (Exception ex)
51              {
52                  transaction.Rollback();
53                  throw;
54              }
55              finally
56              {
57                  connection.Close();
58                  transaction.Dispose();
59                  connection.Dispose();
60              }
61          }

 

posted @ 2017-05-26 14:40  mingxiu  阅读(2607)  评论(0)  编辑  收藏  举报