RESTful Web Services Tutorial 7 – Working With Databases (Insert Country using DBContext)

Tutorial 7 - Accessing Database with Entity Framework

Hello! Good to see you, I really appreciate your effort in learning. Thumbs up to you!๐Ÿ˜ƒ๐Ÿ˜ƒ๐Ÿ˜ƒ๐Ÿ˜ƒ

In this tutorial, we are going to access the database we created in Tutorial 6 and Tutorial 7 with the help of Entity Framework. Entity Framework(EF) allows us to sync ourย  .Net application with the database and make changes from the .Net code.

We would cover the following in this tutorial

  1. Review of Tutorial 5 and Tutorial 6
  2. Examine the Changes to the Application Structure
  3. Modify the Controller File
  4. Write a Post Method for Country
  5. Insert some Countries Using the POST Method
  6. PUT Method
  7. DELETE Method
  8. Test all the Operations
  9. Next Steps


1. Review of Tutorial 5 and Tutorial 6

In Tutorial 5, weย  installed SQL Server and then used SQL Server Management Studio to create the Employees sample database we would use to store the employees data. We also created the relationships between the tables using database diagrams.

In Tutorial 6, we went back to Visual Studio and created the Entity Data Model using Entity Framework. This makes is possible to automatically sync our .Net code with out database without having to write any stored procedure or any SQL queries.


2. Examine the Changes to the Application Structure

You need to understand what changes took place when you created the Entity Data Model

In the Solution Explorer, find EmployeeDataModel.edmx
(The edmx file is a container for both the designer/class diagrams and the models, the .cs files that hold the classes)
Expand this file to display the content. The folder structure is as shown in Figure 1


Figure 1: EntityDataModel.edmx


Notice that the Models have been created automatically for

  • Employee
  • Country
  • Address
  • Department

In Tutorial 3, we created the model file (Employee.cs) manually inside the Models folder, but in this case, the model files have been generated automatically from an existing database. This is known as Entity Framework – Database First.


3. Create the Controllers

Expand the Controller folder. Notice that we have already one controller file, the EmployeesController
You now need to create the controllers for Country
Right-click on the Controllers folder and Choose Add > Controller
Choose Web 2 API Controller Empty and shown in Figure 2


Figure 2: Web 2 API Controller Empty


Give it a name CountryController
Click Add. So the CountryController is added



4. Write a POST Method for Country

We would write a POST method that would insert a record into the database using the data from the body of the HTTP POST request.
Open the Country Controller file you created
Copy and paste the code in Listing 7.1 inside the class


public IHttpActionResult AddCountry([FromBody]Country country)
   using (var db = new EmployeeDBEntities())
      db.Countries.Add(new Country() 
         Id = country.Id,
         Name = country.Name,
         Capital = country.Capital,
         Code = country.Code,
         Nationality = country.Nationality
   return Created(Request.RequestUri, Request.Content);

Listing 7.1: POST Method for Country


The code is quite simple. The EmployeeDBEntities is known as a database context and provides functionality to handle database operations like Add(), Remove(), Delete() and SaveChanges().

In this case, we got a Country object from the body of the request. Then extracted the fieldsย  and used them to create a new Country which is given to the Add() method as parameter. The Add method runs some kind of insert query against the database.
The SaveChanges() commits the query.

Before we test this code, write a code to retreive a list of countries. This is given in Listing 7.2.
Copy and paste this code into the CountryController.


public IEnumerable<Country> getCountries()
   var db = new EmployeeDBEntities();
   return db.Countries;

Listing 7.2: Code to Get List of Countries


So now you have the POST method to Add a new Country as well as a GET method to retrieve the list of countries
Build the project and make sure it builds successfully.


5. Insert some Countries Using the POST Method

Run the program.
Open Advanced REST Client
Make a POST request using the code in Listing 7.3 as the request body. You can see how to make POST request with ARC in Tutorial 4.

POST Request url: http://localhost:24675/api/Country/getCountries


"Id": 1,
"Code": "NGN",
"Name": "Nigeria",
"Capital": "Abuja",
"Nationality": "Nigerian"

Listing 7.3: POST Request Body


Change the data to insert two more Country records
Make a GET request to retrive a list of countries

GET Request url: http://localhost:24675/api/Country/AddCountry



6. PUT Method to Update a Record

Listing 7.4 gives the PUT method to edit and update a country from the database.
Copy and paste this code in the CountryController file.
I have used lambda expression to select a country whose id corresponds to the id coming from the request body.

The where clause in the lambda expression reads:

“where s such that s.Id is equal to”


public IHttpActionResult EditCountry([FromBody]Country country)
   using (var db = new EmployeeDBEntities())
       var oldCountry = db.Countries
       .Where(s => s.Id == country.Id)
       oldCountry.Name = country.Name;
       oldCountry.Capital = country.Capital;
       oldCountry.Code = country.Code;
       oldCountry.Nationality = country.Nationality;

   return Ok();

Listing 7.4: PUT Method to Update a Country

7. DELETE Method to Delete a Record

The DELETE method is given in Listing 7.5 to delete a record from the database.
Copy and paste this code in the CountryController file

Notice the four lines needed to delete a record

  • the Attach method attached theย  entity (coming from the request) to the database context
  • the Remove method carries out the delete operation
  • the next line updates the context
  • the SaveChanges commits the delete operation


public IHttpActionResult DeleteCountry([FromBody]Country country)
    using (var db = new EmployeeDBEntities())
        db.Entry(country).State = EntityState.Deleted;
    return Ok();

Listing 7.5: DELETE Method to Delete a Country



8. Test all the CRUD Operations

At this point, you can carry out INSERT, SELECT, UPDATE and DELETE operation on the Countries database using the CountryController.
Run the program
Using Advanced REST Client, insert some recordsย  into the database. Remember to change the Id for each new insert because the insert would fail if the Id is not unique
Perform some PUT and DELETE requests to make sure it works well

If everything works fine, then congratulations! If you have some challenges, let me know in the comment box below.


9. Next Steps

We have successfully completed the REST API for manipulation the Countries information. Now, as a quiz, try to create controllers for the other three tables:

  • Employees
  • Departments
  • Addresses

If you did it correctly, fine. In the next lesson, Tutorial 8, I would give you the code for the other tables without giving much explanation. For now, try to do it yourself.

I would like to thank you for learning!!