public TutorantProfile(Tutorant tutorant, Student user)
{
this.tutorant = tutorant;
this.user = user;
}
// 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));
}