static void TestEmployee()
{
DataAccessLayer.Employee objE = new DataAccessLayer.Employee();
int NewID, RC;
RC = objE.InsEmployee("test", out NewID);
Console.WriteLine("RC: {0} and NewID: {1}", RC, NewID);
foreach (Employee empl in objE.SelEmployee())
{
Console.WriteLine("Employee ID: {0}, Employee Name: {1}", empl.EmployeeID, empl.EmployeeName);
}
RC = objE.UpdEmployee(NewID, "test2");
Console.WriteLine("RC: {0} on update call", RC);
foreach (Employee empl in objE.SelEmployee())
{
Console.WriteLine("Employee ID: {0}, Employee Name: {1}", empl.EmployeeID, empl.EmployeeName);
}
RC = objE.DelEmployee(NewID);
Console.WriteLine("RC: {0} on delete call", RC);
foreach (Employee empl in objE.SelEmployee())
{
Console.WriteLine("Employee ID: {0}, Employee Name: {1}", empl.EmployeeID, empl.EmployeeName);
}
}
public FormManager()
{
InitializeComponent();
eph = new List<EmployeeProjectHour>();
objEPH = new EmployeeProjectHour();
listEmployee = new List<Employee>();
empl = new Employee();
listProject = new List<Project>();
prjt = new Project();
}
public ActionResult formCollection(FormCollection formCollection)
{
Employee emp = new Employee();
DataAccessLib db = new DataAccessLib();
emp.Name = formCollection["Name"];
emp.Designation = formCollection["Designation"];
emp.Gender = formCollection["Gender"];
emp.Salary = Convert.ToDouble(formCollection["Salary"]);
emp.City = formCollection["City"];
emp.State = formCollection["State"];
emp.Zip = formCollection["Zip"];
db.AddEmployees(emp);
return RedirectToAction("Index");
}
public void AddEmployees(Employee emp)
{
string con = System.Configuration.ConfigurationManager.ConnectionStrings["connectionStr"].ToString();
SqlConnection sql_conn = new SqlConnection(con);
sql_conn.Open();
using (SqlCommand sql_command = new SqlCommand("uspInsertEmployee", sql_conn))
{
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Clear();
sql_command.Parameters.AddWithValue("@name", emp.Name);
sql_command.Parameters.AddWithValue("@designation", emp.Designation);
sql_command.Parameters.AddWithValue("@gender", emp.Gender);
sql_command.Parameters.AddWithValue("@salary", emp.Salary);
sql_command.Parameters.AddWithValue("@city", emp.City);
sql_command.Parameters.AddWithValue("@state", emp.State);
sql_command.Parameters.AddWithValue("@zip", emp.Zip);
sql_command.ExecuteNonQuery();
}
}
public IEnumerable<Employee> SelEmployee()
{
//Set up the variables
int intRC = 0;
SqlDataReader dr;
List<Employee> retVal = new List<Employee>();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeesParameterFactory();
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add((objParams.Parameters["RC"]));
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pSelEmployees";
dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
Employee empl = new Employee();
empl.EmployeeID = (int) dr["EmployeeID"];
empl.EmployeeName = (string) dr["EmployeeName"];
retVal.Add(empl);
}
dr.Close();
//check the return code for errors
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return retVal;
}
static void TestEmployeeProjectHour()
{
DataAccessLayer.EmployeeProjectHour objEph = new DataAccessLayer.EmployeeProjectHour();
int RC;
DateTime dt = DateTime.Now;
int newEmployeeID;
int newProjectID;
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
//create an employee and a project to insert
Employee objEmpl = new Employee();
objEmpl.InsEmployee("bob dobalina",out newEmployeeID);
Project objProj = new Project();
objProj.InsProject("recording","", out newProjectID);
RC = objEph.InsEmployeeProjectHours(newEmployeeID, newProjectID, dt, 42);
Console.WriteLine();
Console.WriteLine("RC: {0}", RC);
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
RC = objEph.UpdEmployeeProjectHours(newEmployeeID, newProjectID, dt, 104);
Console.WriteLine();
Console.WriteLine("RC: {0}", RC);
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
RC = objEph.DelEmployeeProjectHours(newEmployeeID, newProjectID, dt);
Console.WriteLine();
Console.WriteLine("RC: {0}", RC);
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
objEmpl.DelEmployee(newEmployeeID);
objProj.DelProject(newProjectID);
}
public List <Employee> ReadEmployeeFromDepartmentFromDB(EmployeeDepartment department)
{
List <Employee> employees = new List <Employee>();
List <EmployeeContract> contracts = new List <EmployeeContract>();
try
{
string sql = $"SELECT FirstName, LastName, employee.EmployeeNumber, Department, DateOfBirth, City, Street, HouseNumber, HouseNumberAddition, PostCode, Email, Phone, BSN, ICEName, ICEPhone, Password FROM employee INNER JOIN employeeemploymentinfo ON employee.EmployeeNumber = employeeemploymentinfo.EmployeeNumber INNER JOIN empoyeepersonalinfo ON employee.EmployeeNumber = empoyeepersonalinfo.EmployeeNumber WHERE Department='{department}';";
MySqlCommand cmd = new MySqlCommand(sql, conn);
string contract = "SELECT EmployeeNumber, FTE, Salary, StartDate, EndDate, EndReason, IsActive, ContractNumber FROM contract;";
MySqlCommand cmdcontract = new MySqlCommand(contract, conn);
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Address adress = new Address(dr[6].ToString(), Convert.ToInt32(dr[7]), dr[8].ToString(), dr[9].ToString(), dr[5].ToString());
EmployeePersonalInfo persInfo = new EmployeePersonalInfo(Convert.ToDateTime(dr[4]), dr[11].ToString(), adress, dr[10].ToString(), dr[12].ToString(), dr[13].ToString(), dr[14].ToString(), dr[15].ToString());
EmployeeEmploymentInformation emplInfo = new EmployeeEmploymentInformation((EmployeeDepartment)Enum.Parse(typeof(EmployeeDepartment), dr[3].ToString()));
Employee e = new Employee(dr[0].ToString(), dr[1].ToString(), emplInfo, persInfo, Convert.ToInt32(dr[2]));
employees.Add(e);
}
dr.Close();
MySqlDataReader drcontr = cmdcontract.ExecuteReader();
while (drcontr.Read())
{
if (Convert.ToBoolean(drcontr[6]))
{
EmployeeContract eca = new EmployeeContract(Convert.ToDouble(drcontr[1]), Convert.ToDouble(drcontr[2]), Convert.ToDateTime(drcontr[3]), Convert.ToInt32(drcontr[0]), drcontr[7].ToString());
contracts.Add(eca);
}
else
{
EmployeeContract ec = new EmployeeContract(Convert.ToDouble(drcontr[1]), Convert.ToDouble(drcontr[2]), Convert.ToDateTime(drcontr[3]), Convert.ToDateTime(drcontr[4]), drcontr[5].ToString(), Convert.ToBoolean(drcontr[6]), Convert.ToInt32(drcontr[0]), drcontr[7].ToString());
contracts.Add(ec);
}
}
dr.Close();
foreach (EmployeeContract c in contracts)
{
foreach (Employee e in employees)
{
if (e.EmployeeNumber == c.EmplNum)
{
e.employmentInfo.InitializeContractHistory(c);
}
}
}
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(employees);
}
public IEnumerable<Employee> SelEmployee(int EmployeeID = -1)
{
int intRC = 0;
objEmployees = new List<Employee>();
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeID: EmployeeID, EmployeeName: EmployeeName);
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelEmployees", new ADOConnectionFactory().Connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add(objParams.Parmeters["RC"]);
objCmd.Parameters.Add(objParams.Parmeters["EmployeeID"]);
try
{
objCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader();
while (objDR.Read())
{
Employee objE = new Employee();
objE.intEmployeeID = (int)objDR["EmployeeID"];
objE.strEmployeeName = (string)objDR["EmployeeName"];
((List<Employee>)objEmployees).Add(objE);
}
objDR.Close(); //You cannot get the return value if the reader is not closed!
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{ throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString()); }
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return objEmployees;
}
