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