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