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