C# DAL.executeScalar方法代码示例

        public int insertloanCharge(String chargeName, double amount, String amountStatus, ArrayList loanTypeNo, int Status)
        {
            int id = 0;
             int resultInner = 0;
             DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
             String sql = "EXEC insertCharges @Status, @ChargeName, @Amount, @AmountStatus, @isArchived";
             Dictionary<String, Object> parameters = new Dictionary<string, object>();
             parameters.Add("@Status", Status);
             parameters.Add("@ChargeName", chargeName);
             parameters.Add("@Amount", amount);
             parameters.Add("@AmountStatus", amountStatus);
             parameters.Add("@isArchived", 0);
             int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));

             String sqlSelect = "Select ChargeId from CHARGES where isArchived=0 and ChargeName= " + "'" + chargeName + "'";
             id = Convert.ToInt32(dal.executeScalar(sqlSelect));

             if (result != 0)
             {

                 foreach (int i in loanTypeNo)
                 {
                     String sqlInner = "INSERT INTO LOAN_TYPE_CHARGES VALUES (@LoanTypeId, @ChargeId)";
                     Dictionary<String, Object> parametersInner = new Dictionary<string, object>();
                     parametersInner.Add("@LoanTypeId", i);
                     parametersInner.Add("@ChargeId", id);
                     resultInner = Convert.ToInt32(dal.executeNonQuery(sqlInner, parametersInner));
                 }

             }

             return resultInner;
        }

 public int checkIfLoanIsCollateralized(int loanTypeId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select isCollateral from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
     int isCollateral = Convert.ToInt32(dal.executeScalar(sql));
     return isCollateral;
 }

 public int checkIfLoanHasComakers(int loanTypeId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select CoMaker from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
     int comaker = Convert.ToInt32(dal.executeScalar(sql));
     return comaker;
 }

 public String checkSpouseName(String accountNo)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select SpouseName from MEMBER WHERE AccountNo = " + "'" + accountNo + "'";
     String spouse = Convert.ToString(dal.executeScalar(sql));
     return spouse;
 }

        public int insertFee(String feeDesc, Double feeAmount, ArrayList memberTypeNo, int Status)
        {
            int resultInner = 0;
             DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
             String sql = "EXEC insertFee @feeDesc, @Amount, @Status;";
             Dictionary<String, Object> parameters = new Dictionary<string, object>();
             parameters.Add("@feeDesc", feeDesc);
             parameters.Add("@Amount", feeAmount);
             parameters.Add("@Status", Status);
             int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));
             Object rs = dal.executeScalar("SELECT @@IDENTITY");     //problem area
             int id = int.Parse(rs.ToString());
             if (id != 0)
             {

                 foreach (int i in memberTypeNo)
                 {
                     String sqlInner = "INSERT INTO MEMBER_TYPE_FEE (MemberTypeNo, FeeId) VALUES (@memberTypeNo, @feeId)";
                     Dictionary<String, Object> parametersInner = new Dictionary<string, object>();
                     parametersInner.Add("@memberTypeNo", i);
                     parametersInner.Add("@feeId", id);
                     resultInner = Convert.ToInt32(dal.executeNonQuery(sqlInner, parametersInner));
                 }

             }

             return resultInner;
        }

 public DataSet selectActiveMember()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select Member.AccountNo as 'Account No.', concat(Member.FirstName,' ',Member.MiddleName,' ',Member.LastName) as 'Account Name', Member_Type.Description AS 'Member Type' from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo and AccountNo not in (Select AccountNo from Termination)";
     String sqlct = "Select COUNT(Member.AccountNo) from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo and AccountNo not in (Select AccountNo from Termination)";
     DataSet ds = dal.executeDataSet(sql);
     checkEmpty = Convert.ToInt32(dal.executeScalar(sqlct));
     return ds;
 }

        public int InsertMinCapitalContrib()
        {
            DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
            String sqlUpdate = "Update MINIMUM_CAPITAL_CONTRIBUTION set isArchived = 1 where MemberTypeNo = "+"'"+this.comboAccountType+"'";
            dal.executeScalar(sqlUpdate);

            String sql = "EXEC SpInsert_MinimumCapitalContribution @MinimumContribution,@Duration,@Status,@MemberTypeNo";
            Dictionary<String, Object> parameters = new Dictionary<string, object>();
            parameters.Add("@MinimumContribution", this.txtContribution);
            parameters.Add("@Duration", this.comboContribution);
            parameters.Add("@Status", this.Status);
            parameters.Add("@MemberTypeNo", this.comboAccountType);
            int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));
            return result;
        }

        public void insertInterestRate()
        {
            DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);

            String updateSql = "Update LOAN_INTEREST_RATE set isArchived = 1 where LoanTypeId = " + "'" + this.LoanTypeId + "'";
            dal.executeScalar(updateSql);

            String sql = "EXEC insertLoanInterestRate @LoanTypeId, @InterestRateStatus, @InterestRateValue, @Per, @ActivationDate, @Status";
            Dictionary<String, Object> parameters = new Dictionary<string, object>();

            parameters.Add("@LoanTypeId", this.LoanTypeId);
            parameters.Add("@InterestRateStatus", this.InterestRateStatus);
            parameters.Add("@InterestRateValue", this.InterestRate);
            parameters.Add("@Per", this.Per);
            parameters.Add("@ActivationDate", this.DateFrom);
            parameters.Add("@Status", this.Status);
            dal.executeNonQuery(sql, parameters);
        }

        public void insertLoanPayment(String paymentType, String accountNo, int applicationId, double amount, String duedate, Boolean hasInterest)
        {
            DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
            String sqlSelect = "Select AmortizationId from loan_amortization where LoanApplicationId = " + "'" + applicationId + "' and AmortizationDueDate ="+"'"+duedate+"'";
            int amortizationId = Convert.ToInt32(dal.executeScalar(sqlSelect));

            String sql = "EXEC insertAmortizationPayment @PaymentType, @AmountPaid, @hasInterest, @AccountNo, @AmortizationId, @LoanApplicationId, @isFullyPaid";
            Dictionary<String, Object> parameters = new Dictionary<string, object>();
            parameters.Add("@PaymentType", paymentType);
            parameters.Add("@AmountPaid", amount);
            parameters.Add("@hasInterest", hasInterest);
            parameters.Add("@AccountNo", accountNo);
            parameters.Add("@AmortizationId", amortizationId);
            parameters.Add("@LoanApplicationId", applicationId);
            parameters.Add("@isFullyPaid", 0);
            dal.executeNonQuery(sql, parameters);

            String sql2 = "Update LOAN_AMORTIZATION set isPaid = 1 where AmortizationId =" +"'"+amortizationId+"'";
            dal.executeScalar(sql2);

            String sql3 = "Select count(AmortizationId) from LOAN_AMORTIZATION WHERE isPaid = 1 and LoanApplicationId= "+"'"+applicationId+"'";
            int countPaid = Convert.ToInt32(dal.executeScalar(sql3));

            String sql4 = "Select count(AmortizationId) from LOAN_AMORTIZATION WHERE LoanApplicationId= " + "'" + applicationId + "'";
            int countAmortization = Convert.ToInt32(dal.executeScalar(sql4));

            if (countPaid == countAmortization)
            {
                String sql5 = "Update PAYMENT set isFullyPaid = 1 where LoanApplicationId =" + "'" + applicationId + "'";
                dal.executeScalar(sql5);

                String sql6 = "Update LOAN_INFORMATION set isCleared = 1 where LoanApplicationId =" + "'" + applicationId + "'";
                dal.executeScalar(sql6);

            }
        }

 public double selectRemainingBalance(int loanApplicationId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select sum(Amount) from loan_amortization where isPaid = 0 and LoanApplicationId = " + "'" + loanApplicationId + "'";
     double amount = Convert.ToDouble(dal.executeScalar(sql));
     return amount;
 }

 public int selectPaymentDatesWithInterestRates(int loanApplicationId, String firstDate, String secondDate)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select count(ORNo) from PAYMENT where hasInterest = 1 and LoanApplicationId = "+"'"+loanApplicationId+"'"+" and PaymentDate between "+"'"+firstDate+"'"+ "and " + "'" + secondDate + "'";
     int i = Convert.ToInt32(dal.executeScalar(sql));
     return i;
 }

 public String selectMaturityDate(int loanApplicationId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select MaturityDate from LOAN_INFORMATION where LoanApplicationId = " + "'" + loanApplicationId + "'";
     String mdate = Convert.ToString(dal.executeScalar(sql));
     return mdate;
 }

 public String selectInterestPerLoanType(int loanTypeId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select concat(InterestRateStatus,' ',InterestRateValue,' ',Per) from LOAN_INTEREST_RATE where (CURRENT_TIMESTAMP >= ActivationDate) and Status = 1 and isArchived = 0 and LoanTypeId =" + "'" + loanTypeId + "'";
     String interest = Convert.ToString(dal.executeScalar(sql));
     return interest;
 }

 public double selectGrantedLoanAmount(int loanApplicationId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select ApprovedAmount from LOAN_INFORMATION where loanapplicationid = " + "'" + loanApplicationId + "'";
     double amount = Convert.ToDouble(dal.executeScalar(sql));
     return amount;
 }

 public int selectPreviousEligibility(int loanTypeId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select isPreviousUnpaid from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
     int elig = Convert.ToInt32(dal.executeScalar(sql));
     return elig;
 }