C# DataHandler.ExecuteSQLReturn方法代码示例

        /// <summary>
        /// CreatedBy : Kanishka SHM
        /// CreatedDate: 2016/01/16
        /// 
        /// Insert User details
        /// 
        /// argument : user (User)
        /// 
        /// 
        /// UpdatedBy : nadeeka
        /// UpdatedDate: 2016/03/03
        /// removed existing connection open method and set parameter's to object list and pass stored procedure name to
        /// call DataHandler class to save user object
        /// </summary>
        /// <returns>1</returns>
        public int InsertUser(User user)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_Id", user.UserId });
            paramertList.Add(new object[] { "@user_name", user.UserName });
            paramertList.Add(new object[] { "@password", user.Password });
            paramertList.Add(new object[] { "@first_name", user.FirstName });
            paramertList.Add(new object[] { "@last_name", user.LastName });
            paramertList.Add(new object[] { "@email", user.Email });
            paramertList.Add(new object[] { "@phone_no", user.PhoneNumber });
            paramertList.Add(new object[] { "@status", user.Status });
            paramertList.Add(new object[] { "@is_delete", user.IsDelete });
            paramertList.Add(new object[] { "@created_by", user.CreatedBy });
            paramertList.Add(new object[] { "@create_Date", DateTime.Now });
            paramertList.Add(new object[] { "@branch_id", user.BranchId });
            paramertList.Add(new object[] { "@role_id", user.RoleId });
            paramertList.Add(new object[] { "@Company_id", user.Company_Id });
            paramertList.Add(new object[] { "@step_status", user.step_status });

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

 /// <summary>
 /// CreatedBy : Kasun Smarawickrama
 /// CreatedDate: 2016/01/14
 /// 
 /// Retrive User Level by userid
 /// </summary>
 /// <param name="userId">userid</param>
 /// <returns></returns>
 /// UpdatedBy : Asanka Senarathna
 public int GetUserLevelByUserId(int userId)
 {
     DataHandler dataHandler = new DataHandler();
     List<object[]> paramertList = new List<object[]>();
     paramertList.Add(new object[] { "@userId", userId });
     try
     {
         return dataHandler.ExecuteSQLReturn("spGetUserLevelByUserId", paramertList);
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

        /// <summary>
        /// CreatedBy: Piyumi
        /// CreatedDate: 3/30/2016
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int GetLoanCount(int para, int roleId)
        {
            //int loanCount = 0;
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@para", para });
            paramertList.Add(new object[] { "@role", roleId });
            try
            {
                return dataHandler.ExecuteSQLReturn("spGetLoanCount", paramertList);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        /// <summary>
        /// CreatedBy:
        /// CreatedDate:4/24/2016
        ///insert log in every transaction
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int InsertLog(Log log)
        {
            try {
                if (log != null)
                {
                    DataHandler dataHandler = new DataHandler();
                    List<object[]> paramertList = new List<object[]>();

                    paramertList.Add(new object[] { "@date_time", log.DateTime });
                    paramertList.Add(new object[] { "@user_id", log.UserId });
                    paramertList.Add(new object[] { "@company_id", log.CompanyId });
                    paramertList.Add(new object[] { "@branch_id", log.BranchId });
                    paramertList.Add(new object[] { "@loan_id", log.LoanId });
                    paramertList.Add(new object[] { "@description", log.Description });
                    paramertList.Add(new object[] { "@page", log.Page });


                    try
                    {
                        return dataHandler.ExecuteSQLReturn("spInsertLog", paramertList);
                    }
                    catch
                    {
                        return 0;
                    }
                    // return 1;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/2/5
        /// insert details of interest which related to a loan
        /// </summary>
        /// <returns>countval</returns>
        public int insertInterestDetails(Interest interest)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();            

            paramertList.Add(new object[] { "@interest_rate", interest.InterestRate });
            paramertList.Add(new object[] { "@paid_date", interest.PaidDate });
            paramertList.Add(new object[] { "@payment_period", interest.PaymentPeriod });
            paramertList.Add(new object[] { "@auto_remind_email", interest.AutoRemindEmail });
            paramertList.Add(new object[] { "@auto_remind_period", interest.RemindPeriod });
            paramertList.Add(new object[] { "@loan_id", interest.LoanId });
            paramertList.Add(new object[] { "@accrual_method_id", interest.AccrualMethodId });           

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

        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/2/9
        /// Insert details of title related to a loanId
        /// </summary>
        /// <param name="loanId"></param>
        /// <returns>countVal</returns>
        public int insertTitleDetails(Title title)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@is_title_tracked", title.IsTitleTrack });
            if (title.IsTitleTrack)
            {
                //paramertList.Add(new object[] { "@title_accept_method", title.TitleAcceptMethod });
                paramertList.Add(new object[] { "@title_received_time_period", title.ReceivedTimeLimit });
                paramertList.Add(new object[] { "@auto_remind_period", title.RemindPeriod });
                paramertList.Add(new object[] { "@auto_remind_email", title.RemindEmail });
                
               
            }
            else
            {
               // paramertList.Add(new object[] { "@title_accept_method", null });
                paramertList.Add(new object[] { "@title_received_time_period", null });
                paramertList.Add(new object[] { "@auto_remind_period", null });
                paramertList.Add(new object[] { "@auto_remind_email", null});
                //paramertList.Add(new object[] { "@need_scan_copy", 0 });
            }

            paramertList.Add(new object[] { "@is_receipt_required", title.IsReceipRequired });
            if (title.IsReceipRequired)
            {

                paramertList.Add(new object[] { "receipt_required_method", title.ReceiptRequiredMethod });
            }
            else
            {
                paramertList.Add(new object[] { "receipt_required_method",null});
            }
            if (title.NeedScanCopy)
            {
                paramertList.Add(new object[] { "@need_scan_copy", 1 });
            }
            else
            {
                paramertList.Add(new object[] { "@need_scan_copy", 0 });
            }
            paramertList.Add(new object[] { "@loan_id", title.LoanId });

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

        public int PayOffUnits(string unitIdList, DateTime payOffDate, int titleStatus)
        {
            try { 
            DataHandler dataHandler = new DataHandler();

            List<object[]> paramertList1 = new List<object[]>();
            paramertList1.Add(new object[] { "@Input", unitIdList });
            paramertList1.Add(new object[] { "@pay_date", payOffDate });
            paramertList1.Add(new object[] { "@title_status", titleStatus });
           
                return dataHandler.ExecuteSQLReturn("spCurtailmentsBackup", paramertList1);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Frontend page:    dashboard page
        /// title:              checking is atleast one permission for report access
        /// designed:           irfan mam
        /// User story:         DFP 476
        /// developed:          irfan mam
        /// date creaed:        6/23/2016
        /// 
        /// </summary>
        /// 
        /// <returns>
        /// if there is no loan has user rights -> false
        ///  if there is atleast one user right for any loan -> true
        /// </returns>
        public bool IsAtleastOnePermissionForReport( int userId)
        {

            bool ret = false; // set ret value false as default 
            DataHandler dataHandler = new DataHandler(); 
            List<object[]> paramertList = new List<object[]>(); // argument list
           
            // add user id to argument list
            paramertList.Add(new object[] { "@user_id", userId });
            try
            {
                // if stored proceture return 1
                if( dataHandler.ExecuteSQLReturn("isAtleastOnePermissionForReport", paramertList)== 1)
                {
                    // set return value to true
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // return the ret value
            return ret;


        }

        public int InsertUserInDashboard(User user)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            if (user != null)
            {
                paramertList.Add(new object[] { "@user_name", user.UserName });
                paramertList.Add(new object[] { "@password", user.Password });
                paramertList.Add(new object[] { "@first_name", user.FirstName });
                paramertList.Add(new object[] { "@last_name", user.LastName });
                paramertList.Add(new object[] { "@email", user.Email });
                paramertList.Add(new object[] { "@phone_no", user.PhoneNumber2 });
                paramertList.Add(new object[] { "@created_date", DateTime.Now });
                paramertList.Add(new object[] { "@created_by", user.CreatedBy});
                paramertList.Add(new object[] { "@company_id", user.Company_Id });
                paramertList.Add(new object[] { "@branch_id", user.BranchId });
                paramertList.Add(new object[] { "@role_id", user.RoleId});
                

                if (user.RoleId == 3)
                {
                    paramertList.Add(new object[] { "@rights", user.UserRights });
                    paramertList.Add(new object[] { "@report_rights", user.ReportRights });
                    paramertList.Add(new object[] { "@loan_id", user.LoanId });
                }
                else
                {
                    paramertList.Add(new object[] { "@rights",""});
                    paramertList.Add(new object[] { "@report_rights", "" });
                    paramertList.Add(new object[] { "@loan_id", 0 });
                }
                paramertList.Add(new object[] { "@step_status", user.step_status });
                paramertList.Add(new object[] { "@status", user.Status });
            }
           

            try
            {
                return dataHandler.ExecuteSQLReturn("spInsertDashboardUserDetails", paramertList);

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

        public int insertNonRegBranchDetails(CompanyBranchModel nonRegBranch, int userId)
        {
            nonRegBranch.MainBranch.BranchCreatedDate = DateTime.Now;
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_id", userId });
            //paramertList.Add(new object[] { "@company_code", companyCode });
            paramertList.Add(new object[] { "@branch_code", nonRegBranch.MainBranch.BranchCode.Trim() });
            if (!string.IsNullOrEmpty(nonRegBranch.MainBranch.BranchName))
            {
                paramertList.Add(new object[] { "@branch_name", nonRegBranch.MainBranch.BranchName.Trim() });
            }

            paramertList.Add(new object[] { "@branch_address_1", nonRegBranch.MainBranch.BranchAddress1.Trim() });
            paramertList.Add(new object[] { "@branch_address_2", nonRegBranch.MainBranch.BranchAddress2 ?? "" });
            paramertList.Add(new object[] { "@state_id", nonRegBranch.MainBranch.StateId });
            paramertList.Add(new object[] { "@city", nonRegBranch.MainBranch.BranchCity ?? "" });
            if ((nonRegBranch.MainBranch.Extention != null) && (nonRegBranch.MainBranch.Extention.ToString() != ""))
            {
                nonRegBranch.MainBranch.BranchZip = nonRegBranch.MainBranch.ZipPre + "-" + nonRegBranch.MainBranch.Extention;
            }
            else
            {
                nonRegBranch.MainBranch.BranchZip = nonRegBranch.MainBranch.ZipPre;
            }
            paramertList.Add(new object[] { "@zip", nonRegBranch.MainBranch.BranchZip.Trim() });
            paramertList.Add(new object[] { "@email", nonRegBranch.MainBranch.BranchEmail ?? "" });
            paramertList.Add(new object[] { "@phone_num_1", nonRegBranch.MainBranch.BranchPhoneNum1.Trim() });
            paramertList.Add(new object[] { "@phone_num_2", nonRegBranch.MainBranch.BranchPhoneNum2 ?? "" });
            paramertList.Add(new object[] { "@phone_num_3", nonRegBranch.MainBranch.BranchPhoneNum3 ?? "" });
            paramertList.Add(new object[] { "@fax", nonRegBranch.MainBranch.BranchFax ?? "" });
            paramertList.Add(new object[] { "@created_date", DateTime.Now });
            paramertList.Add(new object[] { "@company_id", nonRegBranch.MainBranch.BranchCompany });
            paramertList.Add(new object[] { "@branch_id", nonRegBranch.MainBranch.BranchCreatedBy });

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

        /// <summary>
        /// CreatedBy:Asanka Senarathna
        /// CreatedDate: 6/27/2016
        /// Update Loan Status for Active loan to inactive
        /// </summary>
        /// <param name="slctdLoanId"></param>
        /// <param name="slctdLoanCode"></param>
        /// <returns></returns>
        public int UpdateLoanStatus_ActiveInactive(int slctdLoanId, string slctdLoanCode)
        {
            if ((slctdLoanId > 0) && (!string.IsNullOrEmpty(slctdLoanCode)) )
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();


                paramertList.Add(new object[] { "@loan_id", slctdLoanId });
                paramertList.Add(new object[] { "@loan_code", slctdLoanCode });
                paramertList.Add(new object[] { "@loan_status", 0 });

                try
                {
                    return dataHandler.ExecuteSQLReturn("spUpdateLoanStatus_ActivetoInactive", paramertList);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate: 4/21/2016
        /// Update Loan Status
        /// </summary>
        /// <param name="slctdLoanId"></param>
        /// <param name="slctdLoanCode"></param>
        /// <returns></returns>
        public int UpdateLoanStatus(int slctdLoanId, string slctdLoanCode,string slctdActiveDate)
        {
            if ((slctdLoanId > 0) && (!string.IsNullOrEmpty(slctdLoanCode)) && (!string.IsNullOrEmpty(slctdActiveDate)))
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();

               
                paramertList.Add(new object[] { "@loan_id", slctdLoanId });
                paramertList.Add(new object[] { "@loan_code", slctdLoanCode });
                paramertList.Add(new object[] { "@loan_status", 1 });
                paramertList.Add(new object[] { "@active_date", DateTime.Parse(slctdActiveDate) });
                paramertList.Add(new object[] { "@modified_date", DateTime.Now });
                try
                {
                    return dataHandler.ExecuteSQLReturn("spUpdateLoanStatus", paramertList);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else 
            {
                return 0;
            }
        }

        //internal void getSelectedUnitTypes(int loanId, LoanSetupStep1 loanSetupStep1)
        //{
        //    DataHandler dataHandler = new DataHandler();
        //    List<object[]> paramertList = new List<object[]>();
        //    paramertList.Add(new object[] { "@loan_id", loanId });

        //    try
        //    {

        //    }
        //    catch
        //    {

        //    }

        //    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AutoDealersConnection"].ConnectionString))
        //    {
        //        try
        //        {
        //            using (SqlCommand cmd = new SqlCommand("spGetLoanUnitTypesByLoanId", con))
        //            {
        //                cmd.CommandType = CommandType.StoredProcedure;
        //                cmd.Parameters.Add("@loan_id", SqlDbType.Int).Value = loanId;
        //                con.Open();
        //                SqlDataReader reader = cmd.ExecuteReader();

                        
                        

        //                while (reader.Read())
        //                {
        //                    UnitType unitType = new UnitType();

        //                    unitType.unitTypeId = int.Parse(reader["unit_type_id"].ToString());
        //                    //loanSetupStep1.selectedUnitTypes.Add(unitType);







        //                }

        //                reader.Close();




                       

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

        //        }
        //        finally
        //        {
        //            con.Close();
        //        }

        //    }

        //}

        internal int UpdateLoanCurtailment(CurtailmentModel curtailmentModel, int loanId)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();

            bool loanStatus = curtailmentModel.LoanStatus == "Yes" ? true : false;
            char calMode = curtailmentModel.CalculationBase == "Full payment" ? 'f' : 'a';
            char timeBase = curtailmentModel.TimeBase == "Months" ? 'm' : 'd';

            paramertList.Add(new object[] { "@loan_id", loanId });
            paramertList.Add(new object[] { "@loan_status", loanStatus });
            paramertList.Add(new object[] { "@curtailment_due_date", curtailmentModel.DueDate });
            paramertList.Add(new object[] { "@curtailment_auto_remind_email", curtailmentModel.AutoRemindEmail});
            paramertList.Add(new object[] { "@curtailment_remind_period", curtailmentModel.EmailRemindPeriod });
            paramertList.Add(new object[] { "@curtailment_calculation_type", calMode });
            paramertList.Add(new object[] { "@pay_off_type", timeBase });

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

        /// <summary>
        /// CreatedBy : Piyumi
        /// CreatedDate: 05/04/2016
        /// 
        /// Insert company in setup process 
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public int InsertNonRegisteredCompanyAtDashboard(PartnerCompany company)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@company_name", company.CompanyName.Trim() });
            if (!string.IsNullOrEmpty(company.CompanyCode))
            {
                paramertList.Add(new object[] { "@company_code", company.CompanyCode.Trim() });
            }
            else
            {
                paramertList.Add(new object[] { "@company_code", company.CompanyCode});
            }
            paramertList.Add(new object[] { "@company_address_1", company.CompanyAddress1.Trim() });
            if (!string.IsNullOrEmpty(company.CompanyAddress2))
            {
                company.CompanyAddress2.Trim();
                paramertList.Add(new object[] { "@company_address_2", company.CompanyAddress2.Trim() });
            }

            paramertList.Add(new object[] { "@stateId", company.StateId });
            paramertList.Add(new object[] { "@city", company.City.Trim() });
            paramertList.Add(new object[] { "@zip", company.Zip.Trim() });
            if (!string.IsNullOrEmpty(company.Email))
            {
                company.Email.Trim();
            }
            if (!string.IsNullOrEmpty(company.PhoneNum2))
            {
                company.PhoneNum2.Trim();
            }
            if (!string.IsNullOrEmpty(company.PhoneNum3))
            {
                company.PhoneNum3.Trim();
            }
            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[] { "@reg_company_id", company.RegCompanyId });


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

        }

        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:5/3/2016
        /// Update Company details
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public int UpdateCompany(Company company,int userId)
        {
            if (company != null)
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();


                paramertList.Add(new object[] { "@company_id", company.CompanyId });
                paramertList.Add(new object[] { "@company_name", company.CompanyName });
                paramertList.Add(new object[] { "@address1", company.CompanyAddress1 });
                if (!string.IsNullOrEmpty(company.CompanyAddress2)){
                    paramertList.Add(new object[] { "@address2", company.CompanyAddress2 });
                }
                else
                {
                    paramertList.Add(new object[] { "@address2", null });
                }
                paramertList.Add(new object[] { "@city",company.City });
                paramertList.Add(new object[] { "@state", company.StateId });
                paramertList.Add(new object[] { "@zip", company.Zip });
                paramertList.Add(new object[] { "@phone_no", company.PhoneNum1 });
                if (!string.IsNullOrEmpty(company.PhoneNum2))
                {
                    paramertList.Add(new object[] { "@phone_no2", company.PhoneNum2 });
                }
                else
                {
                    paramertList.Add(new object[] { "@phone_no2", null });
                }
                if (!string.IsNullOrEmpty(company.PhoneNum3))
                {
                    paramertList.Add(new object[] { "@phone_no3", company.PhoneNum3 });
                }
                else
                {
                    paramertList.Add(new object[] { "@phone_no3", null });
                }

                if (!string.IsNullOrEmpty(company.Fax))
                {
                    paramertList.Add(new object[] { "@fax", company.Fax });
                }
                else
                {
                    paramertList.Add(new object[] { "@fax", null });
                }
                if (!string.IsNullOrEmpty(company.Email))
                {
                    paramertList.Add(new object[] { "@email", company.Email });
                }
                else
                {
                    paramertList.Add(new object[] { "@email", null });
                }
                if (!string.IsNullOrEmpty(company.WebsiteUrl))
                {
                    paramertList.Add(new object[] { "@web", company.WebsiteUrl });
                }
                else
                {
                    paramertList.Add(new object[] { "@web", null });
                }
                paramertList.Add(new object[] { "@modified_date", DateTime.Now });
                paramertList.Add(new object[] { "@modified_by", userId });

                try
                {
                    return dataHandler.ExecuteSQLReturn("spUpdateCompany", paramertList);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                return 0;
            }
        }