//Create a new connection between a tutorant and coach /* TODO: MAKE SURE THAT YOU CAN ONLY MAKE A CONNECTION WHEN THE STUDENTS EXISTS */ public async Task <HttpResponseMessage> CreateConnectionByTutorantID(int tutorantID, JObject tTocConnection) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); //Verify if all parameters for the CoachTutorantConnection exist. //One or more parameters may be missing, in which case a [400 Bad Request] is returned. if (tTocConnection["studentIDTutorant"] == null || tTocConnection["studentIDCoach"] == null || tTocConnection["status"] == null) { log.LogError("Requestbody is missing data for the CoachTutorantConnection table!"); return(exceptionHandler.BadRequest(log)); } /* Make a Connection entity from the requestBody after checking the required fields */ CoachTutorantConnection coachTutorantConnection = tTocConnection.ToObject <CoachTutorantConnection>(); string queryString = $@"INSERT INTO [dbo].[CoachTutorantConnection] (studentIDTutorant, studentIDCoach, status) VALUES (@studentIDTutorant, @studentIDCoach, @status);"; 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 { //Update the status for the tutorant/coach connection //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place dynamic dObject = coachTutorantConnection; databaseFunctions.AddSqlInjection(tTocConnection, dObject, command); log.LogInformation($"Executing the following query: {queryString}"); await command.ExecuteNonQueryAsync(); } } 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 has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } log.LogInformation($"{HttpStatusCode.Created} | Connection created succesfully."); //Return response code [201 Created]. return(new HttpResponseMessage(HttpStatusCode.Created)); }
//Changes the status of a CoachTutorantConnection. public async Task <HttpResponseMessage> UpdateConnection(JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); //Verify if all parameters for the CoachTutorantConnection exist. //One or more parameters may be missing, in which case a [400 Bad Request] is returned. if (requestBodyData["status"] == null) { log.LogError("Requestbody is missing data for the CoachTutorantConnection table!"); return(exceptionHandler.BadRequest(log)); } /* Make a Connection entity from the requestBody after checking the required fields */ CoachTutorantConnection coachTutorantConnection = requestBodyData.ToObject <CoachTutorantConnection>(); string queryString = $@"UPDATE [dbo].[CoachTutorantConnection] SET status = @status WHERE studentIDTutorant = @studentIDTutorant AND studentIDCoach = @studentIDCoach;"; 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 { //Update the status for the tutorant/coach connection //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place dynamic dObject = coachTutorantConnection; databaseFunctions.AddSqlInjection(requestBodyData, dObject, command); log.LogInformation($"Executing the following query: {queryString}"); int affectedRows = await command.ExecuteNonQueryAsync(); //The studentIDs must be incorrect if no rows were affected, return a [404 Not Found]. if (affectedRows == 0) { log.LogError("Zero rows were affected."); return(exceptionHandler.NotFound()); } } } 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 has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } log.LogInformation($"{HttpStatusCode.NoContent} | Data updated succesfully."); //Return response code [204 NoContent]. return(new HttpResponseMessage(HttpStatusCode.NoContent)); }
//Returns the connection of a specific tutorant public async Task <HttpResponseMessage> GetConnectionByTutorantID(int tutorantID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); CoachTutorantConnection coachTutorantConnection = new CoachTutorantConnection(); string queryString = $@"SELECT * FROM [dbo].[CoachTutorantConnection] WHERE studentIDTutorant = @tutorantID"; 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 { //Get connection from the CoachTutorantConnections table for a specific tutorant using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place command.Parameters.Add("@tutorantID", SqlDbType.Int).Value = tutorantID; log.LogInformation($"Executing the following query: {queryString}"); //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlDataReader reader = await command.ExecuteReaderAsync()) { if (!reader.HasRows) { //Query was succesfully executed, but returned no data. //Return response code [404 Not Found] log.LogError("SQL Query was succesfully executed, but returned no data."); return(exceptionHandler.NotFound()); } while (reader.Read()) { coachTutorantConnection = new CoachTutorantConnection { //Reader 0 contains coachTutorantConnectionID key (of the database), //this data is irrelevant for the user. studentIDTutorant = SafeReader.SafeGetInt(reader, 1), studentIDCoach = SafeReader.SafeGetInt(reader, 2), status = SafeReader.SafeGetString(reader, 3) }; } } } } 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 has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } var jsonToReturn = JsonConvert.SerializeObject(coachTutorantConnection); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully."); //Return response code [200 OK] and the requested data. return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }