public List<Employee> SelectAllEmployees()
{
string Idtmp = "";
string Usernametmp = "";
string PasswordHashtmp = "";
string FirstNametmp = "";
string LastNametmp = "";
string Emailtmp = "";
int ShiftGroupIDtmp = 0;
int DivisionIDtmp = 0;
var empList = new List<Employee>();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT * FROM dbo.AspNetUsers";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check if the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
// Save DB-Return into variables
// wihout Role and StaffID
Idtmp = reader.GetString(reader.GetOrdinal("Id"));
Usernametmp = reader.GetString(reader.GetOrdinal("Username"));
PasswordHashtmp = reader.GetString(reader.GetOrdinal("PasswordHash"));
FirstNametmp = reader.GetString(reader.GetOrdinal("FirstName"));
LastNametmp = reader.GetString(reader.GetOrdinal("LastName"));
Emailtmp = reader.GetString(reader.GetOrdinal("Email"));
if (reader.IsDBNull(reader.GetOrdinal("ShiftGroupID")))
//Test, ob ID NULL, wenn ja ID = 0
{
ShiftGroupIDtmp = 0;
}
else
{
ShiftGroupIDtmp = reader.GetInt32(reader.GetOrdinal("ShiftGroupID"));
}
DivisionIDtmp = reader.GetInt32(reader.GetOrdinal("DivisionID"));
Employee emp = new Employee(Idtmp, Usernametmp, PasswordHashtmp, FirstNametmp,
LastNametmp, Emailtmp, ShiftGroupIDtmp, DivisionIDtmp);
empList.Add(emp);
}
}
}
}
return empList;
}
}
public void SelectEmployeesOfDivisionTest_F5208_K_1()
{
string Idtmp = "c";
string Usernametmp = "c";
string PasswordHashtmp = "c";
string FirstNametmp = "c";
string LastNametmp = "c";
string Emailtmp = "c";
int ShiftGroupIDtmp = 0;
int DivisionIDtmp = 0;
var EmpList = new List<Employee>();
List<List<object>> MegaListeDB = new List<List<object>>();
List<object> MegaListeDB1 = new List<object>();
List<object> MegaListeDB2 = new List<object>();
List<object> MegaListeDB3 = new List<object>();
//ulong VacationRequestIDtmp = Convert.ToUInt64(bla);
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT * FROM dbo.AspNetUsers WHERE DivisionID = (SELECT DivisionID FROM dbo.AspNetUsers WHERE Id = @EmployeeID)";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeID", "b03a5057-7505-4d5e-997f-efc0b40ed6e0");
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
int i = 0;
// Check if the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
// Save DB-Return into variables
// wihout Role and StaffID
Idtmp = reader.GetString(reader.GetOrdinal("Id"));
Usernametmp = reader.GetString(reader.GetOrdinal("Username"));
PasswordHashtmp = reader.GetString(reader.GetOrdinal("PasswordHash"));
FirstNametmp = reader.GetString(reader.GetOrdinal("FirstName"));
LastNametmp = reader.GetString(reader.GetOrdinal("LastName"));
Emailtmp = reader.GetString(reader.GetOrdinal("Email"));
if (reader.IsDBNull(reader.GetOrdinal("ShiftGroupID")))
//Test, ob ID NULL, wenn ja ID = 0
{
ShiftGroupIDtmp = 0;
}
else
{
ShiftGroupIDtmp = reader.GetInt32(reader.GetOrdinal("ShiftGroupID"));
}
DivisionIDtmp = reader.GetInt32(reader.GetOrdinal("DivisionID"));
Employee DivEmployee = new Employee(Idtmp, Usernametmp, PasswordHashtmp, FirstNametmp,
LastNametmp, Emailtmp, ShiftGroupIDtmp, DivisionIDtmp);
EmpList.Add(DivEmployee);
if (i == 1) //here you can choice which position you will test
{
MegaListeDB1.Add(Idtmp);
MegaListeDB1.Add(Usernametmp);
MegaListeDB1.Add(PasswordHashtmp);
MegaListeDB1.Add(FirstNametmp);
MegaListeDB1.Add(LastNametmp);
MegaListeDB1.Add(Emailtmp);
MegaListeDB1.Add(ShiftGroupIDtmp);
MegaListeDB1.Add(DivisionIDtmp);
}
i++;
}
}
}
}
} //Create list instead of the object 'Employee' to make it comparable
MegaListeDB.Add(MegaListeDB1);
List<List<object>> MegaListe = new List<List<object>>();
List<object> MegaListe1 = new List<object>();
MegaListe1.Add("b03a5057-7505-4d5e-997f-efc0b40ed6e0");
MegaListe1.Add("Login2");
MegaListe1.Add("AMwipYYgMsGCx+p/KtU20DwWkhb2PMRRGlc2oqzNsXpsugQS2FiIR0QHsUllTsJpqA==");
MegaListe1.Add("Vorname2");
MegaListe1.Add("Nachname2");
MegaListe1.Add("*****@*****.**");
MegaListe1.Add(1);
MegaListe1.Add(1);
MegaListe.Add(MegaListe1);
CollectionAssert.AreEqual(MegaListe1,MegaListeDB1);
}
/// <summary>
/// selects the Employee of an VacationRequest
/// </summary>
/// <param name="VacationRequestID">ID of the VacationRequest</param>
/// <returns>EmployeeID of the VacationRequest owner</returns>
public Employee SelectRequester(ulong VacationRequestID)
{
string Idtmp = "c";
string Usernametmp = "c";
string PasswordHashtmp = "c";
string FirstNametmp = "c";
string LastNametmp = "c";
string Emailtmp = "c";
int ShiftGroupIDtmp = 0;
int DivisionIDtmp = 0;
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT Id, Username, PasswordHash, LastName, FirstName, Email, ShiftGroupID, DivisionID FROM dbo.AspNetUsers WHERE Id = (SELECT EmployeeID FROM dbo.VacationRequest WHERE VacationRequestID = @VacationRequestID)";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("VacationRequestID", (long)VacationRequestID);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check if the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
// Save DB-Return into variables
// wihout Role and StaffID
Idtmp = reader.GetString(reader.GetOrdinal("Id"));
Usernametmp = reader.GetString(reader.GetOrdinal("Username"));
PasswordHashtmp = reader.GetString(reader.GetOrdinal("PasswordHash"));
FirstNametmp = reader.GetString(reader.GetOrdinal("FirstName"));
LastNametmp = reader.GetString(reader.GetOrdinal("LastName"));
Emailtmp = reader.GetString(reader.GetOrdinal("Email"));
if (reader.IsDBNull(reader.GetOrdinal("ShiftGroupID"))) //if ID NULL, then ID = 0, no Shiftgroup 0 in database
{
ShiftGroupIDtmp = 0; // 0 means, no Shiftgroup for that Employee
}
else
{
ShiftGroupIDtmp = reader.GetInt32(reader.GetOrdinal("ShiftGroupID"));
}
DivisionIDtmp = reader.GetInt32(reader.GetOrdinal("DivisionID"));
}
}
}
}
}
Employee Requester = new Employee(Idtmp, Usernametmp, PasswordHashtmp, FirstNametmp,
LastNametmp, Emailtmp, ShiftGroupIDtmp, DivisionIDtmp);
return Requester;
}
