Assignment #4: Tech Jobs (Persistent Edition)¶
You will once again work with the
TechJobs application. This time around you’ll add ORM
functionality by using Entity Framework. You will be responsible for completing the code to allow users
to create new job data.
Your final application will have the same list and search capabilities as your Tech Jobs (MVC Edition) but you’ll need to do the work to connect the project to a database for storing user-submitted job data.
Each of the three sections of this assignment will also ask you to demonstrate your SQL skills under an item labelled SQL TASK.
As you work through each part, refer to our demo app to clarify questions about intended application behavior.
Set up a local copy of the project:
In Canvas, Graded Assignment #4: TechJobs (Persistent Edition) contains a GitHub Classroom assignment invitation link and then set up the project in Visual Studio. Refer back to the GitHub Classroom instructions from Assignment #0: Hello, World! for details.
Launch the application to make sure it starts up properly. Then shut it down.
Run the autograding tests. The tests for this assignment are set up the same way as for assignment 2. There are four tasks for this assignment, but the first doesn’t require any coding on your part. Therefore, there are 3 tests files (for tasks 1-3). As with previous assignments, we recommend that you only run the tests for the task you are currently working on.
Checkout and Review the Starter Code¶
You will be able to run your application without any build errors. However, you’ll likely see a host of errors relating to the Entity Framework annotations and classes when you attempt to add any data or load different pages. Some of these have already been added but the connection to the database has not been set up yet. That will be one of your tasks. You’ll need to complete Part1 before you can thoroughly check out the project running in the browser.
That said, it’s a good idea to scan the classes and templates even before you’re able to run the application.
Take a gander at the
Job class. It will look somewhat similar to the model in
Tech Jobs (MVC Edition), with a few key differences.
You’re no longer using a csv file to load job data, instead, we’ll be creating new
Job objects via a
user form. The job data will be stored in a MySQL database that you’ll setup in Part 1 of this assignment.
As you explore the starter code, you’ll notice that the
JobField class is missing. You will focus on just two aspects of a job listing: the employer and the skills required.
Your task for Part 2 is to add code to let a user create an employer object.
Job class will also look different from how you have last seen it.
In Part 3, you will use the many-to-many relationship between skills and jobs to make it easier for users to add skills to new jobs.
In your ASP.NET project, you’ll see an empty file in the solution called
queries.sql. After completing the
C# updates for parts 1, 2, and 3, we ask you to test your application updates with SQL statements.
Since you are entering your own data, the queries we ask you to write will return unique result sets. For example, if you haven’t entered any data yet, there may be an empty result set. However, as the architect of the database, you have the knowledge to write the appropriate queries nonetheless.
Data directory, you will find
JobRepository. Instead of accessing
JobDbContext in your controllers, each controller will have a
repo object that you can use to access the repository which, in turn, accesses
Review each method to see what is returned so you can call the repository’s methods in the controllers as opposed to add a
This is a widely-accepted design pattern when making ASP.NET Core MVC apps called the Repository Pattern. The Microsoft Documentation has an article on the topic if you are interested in learning more.
Task 1: Connect a Database to an ASP.NET App¶
Start MySQL Workbench and create a new schema named
Remember to double click on the schema name in the file tree to make it the default schema.
In the administration tab, create a new user,
techjobswith the same settings as described in the lesson tutorial and set the password to
Make sure that
TechJobsPersistenthas all of the necessary dependencies.
Read through the code that is currently in
JobDbContextto get an idea for what the database will look like.
appsettings.jsonwith the right info. This will include the name of the database, as well as the username and password for a user you have created.
Startup.csincludes the configuration for the database.
You can double check your setup against what you’ve already done for your CodingEvents repo. You can copy these property assignments from your
CodingEventsrepo, only needing to change the database address and username/password values.
Run a new migration and update the database.
Testing: You may now run the tests found in
Build: You should be able to connect to your MySQL database and perform an initial migration. Your project should open a localhost browser window, but not much else. You need to add more to your project.
Test It with SQL¶
In your MySQL workbench, open a new query tab to check your database connection.
SQL TASK: At this point, you will have tables for
queries.sqlunder “Part 1”, list the columns and their data types in the
Before moving on to Task 2, run the autograding tests for Task 1. Once the tests pass, you are ready to go!
Your running application still has limited functionality. You won’t yet be able to add a job with the Add Job form. You also won’t yet be able to view the list of jobs or search for jobs - but this is mostly because you have no job data. Move on to Part 2 below to start adding these functionalities.
Part 2: Adding Employers¶
You will need to have completed the setup steps before starting this section.
Create a new ViewModel called
AddEmployerViewModelthat has 2 properties:
For the purposes of this application, an employer can only have one location.
Add validation to both properties in the ViewModel so that both properties are required.
EmployerController contains four relatively empty action methods. Take the following steps to handle traffic between the views and the model:
Set up a private
JobRepositoryvariable so you can perform CRUD operations on the database. Pass it into a
Index()so that it passes all of the
Employerobjects in the database to the view. For the purpose of this assignment, you will need to use
IEnumerable<Employers>in order for your code to interact with the
JobRepositoryand the interface
Create an instance of
AddEmployerViewModelinside of the
Add()method and pass the instance into the
Add the appropriate code to
ProcessAddEmployerForm()so that it will process form submissions and make sure that only valid
Employerobjects are being saved to the database.
About()currently returns a view with vital information about each employer such as their name and location. Make sure that the method is actually passing an
Employerobject to the view for display.
The starter code comes with 3 views in the
Read through the code in each view.
You may have to add models or make sure naming is consistent between the controller and the view.
Adding a Job¶
One important feature of your application is a form to add a new job.
Two action methods in
ProcessAddJobForm(), will work together to return the view that contains the form and handle form submission.
Home subdirectory in
Views, you will find an
AddJob.cshtml file which contains the beginning of the form.
Right now, there is only one field to the form and that is for the job’s name.
As you work on the application, you will add more fields to this form to add employer and skill info.
Create a new ViewModel called
AddJobViewModel. You will need properties for the job’s name, the selected employer’s ID, and a list of all employers as
This is different from the given ViewModel,
JobDetailViewModelhas properties for the selected employer’s info and the selected skill’s info.
AddJobViewModelhas properties for all of the employers and skills in the database. We need both ViewModels for the application. If you want to see more of
JobDetailViewModelin action, check out
AddJob()pass an instance of
AddJobViewModelto the view.
AddJob.cshtml, add a new
<div class="form-group">element to the form. Add the appropriate
<input>tags to the new
<div>element to create the form field to add employer information to the job. This field should be a dropdown menu with all of the employers in the database. In addition, add a link to the
<div>element to add new employers. This way, if a user doesn’t see the employer they are looking for, they can easily click on the link and add a new employer to the database.
ProcessAddJobForm(), you need to take in an instance of
AddJobViewModeland make sure that any validation conditions you want to add are met before creating a new
Jobobject and saving it to the database.
Testing: You can run the tests found in
Build: You may have a website at this point with more functionality, but it will not be complete. You should have data in your MySQL database.
Test It with SQL¶
Before you move on, test your application now to make sure it runs as expected. You should be able to create Employer objects and view them.
Open MySQL Workbench and make sure you have an
Employerstable and that it is empty.
Start up your application – don’t forget to have your SQL server running – and go to the Add Jobs view.
You won’t be able to add a job yet, but you’ll see a link to Add Employers in the form. Click on it and proceed to check the functionality of the form that follows.
Be sure to test your validation requirements and error handling.
SQL TASK: In
queries.sqlunder “Part 2”, write a query to list the names of the employers in St. Louis City.
If everything seems to work – that is, you are able to submit the form without any errors – but you don’t see your employers in the list after submission, here’s what you should check:
Is there any data in the
Employerstable? Check by going to MySQL Workbench and looking for the employer data within your schema.
If there’s data in the database, check that you are correctly querying for the list of all objects in the controller Are you calling for the proper list with
Ensure you’re passing the list into the view.
Un-comment and then run the tests in
TaskTwo to make sure that you have completed everything. When your tests pass, move on to Part 3 below.
Part 3: Working with a Many-To-Many Relationship¶
Using a many-to-many relationship, we can use the
JobSkill object to store a
Job object’s skills.
Just as a job requires many skills, any skill can be associated with several jobs.
With this in mind, the form to add a job needs to contain all of the skills available as checkboxes so users can add the necessary skills when they create a job.
Review Existing Code¶
Before diving into this section, make sure that you have read through all models, ViewModels, views, and
SkillController to see how the exisiting features and functions to add skills and add a skill to a job work.
In order to add additional functionality to the form for adding a job, we need to add properties to
Add a property for a list of each
Skillobject in the database.
Previously, in an
AddJobViewModelconstructor, you probably set up a
Employerinformation. Pass another parameter of a list of
Skillobjects. Set the
List<Skill>property equal to the parameter you have just passed in.
You next need to update
HomeController so that skills data is being shared with the form and that the user’s skill selections are properly handled.
AddJob()method, update the
AddJobViewModelobject so that you pass all of the
Skillobjects in the database to the constructor.
ProcessAddJobForm()method, pass in a new parameter: an array of strings called
selectedSkills. When we allow the user to select multiple checkboxes, the user’s selections are stored in a string array. The way we connect the checkboxes together is by giving the
nameattribute on the
<input>tag the name of the array. In this case, each
<input>tag on the form for the skills checkboxes should have
"selectedSkills"as the name.
After you add a new parameter, you want to set up a loop to go through each item in
selectedSkills. This loop should go right after you create a new
Jobobject and before you add that
Jobobject to the database.
Inside the loop, you will create a new
JobSkillobject with the newly-created
Jobobject. You will also need to parse each item in
selectedSkillsas an integer to use for
Add each new
JobSkillobject to the database using the appropriate
JobRepositorymethod, but do not add an additional call to
SaveChanges()inside the loop! One call at the end of the method is enough to get the updated info to the database.
Now that we have the controller and ViewModel set up, we need to update the form to add a job.
Add a new
<div class="form-group">element to the form for collecting skills.
Loop through each object in the list of
Give each checkbox a label and add the checkbox input itself. Here is an example of how that
<input>tag might look:
<input type="checkbox" name="selectedSkills" value="@skill.Id" />
Add a link to the form to add skills to the database so if a user doesn’t see the skills they need, they can add skills themselves!
Test It with SQL¶
Run your application and make sure you can create a new job with an employer and several skills. You should now also have restored full list and search capabilities.
SQL TASK: In
queries.sqlunder “Part 3”, write a query to return a list of the names and descriptions of all skills that are attached to jobs in alphabetical order. If a skill does not have a job listed, it should not be included in the results of this query.
You will need to make use of “is not null”.
When you are able to add new employers and skills and use those objects to create a new job, you’re ready to un-comment and run the tests in
If those tests pass, you are done! Congrats!
Testing: Run the tests found in
Build: You should have a working website and see data in MySQL database.