Assignment #4: Tech Jobs (Persistent Edition)¶
Your Task¶
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.
Getting Started¶
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.
The 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.
JobRepository
¶
In the 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 JobDbContext
.
Review each method to see what is returned so you can call the repository’s methods in the controllers as opposed to add a JobDbContext
object.
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
techjobs
.Tip
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,
techjobs
with the same settings as described in the lesson tutorial and set the password totechjobs
.Make sure that
TechJobsPersistent
has all of the necessary dependencies.Read through the code that is currently in
JobDbContext
to get an idea for what the database will look like.Update
appsettings.json
with the right info. This will include the name of the database, as well as the username and password for a user you have created.Check that
ConfigureServices
inStartup.cs
includes the configuration for the database.Tip
You can double check your setup against what you’ve already done for your CodingEvents repo. You can copy these property assignments from your
CodingEvents
repo, only needing to change the database address and username/password values.Run a new migration and update the database.
Progress Check
Testing: You may now run the tests found in TaskOne.Test
.
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
Jobs
,Employers
,Skills
, andJobSkills
. Inqueries.sql
under “Part 1”, list the columns and their data types in theJobs
table.
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.
ViewModels¶
Create a new ViewModel called
AddEmployerViewModel
that has 2 properties:Name
andLocation
.Note
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.
Controllers¶
EmployerController
contains four relatively empty action methods. Take the following steps to handle traffic between the views and the model:
Set up a private
JobRepository
variable so you can perform CRUD operations on the database. Pass it into aEmployerController
constructor.Complete
Index()
so that it passes all of theEmployer
objects in the database to the view. For the purpose of this assignment, you will need to useIEnumerable<Employers>
in order for your code to interact with theJobRepository
and the interfaceIJobRepository
.Create an instance of
AddEmployerViewModel
inside of theAdd()
method and pass the instance into theView()
return method.Add the appropriate code to
ProcessAddEmployerForm()
so that it will process form submissions and make sure that only validEmployer
objects 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 anEmployer
object to the view for display.
Views¶
The starter code comes with 3 views in the Employer
subdirectory.
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 HomeController
, AddJob()
and ProcessAddJobForm()
, will work together to return the view that contains the form and handle form submission.
In the 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 asSelectListItem
.Note
This is different from the given ViewModel,
JobDetailViewModel
.JobDetailViewModel
has properties for the selected employer’s info and the selected skill’s info.AddJobViewModel
has properties for all of the employers and skills in the database. We need both ViewModels for the application. If you want to see more ofJobDetailViewModel
in action, check outListController
andSearchController
.In
AddJob()
pass an instance ofAddJobViewModel
to the view.In
AddJob.cshtml
, add a new<div class="form-group">
element to the form. Add the appropriate<label>
and<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.In
ProcessAddJobForm()
, you need to take in an instance ofAddJobViewModel
and make sure that any validation conditions you want to add are met before creating a newJob
object and saving it to the database.
Progress Check
Testing: You can run the tests found in TaskTwo.Tests
folder.
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
Employers
table 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.sql
under “Part 2”, write a query to list the names of the employers in St. Louis City.
Tip
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
Employers
table? 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
DbContext
?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.
Updaing AddJobViewModel
¶
In order to add additional functionality to the form for adding a job, we need to add properties to AddJobViewModel
.
Add a property for a list of each
Skill
object in the database.Previously, in an
AddJobViewModel
constructor, you probably set up aSelectListItem
list ofEmployer
information. Pass another parameter of a list ofSkill
objects. Set theList<Skill>
property equal to the parameter you have just passed in.
Updating HomeController
¶
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.
In the
AddJob()
method, update theAddJobViewModel
object so that you pass all of theSkill
objects in the database to the constructor.In the
ProcessAddJobForm()
method, pass in a new parameter: an array of strings calledselectedSkills
. 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 thename
attribute 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 newJob
object and before you add thatJob
object to the database.Inside the loop, you will create a new
JobSkill
object with the newly-createdJob
object. You will also need to parse each item inselectedSkills
as an integer to use forSkillId
.Add each new
JobSkill
object to the database using the appropriateJobRepository
method, but do not add an additional call toSaveChanges()
inside the loop! One call at the end of the method is enough to get the updated info to the database.
Updating Home/AddJob.cshtml
¶
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
Skill
objects.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.sql
under “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.Tip
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 TaskThree
.
If those tests pass, you are done! Congrats!
Progress Check
Testing: Run the tests found in TaskThree.Tests
.
Build: You should have a working website and see data in MySQL database.
How to Submit¶
To turn in your assignment and get credit, follow the submission instructions.