public async Task <HttpResponseMessage> UpdateMessageByID(int messageID, JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); Message newMessage = requestBodyData.ToObject <Message>(); string queryString = $"UPDATE [dbo].[Message] SET "; /* Loop through the properties of the jObject Object which contains the values given in the requestBody * loop through the hardcoded properties in the Message Entity to check if they correspond with the requestBody * to prevent SQL injection. */ foreach (JProperty property in requestBodyData.Properties()) { foreach (PropertyInfo props in newMessage.GetType().GetProperties()) { if (props.Name == property.Name) { /* fill the queryString with the property names from the Message and their values */ queryString += $"{props.Name} = @{property.Name},"; } } } queryString = databaseFunctions.RemoveLastCharacters(queryString, 1); queryString += $@" WHERE MessageID = @messageID;"; 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)) { // Parameters are used to ensure no SQL injection can take place. /* pass the requestBody, the entity with the corresponding properties and the SqlCommand to the method * to ensure working SqlInjection for the incoming values*/ databaseFunctions.AddSqlInjection(requestBodyData, newMessage, command); log.LogInformation($"Executing the following query: {queryString}"); int affectedRows = await command.ExecuteNonQueryAsync(); //The SQL query must have been incorrect if no rows were executed, 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 all connections of a specific coach public async Task <HttpResponseMessage> GetAllConnectionsByCoachID(int coachID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); List <CoachTutorantConnection> listOfCoachTutorantConnections = new List <CoachTutorantConnection>(); string queryString = $@"SELECT * FROM [dbo].[CoachTutorantConnection] WHERE studentIDCoach = @coachID"; 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 all connections from the CoachTutorantConnections table for a specific coach using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place command.Parameters.Add("@coachID", SqlDbType.Int).Value = coachID; 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()) { listOfCoachTutorantConnections.Add(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 connection has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } var jsonToReturn = JsonConvert.SerializeObject(listOfCoachTutorantConnections); 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") }); }
public async Task <HttpResponseMessage> GetMessageByID(int messageID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); Message newMessage = new Message(); string queryString = $@"SELECT * FROM [dbo].[Message] WHERE MessageID = @messageID;"; 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)) { command.Parameters.Add("@messageID", SqlDbType.Int).Value = messageID; log.LogInformation($"Executing the following query: {queryString}"); 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()) { newMessage = new Message { MessageID = reader.GetInt32(0), type = SafeReader.SafeGetString(reader, 1), payload = SafeReader.SafeGetString(reader, 2), created = SafeReader.SafeGetDateTime(reader, 3), lastModified = SafeReader.SafeGetDateTime(reader, 4), senderID = SafeReader.SafeGetInt(reader, 5), receiverID = SafeReader.SafeGetInt(reader, 6) }; } } } } 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(newMessage); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully."); //Return response code [200 OK] and the requested data. // Everything went fine, return status code 200. return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }
//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)); }