// Creates a new message based on data given in the request body.
public async Task <HttpResponseMessage> CreateMessage(JObject requestBodyData)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
DatabaseFunctions databaseFunctions = new DatabaseFunctions();
// Verify if all parameters for the Message table exist,
// return response code 400 if one or more of the parameters are missing.
if (requestBodyData["type"] == null || requestBodyData["payload"] == null ||
requestBodyData["created"] == null || requestBodyData["lastModified"] == null ||
requestBodyData["senderID"] == null || requestBodyData["receiverID"] == null)
{
log.LogError($"Requestbody is missing data for the Message table!");
return(exceptionHandler.BadRequest(log));
}
Message newMessage = requestBodyData.ToObject <Message>();
// All fields for the Message table are required.
string queryString = $@"INSERT INTO [dbo].[Message] (type, payload, created, lastModified, senderID, receiverID)" +
$"VALUES (@type, @payload, @created, @lastModified, @senderID, @receiverID);";
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].
connection.Open();
try {
// Insert new message into the Message table.
using (SqlCommand command = new SqlCommand(queryString, connection)) {
// Parameters are used to ensure no SQL injection can take place.
dynamic dObject = newMessage;
databaseFunctions.AddSqlInjection(requestBodyData, 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.
// 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} | Message created succesfully.");
// Return response code [201 Created].
return(new HttpResponseMessage(HttpStatusCode.Created));
}
//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));
}
/*Returns */
public async Task <HttpResponseMessage> TestToken()
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
JObject jObject;
Tokens token;
/* Read from the requestBody */
using (StringReader reader = new StringReader(await req.Content.ReadAsStringAsync()))
{
jObject = JsonConvert.DeserializeObject <JObject>(reader.ReadToEnd());
token = jObject.ToObject <Tokens>();
}
/* Verify if all parameters for the Auth table exist.
* One or more parameters may be missing, in which case a [400 Bad Request] is returned. */
if (token.token == null)
{
log.LogError("Requestbody is missing data for the Auth table!");
return(exceptionHandler.BadRequest(log));
}
HttpResponseMessage response;
if (checkTokenValid(token.token))
{
//Return response code [200 OK].
response = new HttpResponseMessage(HttpStatusCode.BadRequest);
response.Content = new StringContent("Token is valid");
return(response);
}
//Return response code [200 OK].
response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new StringContent("Token is not valid");
return(response);
}
//Delete the connections of a specific tutorant
public async Task <HttpResponseMessage> DeleteConnectionByTutorantID(int tutorantID)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
//Query string used to delete the coach from the coach table
string queryString = $@"DELETE 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 {
//Delete the connection from a specific tutorant in the CoachTutorantConnection table
//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
command.Parameters.Add("@tutorantID", SqlDbType.Int).Value = tutorantID;
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 deleted succesfully.");
//Return response code [204 NoContent].
return(new HttpResponseMessage(HttpStatusCode.NoContent));
}
public async Task <HttpResponseMessage> DeleteMessageByID(int messageID)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
string queryString = $@"DELETE 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.DateTime).Value = messageID;
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 while deleting from the Tutorant table.");
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.");
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.");
return(exceptionHandler.ServiceUnavailable(log));
}
log.LogInformation($"{HttpStatusCode.NoContent} | Data deleted succesfully");
// Return response code [204 NoContent].
return(new HttpResponseMessage(HttpStatusCode.NoContent));
}
/* Returns the workload of the coach (from the coach table) */
public async Task <HttpResponseMessage> GetCoachByID(int coachID)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
Coach newCoach = new Coach();
string queryString = $@"SELECT *
FROM [dbo].[Coach]
WHERE studentID = @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 data from the Coach table by studentID
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())
{
newCoach = new Coach {
studentID = SafeReader.SafeGetInt(reader, 1),
workload = SafeReader.SafeGetInt(reader, 2)
};
}
}
}
} 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(newCoach);
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")
});
}
/* Returns the workload of the coach (from the coach table) */
/* Returns the profile of all coaches (from the student table)
* and the workload of all coaches (from the coach table) */
public async Task <HttpResponseMessage> GetAllCoachProfiles()
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
List <CoachProfile> listOfCoachProfiles = new List <CoachProfile>();
string queryString = $@"SELECT Student.*, Coach.workload
FROM [dbo].[Student]
INNER JOIN [dbo].[Coach]
ON Student.studentID = Coach.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 {
/* Get all profiles from the Student and Coach tables */
using (SqlCommand command = new SqlCommand(queryString, connection)) {
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())
{
listOfCoachProfiles.Add(new CoachProfile(
new Coach {
studentID = SafeReader.SafeGetInt(reader, 0),
workload = SafeReader.SafeGetInt(reader, 10)
},
new Student {
studentID = SafeReader.SafeGetInt(reader, 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)
}
));
}
}
}
} 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));
}
string jsonToReturn = JsonConvert.SerializeObject(listOfCoachProfiles);
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")
});
}
/* 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));
}
/* 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));
}
//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 profile of the tutorant (from the student table).
public async Task <HttpResponseMessage> GetTutorantProfileByID(int tutorantID)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
TutorantProfile newTutorantProfile = new TutorantProfile();
string queryString = $@"SELECT Student.* FROM [dbo].[Student]
INNER JOIN [dbo].[Tutorant]
ON Student.studentID = Tutorant.studentID
WHERE Student.studentID = @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 {
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())
{
newTutorantProfile = new TutorantProfile(
new Tutorant {
studentID = SafeReader.SafeGetInt(reader, 0)
},
new Student {
studentID = SafeReader.SafeGetInt(reader, 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)
}
);
}
}
}
} 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(newTutorantProfile);
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")
});
}
// 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));
}
/* 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")
});
}
/* Updates the workload of the coach (in the coach table) */
public async Task <HttpResponseMessage> UpdateCoachByID(int coachID, JObject requestBodyData)
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
DatabaseFunctions databaseFunctions = new DatabaseFunctions();
//newCoach.workload will be 0 if the requestbody contains no "workload" parameter,
//in which case [400 Bad Request] is returned.
if (requestBodyData["workload"] == null)
{
log.LogError("Requestbody contains no workload.");
return(exceptionHandler.BadRequest(log));
}
Coach newCoach = requestBodyData.ToObject <Coach>();
string queryString = $@"UPDATE [dbo].[Coach]
SET workload = @workload
WHERE studentID = @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 {
//Update the workload
//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
/* PREVIOUSLY: */
//command.Parameters.Add("@workload", SqlDbType.Int).Value = newCoach.workload;
//command.Parameters.Add("@coachID", SqlDbType.Int).Value = coachID;
/* CHANGED to: due to consistency and scalability */
dynamic dObject = newCoach;
databaseFunctions.AddSqlInjection(requestBodyData, dObject, command);
log.LogInformation($"Executing the following query: {queryString}");
int affectedRows = command.ExecuteNonQuery();
//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.ServiceUnavailable(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.BadRequest(log));
}
log.LogInformation($"{HttpStatusCode.NoContent} | Data updated succesfully.");
//Return response code [204 NoContent].
return(new HttpResponseMessage(HttpStatusCode.NoContent));
}
/*Returns */
public async Task <HttpResponseMessage> Login()
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
UserAuth userAuth;
JObject jObject;
/* Read from the requestBody */
using (StringReader reader = new StringReader(await req.Content.ReadAsStringAsync()))
{
jObject = JsonConvert.DeserializeObject <JObject>(reader.ReadToEnd());
userAuth = jObject.ToObject <UserAuth>();
}
/* Verify if all parameters for the Auth table exist.
* One or more parameters may be missing, in which case a [400 Bad Request] is returned. */
if (jObject["studentID"] == null || jObject["password"] == null)
{
log.LogError("Requestbody is missing data for the Auth table!");
return(exceptionHandler.BadRequest(log));
}
/* Create query for selecting data from the database */
string queryString = $@"SELECT part1, part2 FROM [dbo].[Auth] 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
{
//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
command.Parameters.Add("@studentID", System.Data.SqlDbType.Int).Value = userAuth.studentID;
log.LogInformation($"Executing the following query: {queryString}");
using (SqlDataReader reader = command.ExecuteReader())
{
/* If the student does not exist, it returns a notFoundException */
/* Return status code 404 */
while (reader.Read())
{
//part1 = salt, part2 = hash
userAuth.salt = reader.GetString(0);
userAuth.hash = reader.GetString(1);
}
}
}
}
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.");
HttpResponseMessage response;
if (userAuth.hash == encryptPassword(userAuth).hash)
{
//Return response code [201 Created].
response = new HttpResponseMessage(HttpStatusCode.OK);
try
{
response.Content = new StringContent(leaseToken(userAuth.studentID.ToString()));
}
catch (Exception e)
{
log.LogError("Somthing went wrong within the token system");
log.LogError(e.Message);
}
return(response);
}
//Return response code [400 BadRequest].
response = new HttpResponseMessage(HttpStatusCode.BadRequest);
log.LogInformation("test4");
return(response);
}
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")
});
}
/*Returns */
public async Task <HttpResponseMessage> CreateAuth()
{
ExceptionHandler exceptionHandler = new ExceptionHandler(log);
UserAuth userAuth;
JObject jObject;
/* Read from the requestBody */
using (StringReader reader = new StringReader(await req.Content.ReadAsStringAsync()))
{
jObject = JsonConvert.DeserializeObject <JObject>(reader.ReadToEnd());
userAuth = jObject.ToObject <UserAuth>();
}
/* Verify if all parameters for the Auth table exist.
* One or more parameters may be missing, in which case a [400 Bad Request] is returned. */
if (jObject["studentID"] == null || jObject["password"] == null)
{
log.LogError("Requestbody is missing data for the Auth table!");
return(exceptionHandler.BadRequest(log));
}
/* ******** To do ******************
* Check if the ID is already in the db
* Check password length and stuff
* Encrypt password (make function for)
* ********************************* */
//encrypt password
userAuth = encryptPassword(userAuth);
/* Create query for setting the data into the database */
string queryString = $@"INSERT INTO [dbo].[Auth] (studentID, part1, part2)
VALUES (@studentID, @salt, @hash);";
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
command.Parameters.Add("@studentID", System.Data.SqlDbType.Int).Value = userAuth.studentID;
command.Parameters.Add("@salt", System.Data.SqlDbType.VarChar).Value = userAuth.salt;
command.Parameters.Add("@hash", System.Data.SqlDbType.VarChar).Value = userAuth.hash;
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].
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
try
{
response.Content = new StringContent(leaseToken(userAuth.studentID.ToString()));
}
catch (Exception e)
{
log.LogError("Somthing went wrong within the token system");
log.LogError(e.Message);
}
return(response);
}
/*
* 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")
});
}
