public static PositionsDM ReadPositionById(string positionId) { PositionsDM _position = new PositionsDM(); try { using (SqlCommand cmd = new SqlCommand("READ_POSITION_BY_ID", SqlConnect.Connection)) { cmd.CommandType = CommandType.StoredProcedure; SqlConnect.Connection.Open(); cmd.Parameters.AddWithValue("@Position_ID", positionId); using (var reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { _position.PositionId = (Int64)reader["Position_ID"]; _position.PositionName = (string)reader["Position_Name"]; } } } SqlConnect.Connection.Close(); } return(_position); } catch (Exception e) { SqlConnect.Connection.Close(); throw (e); } }
public static List <PositionsDM> ReadPositions() { List <PositionsDM> _positionList = new List <PositionsDM>(); try { using (SqlCommand cmd = new SqlCommand("READ_POSITIONS", SqlConnect.Connection)) { cmd.CommandType = CommandType.StoredProcedure; SqlConnect.Connection.Open(); using (var reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var _position = new PositionsDM(); _position.PositionId = (Int64)reader["Position_ID"]; _position.PositionName = (string)reader["Position_Name"]; _positionList.Add(_position); } } } SqlConnect.Connection.Close(); } return(_positionList); } catch (Exception e) { SqlConnect.Connection.Close(); throw (e); } }
public static void DeletePosition(PositionsDM _position) { try { using (SqlCommand cmd = new SqlCommand("DELETE_POSITION", SqlConnect.Connection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Position_ID", _position.PositionId); SqlConnect.Connection.Open(); cmd.ExecuteNonQuery(); SqlConnect.Connection.Close(); } } catch (Exception e) { SqlConnect.Connection.Close(); throw (e); } }
/// <summary> /// Basic CRUD methods for Position information. PositionsDM is the model being used here. /// </summary> /// #region POSITION DAL METHODS public static void CreatePosition(PositionsDM _position) { try { //Creating a way of adding new user information to my database using (SqlCommand cmd = new SqlCommand("CREATE_POSITION", SqlConnect.Connection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Position_Name", _position.PositionName); SqlConnect.Connection.Open(); cmd.ExecuteNonQuery(); SqlConnect.Connection.Close(); } } catch (Exception e) { SqlConnect.Connection.Close(); throw (e); } }
public ActionResult ReadPositionByID(PositionsDM position) { return(View(Mapper.Map <PositionsVM>(PositionsDAL.ReadPositionById(position.PositionId.ToString())))); }
public List <EmployeeDM> ReadEmployees() { List <EmployeeDM> employeeList = new List <EmployeeDM>(); try { SqlConnect.Connection.Open(); using (SqlCommand cmd = new SqlCommand("READ_EMPLOYEES", SqlConnect.Connection)) { cmd.CommandType = CommandType.StoredProcedure; using (var reader = cmd.ExecuteReader()) { if (!reader.HasRows) { return(employeeList); } while (reader.Read()) { //Creating objects of the modals inside the loop so that //the object can be used for new information every iteration of the loop. #region Modal Objects EmployeeDM employee = new EmployeeDM(); AddressDM address = new AddressDM(); EmployeeTimeDM EmployeeTime = new EmployeeTimeDM(); PositionsDM position = new PositionsDM(); StatusDM Status = new StatusDM(); #endregion #region Pulling Employee Table Information employee.EmployeeId = (Int64)reader["Employee_ID"]; employee.EmployeeNumber = (string)reader["Employee_Number"]; employee.EmployeeName = (string)reader["Employee_Name"]; employee.EmployeeFirstName = (string)reader["Employee_FirstName"]; employee.EmployeeMiddle = (string)reader["Employee_MiddleName"]; employee.EmployeeLastName = (string)reader["Employee_LastName"]; employee.Age = (int)reader["Age"]; employee.BirthDate = (DateTime)reader["Birth_Date"]; if (reader["Team_ID"] != DBNull.Value) { employee.TeamID = (Int64)reader["Team_ID"]; } if (reader["Role_ID"] != DBNull.Value) { employee.RoleID = (Int64)reader["Role_ID"]; } if (reader["Assignment_ID"] != DBNull.Value) { employee.AssignmentID = (Int64)reader["Assignment_ID"]; } #endregion //#region Pulling Address Table Information //address.Address = (string)reader["Address"]; //address.City = (string)reader["City"]; //address.State = (string)reader["State"]; //address.Country = (string)reader["Country"]; //address.Zip_Code = (int)reader["Zip_Code"]; //address.Phone = (string)reader["Phone"]; //address.Email = (string)reader["Email"]; //#endregion //#region Pulls Employee Time Table Information //EmployeeTime.Other_Total = (decimal)reader["Other_Total"]; //EmployeeTime.Other_Available = (decimal)reader["Other_Available"]; //EmployeeTime.Other_Used = (decimal)reader["Other_Used"]; //EmployeeTime.Payed_Total = (decimal)reader["Payed_Total"]; //EmployeeTime.Payed_Used = (decimal)reader["Payed_Total"]; //#endregion //#region Pulls Employee Work Status Information //Status.EmployeeStatus = (string)reader["Employee_Status"]; //Status.HireDate = (DateTime)reader["Hire_Date"]; //Status.PayType = (string)reader["Pay_Type"]; //Status.ServiceLength = (string)reader["Service_Length"]; //Status.EmploymentType = (string)reader["Employment_Type"]; //Status.OfficeLocation = (string)reader["Office_Location"]; //if (reader["Termination_Date"] != DBNull.Value) // Status.TerminationDate = (DateTime)reader["Termination_Date"]; //#endregion ////Adding the object properties to the employment object to be used together for the view modal //employee.address = address; employee.EmployeeTime = EmployeeTime; employee.Status = Status; employeeList.Add(employee); } } cmd.Connection.Close(); } return(employeeList); } catch (Exception ex) { throw ex; } finally { SqlConnect.Connection.Close(); } }