/* 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")
});
}
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 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")
});
}
// 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")
});
}
/* 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")
});
}