C# DataHandler.ExecuteSQLWithReturnVal方法代码示例

        /*

Frontend page: Reset Password page
Title:  Verifying the user account using usedId and token
Designed: Irfan Mam
User story: 
Developed: Irfan MAM
Date created: 1/17/2016

*/
        public bool verifyAccount(int userId, string token)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_id", userId });
            paramertList.Add(new object[] { "@token", token });
            paramertList.Add(new object[] { "@expired_date", DateTime.Now });

            try
            {
                return dataHandler.ExecuteSQLWithReturnVal("spverifyAccountBytoken", paramertList) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /*

 Frontend page: Fee Page
 Title: Update Fees 
 Designed: Irfan MAM
 User story:
 Developed: Irfan MAM
 Date created: 4/22/2016

*/
        internal int updateFees(List<Fees> lstFee,DateTime paidDate, int loanId , int userId)
        {
            try
            {
                int i = 1;

                // bind the list of fee details to xml 
                XElement xEle = new XElement("Fee",
                    from fee in lstFee
                    select new XElement("FeeUnit",
                        new XElement("FeeId", fee.FeeId),
                        new XElement("Type", fee.Type),
                       
                        
                        new XElement("id", i++)
                        ));
                string xmlDoc = xEle.ToString();


                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList2 = new List<object[]>();
                paramertList2.Add(new object[] { "@loan_id", loanId });
                paramertList2.Add(new object[] { "@paid_date", paidDate });
                paramertList2.Add(new object[] { "@user_id", userId });
                paramertList2.Add(new object[] { "@Input", xmlDoc });

                try
                {
                    return dataHandler.ExecuteSQLWithReturnVal("spUpdateFee", paramertList2);
                }
                catch 
                {
                    return 0;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public int CheckAdvanceFeeAtPayOff(int loanId)
        {
            try
            {
                DataHandler dataHandler = new DataHandler();

                List<object[]> paramertList1 = new List<object[]>();
                paramertList1.Add(new object[] { "@loan_id", loanId });

                return dataHandler.ExecuteSQLWithReturnVal("spCheckAdvanceFeeAtPayOff", paramertList1);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /*

Frontend page: Reset Password page
Title:  Update Password
Designed: Irfan Mam
User story: 
Developed: Irfan MAM
Date created: 1/17/2016

*/
        public bool resetPassword(int userId, ResetPassword resetPasswordModel)
        {
            DataHandler dataHandler = new DataHandler();
            string newSalt = PasswordEncryption.RandomString();
            resetPasswordModel.Password = PasswordEncryption.encryptPassword(resetPasswordModel.Password, newSalt);

            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_id", userId });
            paramertList.Add(new object[] { "@password", resetPasswordModel.Password });
            

            try
            {
                return dataHandler.ExecuteSQLWithReturnVal("spUpdatePassword", paramertList) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        internal int insertLoanStepOne(LoanSetupStep1 loanSetupStep1, int loanId)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            try
            {
            paramertList.Add(new object[] { "@loan_id", loanId });
            paramertList.Add(new object[] { "@advance", loanSetupStep1.advancePercentage});
            paramertList.Add(new object[] { "@auto_remind_email", loanSetupStep1.autoReminderEmail });
            paramertList.Add(new object[] { "@auto_remind_period", loanSetupStep1.autoReminderPeriod });
            paramertList.Add(new object[] { "@default_unit_type", loanSetupStep1.defaultUnitType });
            paramertList.Add(new object[] { "@is_edit_allowable", loanSetupStep1.isEditAllowable });
            paramertList.Add(new object[] { "@is_interest_calculate", loanSetupStep1.isInterestCalculate });
            paramertList.Add(new object[] { "@loan_amount", loanSetupStep1.loanAmount });
            paramertList.Add(new object[] { "@loan_number", loanSetupStep1.loanNumber });
            paramertList.Add(new object[] { "@maturity_date", loanSetupStep1.maturityDate });
            paramertList.Add(new object[] { "@non_reg_branch_id", loanSetupStep1.nonRegisteredBranchId });
            paramertList.Add(new object[] { "@payment_method", loanSetupStep1.paymentMethod });
            //paramertList.Add(new object[] { "@pay_off_period", loanSetupStep1.payOffPeriod });
            //paramertList.Add(new object[] { "@pay_off_type", ((loanSetupStep1.payOffPeriodType == 0) ? 'd' : 'm') });
            paramertList.Add(new object[] { "@loan_code", (new BranchAccess()).getBranchByBranchId(loanSetupStep1.RegisteredBranchId).BranchCode + "-" + loanSetupStep1.loanNumber });
            paramertList.Add(new object[] { "@start_date", loanSetupStep1.startDate });
            paramertList.Add(new object[] { "@created_date", DateTime.Now });
            paramertList.Add(new object[] { "@loan_status", false });
            paramertList.Add(new object[] { "@is_delete", false });

                //DataSet dataSet = dataHandler.GetDataSet("spInsertLoanStepOne", paramertList);
                //if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                //{
                //    loanId =int.Parse(dataSet.Tables[0].Rows[0]["return"].ToString());

                //}

                loanId=dataHandler.ExecuteSQLWithReturnVal("spInsertLoanStepOne", paramertList);

                if (loanId == 0)
                {
                    return loanId;
                }
                else
                {
                    foreach (UnitType UnitType in loanSetupStep1.allUnitTypes)
                    {
                        if (UnitType.isSelected == true)
                        {
                            List<object[]> paramertList1 = new List<object[]>();
                            paramertList1.Add(new object[] { "@loan_id", loanId });
                            paramertList1.Add(new object[] { "@unit_type_id", UnitType.unitTypeId });

                            dataHandler.GetDataSet("spInsertLoanUniType", paramertList1);
                        }


                    }

                    return loanId;
                }


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy : Kanishka SHM
        /// CreatedDate: 01/26/2016
        /// 
        /// Insert company in setup process
        /// 
        /// UpdatedBy : nadeeka
        /// UpdatedDate: 2016/03/06
        /// removed existing connection open method and set parameter's to object list and pass stored procedure name to
        /// call DataHandler class to save company object
        /// 
        /// </summary>
        /// <param name="company"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public int InsertCompany(Company company, string type)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@company_name", company.CompanyName ?? "" });
            paramertList.Add(new object[] { "@company_code", company.CompanyCode ?? "" });
            paramertList.Add(new object[] { "@company_address_1", company.CompanyAddress1 ?? "" });
            paramertList.Add(new object[] { "@company_address_2", company.CompanyAddress2 ?? "" });
            paramertList.Add(new object[] { "@stateId", company.StateId });
            paramertList.Add(new object[] { "@city", company.City ?? "" });
            paramertList.Add(new object[] { "@zip", company.Zip ?? "" });
            paramertList.Add(new object[] { "@email", company.Email ?? "" });
            paramertList.Add(new object[] { "@phone_num_1", company.PhoneNum1 ?? "" });
            paramertList.Add(new object[] { "@phone_num_2", company.PhoneNum2 ?? "" });
            paramertList.Add(new object[] { "@phone_num_3", company.PhoneNum3 ?? "" });
            paramertList.Add(new object[] { "@fax", company.Fax ?? "" });
            paramertList.Add(new object[] { "@website_url", company.WebsiteUrl ?? "" });
            paramertList.Add(new object[] { "@created_by", company.CreatedBy });
            paramertList.Add(new object[] { "@created_date", DateTime.Now });
            paramertList.Add(new object[] { "@company_type", company.TypeId });
            paramertList.Add(new object[] { "@first_super_admin_id", company.FirstSuperAdminId });
            paramertList.Add(new object[] { "@company_status", company.CompanyStatus });
            paramertList.Add(new object[] { "@transaction_type", type });

            try
            {
                return dataHandler.ExecuteSQLWithReturnVal("spInsertCompany", paramertList);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }