C# SIOTSDB_HHZXDataContext.ExecuteCommand方法代码示例

        public ReturnValueInfo InsertRecord(PaymentUDGeneralSetting_pus_Info infoObject)
        {
            ReturnValueInfo returnInfo = new ReturnValueInfo(false);

            if (infoObject != null)
            {
                try
                {
                    using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                    {
                        string sqlStr = string.Empty;

                        sqlStr += "delete from " + Environment.NewLine;

                        sqlStr += "dbo.PaymentUDGeneralSetting_pus" + Environment.NewLine;

                        sqlStr += "where 1=1" + Environment.NewLine;

                        if (infoObject.pus_cGradeID != null)
                        {
                            sqlStr += "and pus_cGradeID='" + infoObject.pus_cGradeID.Value.ToString() + "'" + Environment.NewLine;
                        }

                        if (infoObject.pus_cCardUserID != null)
                        {
                            sqlStr += "and pus_cCardUserID='" + infoObject.pus_cCardUserID.Value.ToString() + "'" + Environment.NewLine;
                        }

                        if (infoObject.pus_cClassID != null)
                        {
                            sqlStr += "and pus_cClassID='" + infoObject.pus_cClassID.Value.ToString() + "'" + Environment.NewLine;
                        }

                        if (infoObject.pus_iWeek != null)
                        {
                            sqlStr += "and pus_iWeek=" + infoObject.pus_iWeek.Value.ToString() + "" + Environment.NewLine;
                        }

                        db.ExecuteCommand(sqlStr, new object[] { });

                        PaymentUDGeneralSetting_pus insertData = Common.General.CopyObjectValue<PaymentUDGeneralSetting_pus_Info, PaymentUDGeneralSetting_pus>(infoObject);

                        db.PaymentUDGeneralSetting_pus.InsertOnSubmit(insertData);

                        db.SubmitChanges();

                        returnInfo.boolValue = true;
                    }
                }
                catch (Exception Ex)
                {

                    returnInfo.messageText = Ex.Message;
                }
            }

            return returnInfo;
        }

//.........这里部分代码省略.........
            from ConsumeRecord_csr with(nolock)
            join(
            select UserID=csr_cCardUserID,MaxDate=MAX(csr_dConsumeDate)
            from ConsumeRecord_csr with(nolock)
            join vie_AllStudentCardUserInfos on csr_cCardUserID=cus_cRecordID
            group by csr_cCardUserID)A
            on csr_cCardUserID=UserID and csr_dConsumeDate=MaxDate

            insert into @tb_CardRechargeDetail
            select distinct rcr_cUserID,rcr_dRechargeTime,rcr_fBalance
            from RechargeRecord_rcr with(nolock)
            join(
            select UserID=rcr_cUserID,MaxDate=MAX(rcr_dRechargeTime)
            from RechargeRecord_rcr with(nolock)
            join vie_AllStudentCardUserInfos on rcr_cUserID=cus_cRecordID and rcr_dRechargeTime is not null
            and rcr_cRechargeType<>'Recharge_AdvanceMoney'
            group by rcr_cUserID)A
            on rcr_cUserID=UserID and rcr_dRechargeTime=MaxDate

            update CardUserAccount_cua set cua_fCurrentBalance=StuBalance,cua_dLastSyncTime=GETDATE()
            from CardUserAccount_cua Acc with(nolock)
            join(
            select cua_cRecordID,StuBalance=(case when (A.UserID is not null and B.UserID is not null) then
            (case when ConsumeDate>=RechargeDate then A.CardBalance else B.CardBalance end)
            when (A.UserID is not null and B.UserID is null) then A.CardBalance
            when (A.UserID is null and B.UserID is not null) then B.CardBalance
            else 0 end)-cua_fSysAdvance
            from vie_AllStudentCardUserInfos
            left join @tb_CardConsumeDetail A on A.UserID=cus_cRecordID
            left join @tb_CardRechargeDetail B on B.UserID=cus_cRecordID
            )TT on Acc.cua_cRecordID=TT.cua_cRecordID

            select * from vie_AllStudentCardUserInfos";
                        db.ExecuteCommand(strStuSycnAccount, new object[] { });

                        #endregion

                        #region 同步教师账户金额信息

                        string strStaffSycnAccount = @"declare @tb_CardConsumeDetail table (UserID uniqueidentifier,ConsumeDate datetime,CardBalance decimal(18,2))
            declare @tb_CardRechargeDetail table (UserID uniqueidentifier,RechargeDate datetime,CardBalance decimal(18,2))

            insert into @tb_CardConsumeDetail
            select distinct csr_cCardUserID,csr_dConsumeDate,csr_fCardBalance
            from ConsumeRecord_csr with(nolock)
            join(
            select UserID=csr_cCardUserID,MaxDate=MAX(csr_dConsumeDate)
            from ConsumeRecord_csr with(nolock)
            join CardUserMaster_cus on csr_cCardUserID=cus_cRecordID
            and cus_cIdentityNum='STAFF'
            group by csr_cCardUserID)A
            on csr_cCardUserID=UserID and csr_dConsumeDate=MaxDate

            insert into @tb_CardRechargeDetail
            select distinct rcr_cUserID,rcr_dRechargeTime,rcr_fBalance
            from RechargeRecord_rcr with(nolock)
            join(
            select UserID=rcr_cUserID,MaxDate=MAX(rcr_dRechargeTime)
            from RechargeRecord_rcr with(nolock)
            join CardUserMaster_cus on rcr_cUserID=cus_cRecordID
            and cus_cIdentityNum='STAFF'
            and rcr_dRechargeTime is not null and rcr_cRechargeType<>'Recharge_AdvanceMoney'
            group by rcr_cUserID)A
            on rcr_cUserID=UserID and rcr_dRechargeTime=MaxDate

            update CardUserAccount_cua set cua_fCurrentBalance=StuBalance,cua_dLastSyncTime=GETDATE()

        public ReturnValueInfo UpdateBatchRecord(List<BlacklistChangeRecord_blc_Info> listRecords)
        {
            ReturnValueInfo rvInfo = new ReturnValueInfo();
            try
            {
                if (listRecords == null || (listRecords != null && listRecords.Count < 1))
                {
                    rvInfo.messageText = Common.DefineConstantValue.SystemMessageText.strMessageText_E_ObjectNull;
                    rvInfo.isError = true;
                    return rvInfo;
                }

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    var groupCardNo = listRecords.GroupBy(x => x.blc_iCardNo);
                    if (groupCardNo != null)
                    {
                        StringBuilder sbSQL = new StringBuilder();
                        string strSQLTemp = "UPDATE BlacklistChangeRecord_blc SET blc_lIsFinished = 1, blc_dFinishDate =GETDATE() WHERE 1=1 AND  blc_lIsFinished = 0";
                        foreach (var itemCardNo in groupCardNo)
                        {
                            int iCardNo = itemCardNo.Key;
                            BlacklistChangeRecord_blc_Info blInfo = itemCardNo.OrderByDescending(x => x.blc_dAddDate).FirstOrDefault();
                            if (blInfo != null)
                            {
                                StringBuilder sbSubSQL = new StringBuilder();
                                sbSubSQL.AppendLine(strSQLTemp);
                                sbSubSQL.AppendLine("AND blc_iCardNo = " + iCardNo);
                                sbSubSQL.AppendLine("AND blc_dAddDate <= '" + blInfo.blc_dAddDate.ToString() + "';");
                                sbSQL.AppendLine(sbSubSQL.ToString());
                            }
                        }
                        if (!string.IsNullOrEmpty(sbSQL.ToString()))
                        {
                            int iRes = db.ExecuteCommand(sbSQL.ToString(), new object[] { });
                        }
                    }
                }
                rvInfo.boolValue = true;
            }
            catch (Exception ex)
            {
                rvInfo.isError = true;
                rvInfo.messageText = ex.Message;
            }
            return rvInfo;
        }