C# DBOperationsUtil.getRecord方法代码示例

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.IsAuthenticated)
        {
            if (!IsPostBack)
            {
                populateVacancy();
            }

            // Get a reference to the currently logged on user 
            MembershipUser currentUser = Membership.GetUser();

            // Determine the currently logged on user's UserId value 
            Guid currentUserId = (Guid)currentUser.ProviderUserKey;

            //prepare parameters
            IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
            argumentsMap.Add("@UserId", currentUserId);

            //Pass Stored Procedure Name and parameter list. 
            DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeBranchDetail, argumentsMap);

            //call getRecord method and get DataTable
            DataTable dataTable = storeToDb.getRecord();
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    loggedInEmpID = row["Emp_ID"].ToString();
                }
            }

        }
    }

    /**
     * Return list of vacancy which have this status.
     */
    public static TransactionResponse getAllActiveVacancy(string status)
    {
        TransactionResponse response = new TransactionResponse();
        IDictionary<string, object> statusParams = new Dictionary<string, object>();
        statusParams.Add("@status", status);
        statusParams.Add("@districtId", PageAccessManager.getDistrictID());

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil dpOperation = new DBOperationsUtil(DbAccessConstants.spAllActiveVacancy, statusParams);
        try
        {
            DataTable dataTable = dpOperation.getRecord();

            response.Data = dataTable;
            response.setSuccess(true);
        }
        catch (SqlException ex)
        {
            //Other SqlException is catched
            response.setSuccess(false);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setMessage(DBOperationErrorConstants.M_UNKNOWN_EVIL_ERROR);
            response.setErrorCode(DBOperationErrorConstants.E_UNKNOWN_ERROR_AT_DB_OOPERATION);
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            LoggerManager.upDateWithGenericErrorMessage(response);
        }
        return response;
    }

    public static DataTable getAllBranchEmpStatus()
    {
        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetAllBranchEmployeeStatus, null);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static DataTable getAllBranchEmpStatus()
    {
        //Pass Stored Procedure Name and parameter list. 
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@district", PageAccessManager.getDistrictID());

        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetAllBranchEmployeeStatus, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static DataTable getAssignedEmployeeFormOtherDistrict()
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetListOfAssignedEmployeefromOtherDistrict, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static DataTable getSpecBranchEmpStatus(int branchID)
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@branch", branchID);

        //Pass Stored Procedure Name and parameter list. 
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetBranchEmployeeStatus, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static TransactionResponse getAllNotificationsForCurrentEmployee(MembershipUser currentUser)
    {
        //get detail of the logged on user.
        Employee employee = EmployeeManager.getLoggedOnUser((Guid)currentUser.ProviderUserKey);

        if (employee == null)
        {
            return EmployeeManager.handleLoggedInUserCanNotBeIdentfied();
        }

        TransactionResponse response = new TransactionResponse();
        try
        {
            IDictionary<string, object> employeeIdMap = new Dictionary<string, object>();
            employeeIdMap.Add("@EMP_ID", employee.EmpID);
            employeeIdMap.Add("@destrictID", PageAccessManager.getDistrictID());

            //Pass Stored Procedure Name and parameter list.
            DBOperationsUtil dbOperation = new DBOperationsUtil(DbAccessConstants.spGetAllNotificationForTheCurrentEmployee, employeeIdMap);
            DataTable dataTable = dbOperation.getRecord();
            //put the data on Transaction response
            response.Data = dataTable;
            response.setSuccess(true);
            response.setMessageType(TransactionResponse.SeverityLevel.INFO);
            response.setMessage(DBOperationErrorConstants.M_NOTIFICATION_INFO);
            //get Notifications inside the TransactionResponse.
            return response;
        }
        catch (SqlException ex)
        {
            response.setErrorCode(DBOperationErrorConstants.E_ERROR_WHILE_READING_NOTIFICATION);
            response.setMessage(DBOperationErrorConstants.M_ERROR_WHILE_READING_NOTIF);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setSuccess(false);
            return response;
        }

           //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            response.setErrorCode(DBOperationErrorConstants.E_UNKNOWN_EVIL_ERROR);
            response.setMessage(DBOperationErrorConstants.M_UNKNOWN_EVIL_ERROR);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setSuccess(false);
            return response;
        }
    }

    public static TransactionResponse getDistrictSettingValue(string paramTag)
    {
        TransactionResponse response = new TransactionResponse();

        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@paramter_tag", paramTag);
        argumentsMap.Add("@districtID", PageAccessManager.getDistrictID());

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil dbOperation = new DBOperationsUtil(DbAccessConstants.spGetDistrictSetting, argumentsMap);
        try
        {
            DataTable dataTable = dbOperation.getRecord();
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    //put the data on Transaction reponse
                    response.Data = row["parameter_value"].ToString();
                    response.setSuccess(true);
                    return response;
                }
            }
            response.Data = PARAMETER_NOT_DEFINED + DBOperationErrorConstants.CONTACT_ADMIN;
            response.setSuccess(false);
            return response;
        }
        catch (SqlException ex)
        {
            response.Data = PARAMETER_NOT_DEFINED + DBOperationErrorConstants.CONTACT_ADMIN;
            response.setSuccess(false);
            return response;
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            response.Data = PARAMETER_NOT_DEFINED + DBOperationErrorConstants.CONTACT_ADMIN;
            response.setSuccess(false);
            return response;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.IsAuthenticated)
        {
            // Get a reference to the currently logged on user 
            MembershipUser currentUser = Membership.GetUser();

            // Determine the currently logged on user's UserId value 
            Guid currentUserId = (Guid)currentUser.ProviderUserKey;

            //prepare parameters
            IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
            argumentsMap.Add("@UserId", currentUserId);

            //Pass Stored Procedure Name and parameter list. 
            DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeBranchDetail, argumentsMap);

            //call getRecord method and get DataTable
            DataTable dataTable = storeToDb.getRecord();
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    fName = row["First_Name"].ToString();
                    mName = row["Middle_Name"].ToString();
                    jTittle = row["Job_Tittle"].ToString();
                    branchName = row["branchName"].ToString();
                    branchID = Convert.ToInt16(row["branch"].ToString());
                }
            }
            else
            {
                //something went wrong, show correct error to the user
            }
            lblBranch.Text = branchName;
        }
        if (!IsPostBack)
        {
            DataTable getds = BranchEmployeeStatusManager.getSpecBranchEmpStatus(branchID);
            BindDataSetToGV(getds);
        }
    }

    /**
     * gets list of Current branch
     */
    public static DataTable getCurrentBranch()
    {
        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetBranchsList, null);

        DataTable dataTable = null;
        try
        {
            return dataTable = storeToDb.getRecord();
        }
        catch (SqlException ex)
        {
            //return emptylist
            return dataTable;
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            return dataTable;
        }
    }

    /**
     * gets list of employee for a given Branch
     */
    public List<Employee> getListOfEmployeeByFirstNameOrEmpIdAtBranch()
    {
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@txtEmpID", employee.UserName);
        argumentsMap.Add("@branch", employee.Branch);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeByFirstNameAutoCompleteSpecBranch, argumentsMap);

        //call getRecord method and get SqlDataReader
        DataTable dataTable = null;
        try
        {
            dataTable = storeToDb.getRecord();

            //format the Result to return to presentation layer.
            List<Employee> listOfEmployee = null;

            if (dataTable != null)
            {
                listOfEmployee = new List<Employee>();
                foreach (DataRow row in dataTable.Rows)
                {
                    Employee emply = new Employee();
                    emply.FName = row["First_Name"].ToString();
                    emply.MName = row["Middle_Name"].ToString();
                    emply.LName = row["Last_Name"].ToString();
                    emply.EmpID = row["Emp_ID"].ToString();
                    listOfEmployee.Add(emply);
                }
            }
            return listOfEmployee;
        }
        catch (SqlException ex)
        {
            //return emptylist
            return new List<Employee>();
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            return new List<Employee>();
        }
    }

    /**
     * Get Inactive employee by date interval
     */
    public TransactionResponse getInactiveEmployeeResult(string startedDate, string endDate)
    {
        TransactionResponse response = new TransactionResponse();
        try
        {
            //Add List of Arguments for new employee
            IDictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@startDate", startedDate);
            parameters.Add("@endDate", endDate);

            DBOperationsUtil dpOperation = new DBOperationsUtil(DbAccessConstants.spGetInactiveEmployeeDetail, parameters);

            DataTable getResult = dpOperation.getRecord();
            if (getResult != null && getResult.Rows.Count > 0)
            {
                response.Data = getResult;
                response.setMessageType(TransactionResponse.SeverityLevel.SUCESS);
                response.setMessage(DBOperationErrorConstants.M_REPORT_GENERATED_SUCCESS);
                response.setSuccess(true);
                return response;
            }
            else
            {
                response.setMessageType(TransactionResponse.SeverityLevel.INFO);
                response.setMessage(DBOperationErrorConstants.M_GENERATE_INACTIVE_EMPLOYEE_REPORT_EMPTY);
                response.setSuccess(false);
                return response;
            }
        }
        catch (SqlException ex)
        {
            //Other SqlException is catched
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setMessage(DBOperationErrorConstants.M_UNABLE_TO_GENERATE_INACTIVE_EMPLOYEE);
            response.setErrorCode(DBOperationErrorConstants.E_UNKNOWN_ERROR_AT_DB_OOPERATION);
            response.setSuccess(false);
        }
        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            LoggerManager.upDateWithGenericErrorMessage(response);
        }
        return response;
    }

    /**
     * Gives the current active HR amanger.
     */
    public static string getCurrentHRManager()
    {
        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetCurrentHrManager, null);

        //call getRecord method and get SqlDataReader
        DataTable dataTable = null;
        try
        {
            dataTable = storeToDb.getRecord();

            //The if there was a result
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    return row["HrManagerEmpID"].ToString();
                }
            }
            return PageConstants.ERROR;
        }
        catch (SqlException ex)
        {
            return PageConstants.ERROR;
        }

           //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            return PageConstants.ERROR;
        }
    }

    /**
     * get employee Job title and branch detail.
     */
    public Employee getEmployeeJobTitleAndBranchDetail()
    {
        //create parameter to pass
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@Emp_ID", employee.EmpID);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeJobTitleBranchAndDistrict, argumentsMap);

        //call getRecord method and get SqlDataReader
        DataTable dataTable = null;
        Employee employeeRply = null;
        try
        {
            dataTable = storeToDb.getRecord();

            //The if there was a result
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    employeeRply = new Employee();
                    employeeRply.EmpID = employee.EmpID;
                    employeeRply.UserName = row["UserId"].ToString();
                    employeeRply.FName = row["First_Name"].ToString();
                    employeeRply.MName = row["Middle_Name"].ToString();
                    employeeRply.LName = row["Last_Name"].ToString();
                    employeeRply.Sex = row["Sex"].ToString();
                    employeeRply.PrevJob = row["PrevJob"].ToString();
                    employeeRply.JobTitle = row["JobTitle"].ToString();
                    employeeRply.Branch = row["branchName"].ToString();
                    employeeRply.BranchID = row["branch"].ToString();
                    employeeRply.JobGrade = row["Grade"].ToString();
                    employeeRply.Salary = row["Salary"].ToString();
                    employeeRply.Hdate = row["HDate"].ToString();
                    employeeRply.District = row["distric_name"].ToString();

                    //We are seaarching Employee by ID, there an only be only employee
                    return employeeRply;
                }
            }
            return employeeRply;
        }
        catch (SqlException ex)
        {
            return employeeRply;
        }

           //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            return employeeRply;
        }
    }

    public Employee detailOfEmployeToBeEvaluated(string vacancyNo, string vacancyDate)
    {
        //create parameter to pass
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@Emp_ID", employee.EmpID);
        argumentsMap.Add("@vacancyNo", vacancyNo);
        argumentsMap.Add("@vacancyDate", vacancyDate);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeToBeEvaluated, argumentsMap);

        //call getRecord method and get SqlDataReader
        DataTable dataTable = null;
        Employee employeeRply = null;
        try
        {
            dataTable = storeToDb.getRecord();

            //The if there was a result
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    employeeRply = new Employee();
                    employeeRply.EmpID = employee.EmpID;
                    employeeRply.FName = row["First_Name"].ToString();
                    employeeRply.MName = row["Middle_Name"].ToString();
                    employeeRply.LName = row["Last_Name"].ToString();
                    employeeRply.EducationalQualification = row["Educational Level"].ToString();
                    employeeRply.JobTitle = row["JobTitle"].ToString();
                    employeeRply.Branch = row["branchName"].ToString();
                    employeeRply.EmployeeType = row["applicant_type"].ToString();
                    if (row["proccessed_date"] != null && !row["proccessed_date"].ToString().Equals(""))
                    {
                        employeeRply.ProcessedDate = row["proccessed_date"].ToString();
                    }
                    if (row["checked_date"] != null && !row["checked_date"].ToString().Equals(""))
                    {
                        employeeRply.CheckedDate = row["checked_date"].ToString();
                    }

                    //We are seaarching Employee by ID, there an only be only employee
                    return employeeRply;
                }
            }
            return employeeRply;
        }
        catch (SqlException ex)
        {
            return employeeRply;
        }

           //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            return employeeRply;
        }
    }