/* Returns the data from all the students that were created (Coaches and Tutorants) * based on the filters given by the user through query parameters. */ public async Task <HttpResponseMessage> GetAllStudents(List <string> parameters, List <string> propertyNames) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); List <Student> listOfStudents = new List <Student>(); string queryString = $"SELECT * FROM [dbo].[Student]"; /* If there are any query parameters, loop through the properties of the User * to check if they exist, if so, add the given property with its query value * to the queryString. This enables filtering between individual words in * the interests and study columns */ if (parameters.Count != 0 && parameters[0] != "") { queryString += $" WHERE"; for (int i = 0; i < parameters.Count; ++i) { if (parameters[i] == "interests" || parameters[i] == "study" || parameters[i] == "vooropleiding") { queryString += $" {propertyNames[i]} LIKE '%{parameters[i]}' AND"; } else { queryString += $" {propertyNames[i]} = '{parameters[i]}' AND"; } } //Remove ' AND' from the queryString to ensure this is the end of the filtering queryString = databaseFunctions.RemoveLastCharacters(queryString, 4); } else if (propertyNames.Count != 0 && parameters[0] == "") { queryString += $" ORDER BY"; for (int i = 0; i < parameters.Count; ++i) { queryString += $" {propertyNames[i]} AND"; } /* Remove ' AND' from the queryString to ensure this is the end of the filtering */ queryString = databaseFunctions.RemoveLastCharacters(queryString, 4); } try { using (SqlConnection connection = new SqlConnection(connectionString)) { /* The connection is automatically closed when going out of scope of the using block. * The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ connection.Open(); try { using (SqlCommand command = new SqlCommand(queryString, connection)) { log.LogInformation($"Executing the following query: {queryString}"); /* Executing the queryString to get all Student profiles * and add the data of all students to a list of students */ using (SqlDataReader reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { listOfStudents.Add(new Student { studentID = reader.GetInt32(0), firstName = SafeReader.SafeGetString(reader, 1), surName = SafeReader.SafeGetString(reader, 2), phoneNumber = SafeReader.SafeGetString(reader, 3), photo = SafeReader.SafeGetString(reader, 4), description = SafeReader.SafeGetString(reader, 5), degree = SafeReader.SafeGetString(reader, 6), study = SafeReader.SafeGetString(reader, 7), studyYear = SafeReader.SafeGetInt(reader, 8), interests = SafeReader.SafeGetString(reader, 9), vooropleiding = SafeReader.SafeGetString(reader, 10) }); } } } } catch (SqlException e) { /* The Query may fail, in which case a [400 Bad Request] is returned. */ log.LogError("SQL Query has failed to execute."); log.LogError(e.Message); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { /* The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ log.LogError("SQL connection has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } /* Convert the list of students to a JSON and Log a OK message */ var jsonToReturn = JsonConvert.SerializeObject(listOfStudents); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully"); /* Return the JSON. Return status code 200 */ return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }
/* * Returns the data from a specific student (Coaches and Tutorants) * given by the studentID in the path. */ public async Task <HttpResponseMessage> GetStudentByID(int studentID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); Student newStudent = new Student(); /* Initialize the queryString */ string queryString = $"SELECT * FROM [dbo].[Student] WHERE studentID = @studentID;"; try { using (SqlConnection connection = new SqlConnection(connectionString)) { /* * The connection is automatically closed when going out of scope of the using block. * The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ connection.Open(); try { using (SqlCommand command = new SqlCommand(queryString, connection)) { /* Adding SQL Injection to the StudentID parameter to prevent SQL attacks */ command.Parameters.Add("@studentID", System.Data.SqlDbType.Int).Value = studentID; /* * Executing the queryString to get the student profile * and add the data of the student to a newStudent */ log.LogInformation($"Executing the following query: {queryString}"); using (SqlDataReader reader = await command.ExecuteReaderAsync()) { /* If the student does not exist, it returns a notFoundException */ /* Return status code 404 */ if (!reader.HasRows) { return(exceptionHandler.NotFound()); } while (reader.Read()) { newStudent = new Student { studentID = reader.GetInt32(0), firstName = SafeReader.SafeGetString(reader, 1), surName = SafeReader.SafeGetString(reader, 2), phoneNumber = SafeReader.SafeGetString(reader, 3), photo = SafeReader.SafeGetString(reader, 4), description = SafeReader.SafeGetString(reader, 5), degree = SafeReader.SafeGetString(reader, 6), study = SafeReader.SafeGetString(reader, 7), studyYear = SafeReader.SafeGetInt(reader, 8), interests = SafeReader.SafeGetString(reader, 9), vooropleiding = SafeReader.SafeGetString(reader, 10) }; } } } } catch (SqlException e) { /* The Query may fail, in which case a [400 Bad Request] is returned. */ log.LogError("Could not perform given query on the database"); log.LogError(e.Message); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { /* The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ log.LogError("SQL has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } /* Convert the student to a JSON and Log a OK message */ var jsonToReturn = JsonConvert.SerializeObject(newStudent); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully"); /* Return the JSON Return status code 200 */ return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }