C# DBOperationsUtil类代码示例

    /**
     * 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;
    }

    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();
                }
            }

        }
    }

    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;
        }
    }

    private TransactionResponse sendInsertPromotionToDb(string spName, IDictionary<string, object> parameters)
    {
        //Pass Stored Procedure Name and parameter list. 
        DBOperationsUtil storeToDb = new DBOperationsUtil(spName, parameters);

        TransactionResponse response = new TransactionResponse();
        try
        {
            //call store to DB mathod and get reponse. 
            storeToDb.instertNewRecord();
            response.setSuccess(true);
            response.setMessageType(TransactionResponse.SeverityLevel.SUCESS);
            response.setMessage(DBOperationErrorConstants.M_PROMOTION_REGISTER_OK);
        }

        catch (SqlException ex) 
        {
            //Determine if the cause was duplicate KEY.
            if (ex.ToString().Contains(DBOperationErrorConstants.PK_DUPLICATE_INDICATOR))
            {
                response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
                response.setMessage(DBOperationErrorConstants.M_DUPLICATE_PROMOTION_KEY_ERROR);
                response.setErrorCode(DBOperationErrorConstants.E_DUPLICATE_KEY_ERROR);
            }
            else
            {
                //Other SqlException is catched
                response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
                response.setMessage(DBOperationErrorConstants.M_UNKNOWN_ERROR_APP_RATING_REGISTER);
                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. 
            logException(ex);
            LoggerManager.upDateWithGenericErrorMessage(response);
        }

        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);
        }
    }

    public TransactionResponse addNewPromotionAssignment()
    {
        //Add List of Arguments for new promotion Assigment
        IDictionary<string, object> promotionAssigmentParameters = new Dictionary<string, object>();

        promotionAssigmentParameters.Add("@minNo", promotionAssigment.MinuteNo);
        promotionAssigmentParameters.Add("@hROfficerID", promotionAssigment.HROfficerID);
        promotionAssigmentParameters.Add("@deadLine", promotionAssigment.DeadLine);
        promotionAssigmentParameters.Add("@remark", promotionAssigment.Remark);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil dpOperation = new DBOperationsUtil(DbAccessConstants.spAddPromotionAssignmentToHROfficer, promotionAssigmentParameters);
        TransactionResponse response = new TransactionResponse();
        try
        {
            //call update to DB mathod and get reponse.
            dpOperation.updateRecord();
            response.setSuccess(true);
            response.setMessageType(TransactionResponse.SeverityLevel.SUCESS);
            response.setMessage(DBOperationErrorConstants.M_VACANCY_ASSIGNED_SUCCESS);
        }

        catch (SqlException ex)
        {
            //Other SqlException is catched
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setMessage(DBOperationErrorConstants.PK_DUPLICATE_INDICATOR + ". "+ DBOperationErrorConstants.M_DUPLICATE_PROMOTION_ASSIGNEMENT);
            response.setErrorCode(DBOperationErrorConstants.E_PROMOTION_ASSIGNEMETN_FAILED);
        }

        //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;
    }

    /**
     * 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;
        }
    }

    public DataTable getUnassignedPromotion()
    {
        //Pass Stored Procedure Name and parameter list.
        IDictionary<string, object> parameters = new Dictionary<string, object>();
        parameters.Add("@districtID", PageAccessManager.getDistrictID());
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetPromotionAssignedToHROfficer, parameters);

        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.
            logException(ex);
            return dataTable;
        }
    }

    //this methode return a list of promoted or Assigned Employee for specified Post
    public DataTable getSpecifiedPromotionResult(int postID, string promotionOrAssigned)
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@Post", postID);
        argumentsMap.Add("@status", promotionOrAssigned);

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

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

        return dataTable;
    }

    public DataTable getmanagerialPosition(string branch)
    {
        //Pass Stored Procedure Name and parameter list.
        //Add List of Arguments TO GET managerial position
        IDictionary<string, object> branchID = new Dictionary<string, object>();

        branchID.Add("@branch", branch);
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmpAtManagerialPosition, branchID);

        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.
            logException(ex);
            return dataTable;
        }
    }