/**
* Purpose: Gets all employee records from database
* Return:
* List of all employee records
*/
public List <Employee> getAll()
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
List <Employee> list = new List <Employee>();
// Execute the query to retrieve all customers
conn.execute(@"SELECT
EmployeeId,
FirstName,
LastName,
DepartmentId,
Administrator
FROM Employee
WHERE EndDate IS NULL",
(SqliteDataReader reader) => {
while (reader.Read())
{
list.Add(new Employee
{
EmployeeId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString(),
DepartmentId = reader.GetInt32(3),
Administrator = Convert.ToBoolean(reader.GetInt32(4))
});
}
}
);
return(list);
}
/**
* Purpose: Gets a single employee from database
* Arguments:
* EmployeeId - An Employee ID
* Return:
* Single employee from database
*/
public Employee get(int EmployeeId)
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
Employee e = null;
conn.execute(@"SELECT
EmployeeId,
FirstName,
LastName,
DepartmentId,
Administrator
FROM Employee
WHERE EndDate IS NULL AND EmployeeId = " + EmployeeId, (SqliteDataReader reader) => {
while (reader.Read())
{
e = new Employee
{
EmployeeId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString(),
DepartmentId = reader.GetInt32(3),
Administrator = Convert.ToBoolean(reader.GetInt32(4))
};
}
});
return(e);
}
/**
* Purpose: Gets all deparments from database
* Return:
* List of all department records
*/
public List <Department> getAll()
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
List <Department> list = new List <Department>();
// Execute the query to retrieve all customers
conn.execute(@"SELECT
DepartmentId,
Name
FROM Department",
(SqliteDataReader reader) =>
{
while (reader.Read())
{
list.Add(new Department
{
DepartmentId = reader.GetInt32(0),
Label = reader[1].ToString()
});
}
}
);
return(list);
}
public Employee getEmployeeByFullName(string fullName)
{
// Break the string into an array, whitespace as the delimiter
string[] fullNameAsArray = Regex.Split(fullName, @"\s+").Where(s => s != string.Empty).ToArray();
string firstName = fullNameAsArray[0];
string lastName = fullNameAsArray[1];
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
Employee e = null;
conn.execute(@"SELECT
EmployeeId,
FirstName,
LastName,
DepartmentId,
Administrator
FROM Employee
WHERE EndDate IS NULL AND FirstName = '" + firstName + "' AND " + "LastName = '" + lastName + "'", // pls no sql hacks :(
(SqliteDataReader reader) => {
while (reader.Read())
{
e = new Employee
{
EmployeeId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString(),
DepartmentId = reader.GetInt32(3),
Administrator = Convert.ToBoolean(reader.GetInt32(4))
};
}
});
return(e);
}
/**
* Purpose: Gets a single department from database
* Arguments:
* DepartmentId - A department ID
* Return:
* Single department from database
*/
public Department get(int DepartmentId)
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
Department d = null;
conn.execute(@"SELECT
DepartmentId,
Name
FROM Department
WHERE DepartmentId = " + DepartmentId, (SqliteDataReader reader) =>
{
while (reader.Read())
{
d = new Department
{
DepartmentId = reader.GetInt32(0),
Label = reader[1].ToString()
};
}
});
return(d);
}