VOL框架后台事务处理的两种方式

VOL框架后台事务处理的两种方式

方式一：框架自带增删改的事务处理

        public override WebResponseContent Del(object[] keys, bool delList = true)
        {
            string ids = string.Join(",", keys);
            string asql = $@"
                        SELECT sum(auditStatus) AS auditstatus
                        FROM ST_LockUnlock 
                        WHERE LockUnlockID IN ({ids})";
            var data = DBServerProvider.SqlDapper.QueryDynamicList(asql, "");
            if (data[0].auditstatus == null)
            {
                return webResponse.Error("所选记录不存在，请刷新后重新选择！");
            }
            else if (data[0].auditstatus > 0)
            {
                return webResponse.Error("只能删除状态为[待审批]的数据，请刷新后重新选择！");
            }
            else
            {
                DelOnExecuted = (keys) =>
                {
                    string stepDel = $@"
                        delete FROM Sys_WorkFlowTableStep 
                        WHERE WorkFlowTable_Id IN 
                        (
                            SELECT workflowtable_id 
                            FROM Sys_WorkFlowTable
                            WHERE WorkTableKey IN ({ids}) AND WorkTable='ST_LockUnlock'
                        )";
                    repository.ExecuteSqlCommand(stepDel);//事务处理只能用该方法
                    string tableDel = $@"
                        delete FROM Sys_WorkFlowTable
                        WHERE WorkTableKey IN ({ids}) AND WorkTable='ST_LockUnlock'";
                    repository.ExecuteSqlCommand(tableDel);

                    return webResponse.OK();
                };
                return base.Del(keys, delList);
            }

        }

方式二：自定义方法的事务处理

        public async Task<WebResponseContent> Confirm(string id)
        {
            var dapper = DBServerProvider.SqlDapper;
            try
            {
                //开始事务处理
                dapper.BeginTrans();

                string sql = @$"
                    UPDATE ST_MeterMonthVolMain 
                    SET ConfirmState='1',ConfirmID={UserContext.Current.UserInfo.User_Id},Confirmor='{UserContext.Current.UserInfo.UserTrueName}',ConfirmDate='{DateTime.Now}'
                    WHERE MonthCompMainID='{id}'";
                await dapper.ExcuteNonQueryAsync(sql, "");

                string subsql = $@"
                    SELECT a.MonthCompID,a.MeterMonth,a.FeeDate,a.FinalVol,a.Price,a.ReceivableFee,
                        b.MeterID,b.MeterCode,b.MeterName,
                        c.CustomerID,c.CustCode,c.CustName,c.INVCustName,c.CompanyCode,c.AccBanlance,c.AccDeposit
                    FROM ST_MeterMonthVol a
                    LEFT JOIN ST_CodeMeter b ON b.MeterID = a.MeterID
                    LEFT JOIN ST_CustomerInfo c ON c.CustomerID = b.CustomerID
                    WHERE a.MonthCompMainID='{id}'
                ";
                List<meterConfirmParam> list = DBServerProvider.SqlDapper.QueryList<meterConfirmParam>(subsql, "");

                StringBuilder sb = new StringBuilder();
                sb.Append($@"
                        insert into ST_FeeReceivable (MonthCompID,CustomerID,CustCode,CustName,MeterID,MeterName,CompanyCode,
                            Vol,Price,Amount,AccBanlance,AccDeposit,
                            MeterMonth,Unit,FeeName,PaidFee,WaitFee,InvFee,InvFlag,FeeDate)
                        values ");
                
                foreach(meterConfirmParam item in list)
                {
                    sb.Append($@"
                        ({item.MonthCompID},{item.CustomerID},'{item.CustCode}','{item.CustName}',{item.MeterID},'{item.MeterName}','{item.CompanyCode}',
                        {item.FinalVol},{item.Price},{item.ReceivableFee},{item.ReceivableFee},{item.AccBanlance},{item.AccDeposit},
                        '{item.MeterMonth}','吨','蒸汽往来',0,0,0,0,'{DateTime.Now}'),");

                }
                sb.Remove(sb.Length - 1, 1);
                await dapper.ExcuteNonQueryAsync(sb.ToString(), "");

                dapper.Commit();

                webResponse.Status = true;
                webResponse.Message = "确认成功！";
                return webResponse.OK();
            }
            catch (Exception ex)
            {
                dapper.Rollback();
                return webResponse.Error("确认失败！"+ex.Message);
            }
        }