/* Creates a new profile based on the data in the requestbody */
public async Task <HttpResponseMessage> CreateCoachProfile(JObject requestBodyData)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
DatabaseFunctions databaseFunctions = new DatabaseFunctions();
/* Split the requestBody in a Coach and Student entity */
JObject coachProfile = requestBodyData.SelectToken("coach").ToObject <JObject>();
JObject studentProfile = requestBodyData.SelectToken("student").ToObject <JObject>();
/* Check if the required data is present in the requestBody
* before making the CoachProfile object */
if (coachProfile["studentID"] == null ||
coachProfile["workload"] == null ||
studentProfile["studentID"] == null)
{
log.LogError("Requestbody is missing the required data");
return(exceptionHandler.BadRequest(log));
}
/* Create the entities from the split-requestBody fro readability */
Coach newCoach = coachProfile.ToObject <Coach>();
Student newStudent = studentProfile.ToObject <Student>();
/* Verify if the studentID of the "student" and the "coach" objects match.
* A [400 Bad Request] is returned if these are mismatching. */
if (newStudent.studentID != newCoach.studentID)
{
log.LogError("RequestBody has mismatching studentID for student and coach objects!");
return(exceptionHandler.BadRequest(log));
}
/* All fields for the Coach table are required */
string queryString_Coach = $@"INSERT INTO [dbo].[Coach] (studentID, workload)
VALUES (@studentID, @workload);";
/* The SQL query for the Students table has to be dynamically generated, as it contains many optional fields.
* By manually adding the columns to the query string (if they're present in the request body) we prevent
* SQL injection and ensure no illegitimate columnnames are entered into the SQL query. */
/* Dynamically create the INSERT INTO line of the SQL statement: */
string queryString_Student = $@"INSERT INTO [dbo].[Student] (";
foreach (JProperty property in studentProfile.Properties())
{
foreach (PropertyInfo props in newStudent.GetType().GetProperties())
{
if (props.Name == property.Name)
{
queryString_Student += $"{property.Name}, ";
}
}
}
queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2);
queryString_Student += ") ";
/* Dynamically create the VALUES line of the SQL statement: */
queryString_Student += "VALUES (";
foreach (JProperty property in studentProfile.Properties())
{
foreach (PropertyInfo props in newStudent.GetType().GetProperties())
{
if (props.Name == property.Name)
{
queryString_Student += $"@{property.Name}, ";
}
}
}
queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2);
queryString_Student += ");";
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 return a [503 Service Unavailable]. */
int studentCreated = 0;
connection.Open();
try {
/*Insert profile into the Student table.
* The Query may fail, in which case a [400 Bad Request] is returned. */
using (SqlCommand command = new SqlCommand(queryString_Student, connection)) {
/* Parameters are used to ensure no SQL injection can take place
* To ensure generic code, a dynamic object is made to make a new Entity and be passed into the injection function */
dynamic dObject = newStudent;
databaseFunctions.AddSqlInjection(studentProfile, dObject, command);
log.LogInformation($"Executing the following query: {queryString_Student}");
// PREVIOUSLY: await command.ExecuteReaderAsync();
studentCreated = command.ExecuteNonQuery();
}
/*Insert profile into the Coach table.
* The Query may fail, in which case a [400 Bad Request] is returned. */
using (SqlCommand command = new SqlCommand(queryString_Coach, connection)) {
/* Parameters are used to ensure no SQL injection can take place
* To ensure generic code, a dynamic object is made to make a new Entity and be passed into the injection function*/
dynamic dObject = newCoach;
databaseFunctions.AddSqlInjection(coachProfile, dObject, command);
log.LogInformation($"Executing the following query: {queryString_Coach}");
/* Is the student query affected 0 rows (i.e.: Student did not create then
* the coach cannot exists as well, so dont make the coach*/
if (studentCreated == 1)
{
// PREVIOUSLY: await command.ExecuteReaderAsync();
command.ExecuteNonQuery();
}
else
{
log.LogError($"Cannot create coach profile, student does not exists");
return(exceptionHandler.BadRequest(log));
}
}
} catch (SqlException e) {
/* The Query may fail, in which case a [400 Bad Request] is returned.
* Reasons for this failure may include a PK violation (entering an already existing studentID). */
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));
}
log.LogInformation($"{HttpStatusCode.Created} | Profile created succesfully.");
/* Return response code [201 Created]. */
return(new HttpResponseMessage(HttpStatusCode.Created));
}
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 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")
});
}
// Create a new profile based on the data in the request body.
public async Task <HttpResponseMessage> CreateTutorantProfile(JObject requestBodyData)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
DatabaseFunctions databaseFunctions = new DatabaseFunctions();
JObject tutorantProfile = requestBodyData.SelectToken("tutorant").ToObject <JObject>();
JObject studentProfile = requestBodyData.SelectToken("student").ToObject <JObject>();
// Verify if all parameters for the tables exist.
// One or more parameters may be missing, in which case a [400 Bad Request] is returned.
if (tutorantProfile["studentID"] == null ||
studentProfile["studentID"] == null)
{
log.LogError("Requestbody is missing the required data");
return(exceptionHandler.BadRequest(log));
}
Tutorant newTutorant = tutorantProfile.ToObject <Tutorant>();
Student newStudent = studentProfile.ToObject <Student>();
// Verify if the studentID of the "user" and the "tutorant" objects match.
// A [400 Bad Request] is returned if these are mismatching.
if (newTutorant.studentID != newStudent.studentID)
{
log.LogError("RequestBody has mismatching studentID for student and tutorant objects!");
return(exceptionHandler.BadRequest(log));
}
// All fields for the Tutorant table are required.
string queryStringTutorant = $@"INSERT INTO [dbo].[Tutorant] (studentID) VALUES (@studentID);";
// The SQL query for the Students table has to be dynamically generated, as it contains many optional fields.
// By manually adding the columns to the query string (if they're present in the request body) we prevent
// SQL injection and ensure no illegitimate columnnames are entered into the SQL query.
// Dynamically create the INSERT INTO line of the SQL statement:
string queryString_Student = $@"INSERT INTO [dbo].[Student] (";
foreach (JProperty property in studentProfile.Properties())
{
foreach (PropertyInfo props in newStudent.GetType().GetProperties())
{
if (props.Name == property.Name)
{
queryString_Student += $"{property.Name}, ";
}
}
}
queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2);
queryString_Student += ") ";
// Dynamically create the VALUES line of the SQL statement:
queryString_Student += "VALUES (";
foreach (JProperty property in studentProfile.Properties())
{
foreach (PropertyInfo props in newStudent.GetType().GetProperties())
{
if (props.Name == property.Name)
{
queryString_Student += $"@{property.Name}, ";
}
}
}
queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2);
queryString_Student += ");";
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 return a [503 Service Unavailable].
int studentCreated = 0;
connection.Open();
try {
// Insert profile into the Student table
using (SqlCommand command = new SqlCommand(queryString_Student, connection)) {
// Parameters are used to ensure no SQL injection can take place.
dynamic dObject = newStudent;
databaseFunctions.AddSqlInjection(studentProfile, dObject, command);
log.LogInformation($"Executing the following query: {queryString_Student}");
studentCreated = command.ExecuteNonQuery();
}
// Insert profile into the Tutorant table.
using (SqlCommand command = new SqlCommand(queryStringTutorant, connection)) {
// Parameters are used to ensure no SQL injection can take place.
dynamic dObject = newTutorant;
databaseFunctions.AddSqlInjection(tutorantProfile, dObject, command);
log.LogInformation($"Executing the following query: {queryStringTutorant}");
if (studentCreated == 1)
{
command.ExecuteNonQuery();
}
else
{
log.LogError($"Cannot create tutorant profile, student does not exists");
return(exceptionHandler.BadRequest(log));
}
}
} catch (SqlException e) {
// The Query may fail, in which case a [400 Bad Request] is returned.
// Reasons for this failure may include a PK violation (entering an already existing studentID).
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));
}
log.LogInformation($"{HttpStatusCode.Created} | Profile created succesfully.");
// Return response code [201 Created].
return(new HttpResponseMessage(HttpStatusCode.Created));
}
/* Update the data from the student given by a requestBody */
public async Task <HttpResponseMessage> UpdateStudentByID(int studentID, JObject requestBodyData)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
DatabaseFunctions databaseFunctions = new DatabaseFunctions();
/* Read the requestBody and put the response into a jObject which can be read later
* Also make a new user object and store the data in the user */
/* If the responseBody is empty (no data has been given by the user)
* return a BadRequest to say that the User must fill the requestBody.
* Bad request is status code 400 */
if (requestBodyData["studentID"] == null)
{
log.LogError($"Requestbody contains no studentID");
return(exceptionHandler.BadRequest(log));
}
Student newStudent = requestBodyData.ToObject <Student>();
string queryString = $"UPDATE [dbo].[Student] SET ";
/* Loop through the properties of the jObject Object which contains the values given in the requestBody
* loop through the hardcoded properties in the Student Entity to check if they correspond with the requestBody
* to prevent SQL injection. */
foreach (JProperty property in requestBodyData.Properties())
{
foreach (PropertyInfo props in newStudent.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}, ";
}
}
}
/* Remove the last character from the queryString, which is ','
* And add the WHERE statement*/
queryString = databaseFunctions.RemoveLastCharacters(queryString, 2);
queryString += $" 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)) {
/* Parameters are used to prevent SQLInjection */
databaseFunctions.AddSqlInjection(requestBodyData, newStudent, 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 connection has failed to open.");
log.LogError(e.Message);
return(exceptionHandler.ServiceUnavailable(log));
}
log.LogInformation($"Changed data of student: {studentID}");
//Return response code [204 NoContent].
return(new HttpResponseMessage(HttpStatusCode.NoContent));
}
