C# DataHandler.ExecuteSQL方法代码示例

        /// <summary>
        /// Created by :Asanka Senarathna
        /// Createddate : 2016/03/30
        /// Update User Request (Add Answer to User Question)
        /// </summary>
        /// <param name="userRequest"></param>
        /// <returns></returns>
        public int UpdateUserRequestAnswer(UserRequest userRequest)
        {
            try
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@request_id", userRequest.request_id });
                paramertList.Add(new object[] { "@answer", userRequest.answer });
                paramertList.Add(new object[] { "@answer_user_id", userRequest.answer_user_id });

                return dataHandler.ExecuteSQL("spUpdateUserRequestAnswer", paramertList) ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Created by : Asanka Senarathna
        /// Created date :2016/03/30
        /// Insert User request message
        /// </summary>
        /// <param name="userRequest"></param>
        /// <returns></returns>
        public int InsertUserRequest(UserRequest userRequest)
        {
            try
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@company_id", userRequest.company_id });
                paramertList.Add(new object[] { "@branch_id", userRequest.branch_id });
                paramertList.Add(new object[] { "@user_id", userRequest.user_id });
                paramertList.Add(new object[] { "@role_id", userRequest.role_id });
                paramertList.Add(new object[] { "@loan_code", userRequest.loan_code });
                paramertList.Add(new object[] { "@page_name", userRequest.page_name });
                paramertList.Add(new object[] { "@topic", userRequest.topic });
                paramertList.Add(new object[] { "@message", userRequest.message });
                paramertList.Add(new object[] { "@priority_level", userRequest.priority_level });

                return dataHandler.ExecuteSQL("spInsertUserRequest", paramertList) ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy : MAM. IRFAN
        /// CreatedDate: 2016/01/13
        /// 
        /// update User Details  userName, firstName, lastName, email, phone, password
        /// 
        /// argument : user_id (int)
        /// 
        /// 
        /// 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 update given user details
        /// </summary>
        /// <returns>User object</returns>
        public bool updateProfileDetails(int userId, string userName, string firstName, string lastName, string email,
            string phone, DateTime modifiedDate)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_Id", userId });
            paramertList.Add(new object[] { "@user_name", userName });
            paramertList.Add(new object[] { "@first_name", firstName });
            paramertList.Add(new object[] { "@last_name", lastName });
            paramertList.Add(new object[] { "@email", email });
            paramertList.Add(new object[] { "@phone_no", phone });
            paramertList.Add(new object[] { "@modified_date", DateTime.Now });

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

        /// <summary>
        /// 
        /// CreatedBy:  Kanishka 
        /// CreatedDate:02/26/2016
        /// 
        /// Delete just added unit records
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="loanId"></param>
        public void DeleteJustAddedUnits(int userId)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> parameterList = new List<object[]>();
            parameterList.Add(new object[] { "@user_id", userId });

            try
            {
                dataHandler.ExecuteSQL("spDeleteJustAddedUnit", parameterList);

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

        /// <summary>
        /// CreatedBy : MAM. IRFAN
        /// CreatedDate: 2016/01/13
        /// 
        /// update User Details userName, firstName, lastName, email, phone, isActive, branchId, password
        /// 
        /// argument : user_id (int)
        /// 
        /// 
        /// 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 update given user details
        /// </summary>
        /// <returns>boolean value</returns>
        public bool updateUserDetails(int userId, string userName, string firstName, string lastName, string email,
            string phone, bool isActive, int branchId, DateTime modifiedDate)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_Id", userId });
            paramertList.Add(new object[] { "@user_name", userName });
            paramertList.Add(new object[] { "@first_name", firstName });
            paramertList.Add(new object[] { "@last_name", lastName });
            paramertList.Add(new object[] { "@email", email });
            paramertList.Add(new object[] { "@phone_no", phone });
            paramertList.Add(new object[] { "@status", isActive });
            paramertList.Add(new object[] { "@modified_date", DateTime.Now });
            paramertList.Add(new object[] { "@branch_id", branchId });

            try
            {
                return dataHandler.ExecuteSQL("spUpdateUserDetails", paramertList) ? true : false;

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

        public int insertFreeDetailsForPayOffPage(UnitPayOffModel unit, DateTime payday,int userID)
        {
            try
            {
                string fee_type = "";
                string fee_due_method = "";
                decimal fee_amount = 0;
                DateTime fee_billdate = payday;
                string v_vin = "", v_year = "", v_model = "", v_make = "", v_advance_date = "";

                DataHandler dataHandler = new DataHandler();

                List<object[]> paramertList1 = new List<object[]>();
                paramertList1.Add(new object[] { "@loan_id", unit.LoanId });
                DataSet dataSet = dataHandler.GetDataSet("spGetAdvanceFeeData", paramertList1);

                if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                {
                    foreach (DataRow row in dataSet.Tables[0].Rows)
                    {
                        fee_due_method = row["payment_due_method"].ToString();
                        fee_type = row["advance_fee_calculate_type"].ToString();
                        fee_amount = decimal.Parse(row["advance_fee_amount"].ToString());
                    }
                }
                v_vin = unit.IdentificationNumber;
                v_make = unit.Make;
                v_model = unit.Model;
                v_year = unit.Year.ToString();
                v_advance_date = unit.DateAdvanced.ToString("MM/dd/yyyy");// ("MM/dd/yyyy");

                string discription = fee_due_method + "," + v_vin + "," + v_year + "," + v_make + "," + v_model + "," + v_advance_date;

                if (fee_due_method == "Vehicle Payoff")
                {
                    List<object[]> paramertList = new List<object[]>();
                    paramertList.Add(new object[] { "@loan_id", unit.LoanId });
                    paramertList.Add(new object[] { "@unit_id", unit.UnitId });
                    paramertList.Add(new object[] { "@type", "advanceFee" });
                    paramertList.Add(new object[] { "@description", discription });
                    paramertList.Add(new object[] { "@amount", fee_amount });
                    paramertList.Add(new object[] { "@due_date", fee_billdate });
                    paramertList.Add(new object[] { "@bill_due_date", fee_billdate });
                    paramertList.Add(new object[] { "@user_id", userID });


                    dataHandler.ExecuteSQL("spInsertAdvanceFeeForPayoff", paramertList);
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// CreatedBy:  Kanishka 
        /// CreatedDate:02/26/2016
        /// 
        /// Insert title document detail
        /// 
        /// </summary>
        /// <returns></returns>
        public bool InsertTitleDocumentUploadInfo(string xmlDoc, string unitId)
        {

            DataHandler dataHandler = new DataHandler();
            List<object[]> parameterList = new List<object[]>();
            parameterList.Add(new object[] { "@Input", xmlDoc });
            parameterList.Add(new object[] { "@unit_id", unitId });

            try
            {
                return dataHandler.ExecuteSQL("spInsertTitleDocumentDetails", parameterList) ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 /// <summary>
 /// Frontend page: Nortification button in any page
 ///Title: When User view nortification message in any page in progeam
 ///Designed: Asanka Senarathna
 ///User story: DFP-
 ///Developed: Asanka Senarathna
 ///Date created: 3/30/2016
 /// </summary>
 /// <param name="userid"></param>
 /// <returns></returns>
 public int UpdateUserViewAnswer(int userid)
 {
     try
     {
         DataHandler dataHandler = new DataHandler();
         List<object[]> paramertList = new List<object[]>();
         paramertList.Add(new object[] { "@user_id", userid });
         //update message states when user view nortification in any page
         return dataHandler.ExecuteSQL("spUpdateUserViewAnswer", paramertList) ? 1 : 0;
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

        public int insertFreeDetailsForAdvance(Unit unit,int loanID)
        {
            try
            {
                string fee_type = "";
                string fee_due_method = "";
                decimal fee_amount = 0;
                int fee_due_date = 0;
                DateTime fee_billdate = unit.AdvanceDate;
                string v_vin = "", v_year = "", v_model = "", v_make = "";

                DataHandler dataHandler = new DataHandler();

                List<object[]> paramertList1 = new List<object[]>();
                paramertList1.Add(new object[] { "@loan_id", loanID });
                DataSet dataSet = dataHandler.GetDataSet("spGetAdvanceFeeData", paramertList1);

                if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                {
                    foreach (DataRow row in dataSet.Tables[0].Rows)
                    {
                        fee_due_method = row["payment_due_method"].ToString();
                        fee_type = row["advance_fee_calculate_type"].ToString();
                        fee_amount = decimal.Parse(row["advance_fee_amount"].ToString());
                        if (fee_type != "")
                        {
                            if (row["payment_due_date"].ToString() == "EoM")
                            {
                                fee_due_date = DateTime.DaysInMonth(fee_billdate.Year, fee_billdate.Month + 1);
                            }
                            else
                            {
                                fee_due_date = int.Parse(row["payment_due_date"].ToString());
                            }
                        }
                    }
                }
                v_vin = unit.IdentificationNumber;
                v_make = unit.Make;
                v_model = unit.Model;
                v_year = unit.Year.ToString();

                string discription = fee_due_method + "," + v_vin + "," + v_year + "," + v_make + "," + v_model;

                if (fee_due_method == "Time of Advance")
                {
                    List<object[]> paramertList = new List<object[]>();
                    paramertList.Add(new object[] { "@loan_id", loanID });
                    paramertList.Add(new object[] { "@unit_id", unit.UnitId });
                    paramertList.Add(new object[] { "@type", "advanceFee" });
                    paramertList.Add(new object[] { "@description", discription });
                    paramertList.Add(new object[] { "@amount", fee_amount });
                    paramertList.Add(new object[] { "@due_date", unit.AdvanceDate });
                    paramertList.Add(new object[] { "@bill_due_date", unit.AdvanceDate });

                    dataHandler.ExecuteSQL("spInsertAdvanceFee", paramertList);
                }
                else if (fee_due_method == "Once a Month")
                {
                    if (fee_type == "Month")
                    {
                        fee_billdate = fee_billdate.AddMonths(1);
                        fee_billdate = new DateTime(fee_billdate.Year, fee_billdate.Month, fee_due_date);
                    }
                    else if (fee_type == "PayPeriod")
                    {
                        if (fee_billdate.Date.Day > fee_due_date)
                        {
                            fee_billdate = fee_billdate.AddMonths(1);
                            fee_billdate = new DateTime(fee_billdate.Year, fee_billdate.Month, fee_due_date);
                        }
                        else
                        {
                            fee_billdate = new DateTime(fee_billdate.Year, fee_billdate.Month, fee_due_date);
                        }
                    }

                    List<object[]> paramertList = new List<object[]>();
                    paramertList.Add(new object[] { "@loan_id", loanID });
                    paramertList.Add(new object[] { "@unit_id", unit.UnitId });
                    paramertList.Add(new object[] { "@type", "advanceFee" });
                    paramertList.Add(new object[] { "@description", discription });
                    paramertList.Add(new object[] { "@amount", fee_amount });
                    paramertList.Add(new object[] { "@due_date", unit.AdvanceDate });
                    paramertList.Add(new object[] { "@bill_due_date", fee_billdate });

                    dataHandler.ExecuteSQL("spInsertAdvanceFee", paramertList);
                }



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

        }

        /// <summary>
        /// CreatedBy : Kanishka SHM
        /// CreatedDate: 2016/01/21
        /// 
        /// Insert new created user to user_activation table
        /// 
        /// 
        /// 
        /// 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 insert activation code for user
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="activationCode"></param>
        /// <returns></returns>
        public int InsertUserActivation(int userId, string activationCode)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_Id", userId });
            paramertList.Add(new object[] { "@activation_code", activationCode });

            try
            {
                return dataHandler.ExecuteSQL("spInsertUserActivation", paramertList) ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/3/4
        /// Update company setup step table
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="branchId"></param>
        /// <param name="stepNumber"></param>
        /// <returns></returns>
        public bool UpdateCompanySetupStep(int companyId, int branchId, int stepNumber)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@company_id", companyId });
            paramertList.Add(new object[] { "@branch_id", branchId });
            paramertList.Add(new object[] { "@step_number", stepNumber });


            try
            {
                return dataHandler.ExecuteSQL("spUpdateCompanySetupStep", paramertList) ? true : false;

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

        /// <summary>
        /// CreatedBy : Nadeeka
        /// CreatedDate: 2016/03/17
        /// 
        /// Insert curtailment breakdown details
        /// 
        /// 
        /// </summary>
        /// <param name="xmlDoc">curtailment breakdown list</param>
        /// <param name="unitId">unit id</param>
        /// <param name="loanId">loan id</param>
        /// <returns></returns>
        public bool InsertCurtailmentScheduleInfo(string xmlDoc, string unitId, int loanId)
        {
            try
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList2 = new List<object[]>();
                paramertList2.Add(new object[] { "@loan_id", loanId });
                paramertList2.Add(new object[] { "@unit_id", unitId });
                paramertList2.Add(new object[] { "@Input", xmlDoc });


                return dataHandler.ExecuteSQL("spInsertCurtailmentSchedule", paramertList2);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate: 03/17/2016
        /// get titles list by identification number
        /// </summary>
        /// <param name="unit"></param>
        /// <param name="loanCode"></param>
        /// <returns>true/false</returns>
        public bool UpdateTitle(Unit unit,string loanCode,int userId)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@unit_id", unit.UnitId });
            //paramertList.Add(new object[] { "@identification_number", unit.IdentificationNumber });
            //paramertList.Add(new object[] { "@year", unit.Year });
            //paramertList.Add(new object[] { "@make", unit.Make });
            //paramertList.Add(new object[] { "@model", unit.Model });
            paramertList.Add(new object[] { "@title_status", unit.TitleStatus });
            paramertList.Add(new object[] { "@loan_code", loanCode });
            paramertList.Add(new object[] { "@user_id", userId });
            paramertList.Add(new object[] { "@modified_date", DateTime.Now });

            try
            {
                return dataHandler.ExecuteSQL("spUpdateTitleStatus", paramertList) ? true : false;

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

        /// <summary>
        /// CreatedBy : Nadeeka
        /// CreatedDate: 2016/02/09
        /// 
        /// Insert curtailment details
        /// 
        /// argument : curtailment list
        /// 
        /// </summary>
        /// <returns>1</returns>
        public int InsertCurtailment(List<Curtailment> lstCurtailment, int loanId)
        {
            //int flag = 0;
            //int delFlag = 0;
            try
            {
                int executeCount = 0;
                DataHandler dataHandler = new DataHandler();

                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@loan_id", loanId });
                if (dataHandler.ExecuteSQL("spDeleteCurtailment", paramertList))
                {
                    foreach (Curtailment curtailment in lstCurtailment)
                    {
                        List<object[]> paramertList2 = new List<object[]>();
                        paramertList2.Add(new object[] { "@loan_id", loanId });
                        paramertList2.Add(new object[] { "@curtailment_id", curtailment.CurtailmentId });
                        paramertList2.Add(new object[] { "@time_period", curtailment.TimePeriod });
                        paramertList2.Add(new object[] { "@percentage", curtailment.Percentage });

                        executeCount = dataHandler.ExecuteSQL("spInsertCurtailment", paramertList2) ? executeCount + 1 : executeCount;

                    }
                }
                return executeCount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            //if (delFlag == 2) flag = delFlag;
            //return flag; 
        }

        /// <summary>
        /// CreatedBy : Irfan
        /// CreatedDate: 01/27/2016
        /// 
        /// Insert company in setup process 
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public bool InsertNonRegisteredCompany(Company company)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@company_name", company.CompanyName.Trim() });
            paramertList.Add(new object[] { "@company_code", company.CompanyCode.Trim() });
            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.CreatedByCompany });


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

        }