Posted on 5/14/2020 12:02:58 PM by Admin

Implementing CRUD

In this article ,we will learn how to implement the CRUD operations in Asp.Net Web API. We will also check whether we are following Rest principles or not?

  • C:- Create a resource on the server. (Http Post)
  • R:- Read or retrieve a resource from server. (Http Get)
  • U:- Update a resource on server.(Http Put)
  • D:- Delete a resource on server. (Http Delete)

To explain all these operations , I am going to use SQL Server as the data Source and Entity Framework ,the ORM which is responsible to fire the respective SQL script behind the scene. You are free to use raw Ado.Net to interact with the database.

Step 1.Create a Database and a Table.

Execute the following SQL script using SQL Server Management Studio. The script creates:-
  1. Test Database.
  2. Employee table under Test Database.


  Create Database Test
   Go

  Use Test
  Go

  CREATE TABLE [dbo].[Employee] (
    [Id]     INT          IDENTITY (1, 1) NOT NULL,
    [Name]   VARCHAR (50) NULL,
    [Salary] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC)
   );
  Go

  SET IDENTITY_INSERT [dbo].[Employee] ON
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (1, N'sachin', 100000)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (2, N'arjun', 23000)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (3, N'vikash', 30000)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (4, N'pocha', 43000)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (5, N'Nivedita', 24000)
  SET IDENTITY_INSERT [dbo].[Employee] OFF
  Go

Step 2.Create a new Asp.Net Web API project.
  • Open Visual Studio , select File--New--Project.

Create Web API Project Step 1
Creating web api project (1)

  • Under installed Template select " Visual C#" and from the middle pane select "Asp.Net Web Application", name the project "MyFirstAPIProject" and Click Ok.
Create Web API Project Step 1.1
Creating web api project (1.1)

  • On the next window, select "Empty template" and check "MVC" checkbox. And Click Ok.
Create Web API Project Step 1.3
Creating web api project (1.3)

  At this point, we have an Empty Web API project created.

Step 3.Add Ado.Net Entity Data Model .

If you have ever worked in any professional web project ,then you may be aware of the term n-tier architecture. Here ,I am not going to discuss the n-tier architecture ,but recommend you to always generate your Entity Data Model in separate Layer ,as this Layer will be responsible for interacting with your database ,so you can call it as Data Access Layer or DAL.

  • Right click on "MyFirstAPIProject" solution in the Solution Explorer and select Add - New Project.

Create Web API Project Step 2
Creating web api project (2.1)

  • In the Add New Project window ,Select Visual C# from the left pane and Class Library Project from the Middle pane and Name the project DAL and click OK .
Create Web API Project Step 2.2
Creating web api project (2.2)

  • Right click on the DAL project and select Add--New Item.
  • In the "Add New Item" window ,Select "Data" from the left pane, Select ADO.NET Entity Data Model from the middle pane, In the Name text box, type TestModel and click Add.
Create Web API Project Step 2.3
Creating web api project (2.3)

  • On the Entity Data Model Wizard, select "EF Designer from database" option and click next.
Create Web API Project Step 2.4
Creating web api project (2.4)
Create Web API Project Step 2.4.1
Creating web api project (2.4.1)

  • On "Connection Properties" window, set Server Name = (your server name), Authentication = Windows Authentication, Select or enter a database name = Test, Click OK .
  • In the next window , change the TestEntity to TestDbContext and click next.
  • On the next screen, select "Employee" table and click Finish.
Create Web API Project Step 2.5
Creating web api project (2.5)

Step 4. Give reference of DAL to MyFirstAPIProject (web Api project).
  • Right click on the references folder in the MyFirstAPIProject project and select "Add Reference".
  On the "Reference Manager" screen select "DAL" project and click OK.

Create Web API Project Step 4.1
Creating web api project (3.1)

Step 5.Create a controller named Employee Controller. Now, We are ready to write Code for each of the CRUD methods.

Implement Get Method


      public IEnumerable GetAllEmployee()
        {
            using(TestDBContext db=new TestDBContext()){
                return db.Employees.ToList();
            }
        }

       public Employee GetEmployeeById(int id)
        {
            using (TestDBContext db = new TestDBContext())
            {
                return db.Employees.FirstOrDefault(e => e.Id == id);
            }
        }

Step 4.Run the application ,and Enter the URL:-
1. http://localhost:portNo/api/employee
It will return the below representation.

Create Empty Web API project
XML representation for all employee

2. http://localhost:portNo/api/employee/4
It will return the below represntation.

Create Empty Web API project
XML representation for particular employee

So, we have successfully Implemented the Get method

Now Let's dive deep and check whether have we followed the rest principles or not.

1.Everything is resource.(No need to verify ,yes everything is a resource. here EmployeeList and single employee both are resource on server).

2.Each resource is uniquely identifiable.(verified)
For List of Employee we have :- http://localhost:portNo/api/employee
For single Employee we have :- http://localhost:portNo/api/employee/1

3.Interface must be simple.(yes ,verified.)
as our method names are starting with Get prefix so the URL are simple. And request is mapping with appropriate action method .The question is how will we achieve simpler interface when method names do not start with http verb.

4.Stateless(verified, over http ,no doubt)

5.Response must be a representation.(yes, verified)
we are getting XML representation back. But how???? who is doing that. we solved the puzzle in previous article.

Is everything good? yes, almost. We are following rest principles obediently. But almost doesn't mean everything.

Let's see what are we missing.

case 1.what if List of employee gets null.(we must return http status code 204 (No content) with no employee found message)

case 2.what if any run time error occurs.(we must return 500 internal server error).

case 3.what if there is no employee of specified Id exist.( we must return 204 status code, with no employee with specified Id exist message. )

In short ,No we are not following rest principles completely. Rest means respect the http protocol and utilize it as much as you can.

Lets modify the above code to get the desired outcome and fulfill the rest principles completely.


    public HttpResponseMessage GetAllEmployee()
        {
            try
            {
                using (TestDBContext db = new TestDBContext())
            {
                var Employees =db.Employees.ToList();
                if(Employees!=null){
                     return Request.CreateResponse(HttpStatusCode.OK, Employees);
                }
               return Request.CreateErrorResponse(HttpStatusCode.NotFound,"No result found");
           }
            }
            catch{
                return new HttpResponseMessage(HttpStatusCode.InternalServerError);
            }
           
        }

        public HttpResponseMessage GetAllEmployeeById(int Id)
        {
            try
            {	
           using (TestDBContext db = new TestDBContext())
            {

                 Employee emp = db.Employees.Where(x => x.Id == Id).SingleOrDefault();
                if(emp!=null){
                    return Request.CreateResponse(HttpStatusCode.OK,emp);
                }
                return Request.CreateErrorResponse(HttpStatusCode.NotFound,"Employee with id " +Id+ "Not found");
              
            }
          }
            catch
            {
                return new HttpResponseMessage(HttpStatusCode.InternalServerError);
            }
           
           
        }

Now ,Run the application and request employee who doesn't exist like employee with id 5. you will get below representation.

Create Empty Web API project
No Result found Error representation

Now Everything is fine. Hurrah ,you have learnt how to create Get services with Web API.

Now onwards ,I am not going to discuss the rest principles every time. But I am giving you a mantra(Tip).While creating any method in web API ,only keep in mind one thing, that is you have to respect the http. Respecting Http means keep the method names simpler or use Http verbs as attribute over action methods and utilize the features of http like setting the useful response headers like http status code, Content-Type, Location of newly created response etc.

Implementing Post Request


    public void Post([FromBody] Employee employee)
        {
            using(TestDBContext db=new TestDBContext()){
                db.Employees.Add(employee);
                db.SaveChanges();
            }
        }

Note:- The Employee object is being passed as parameter to the Post method. The Employee parameter is decorated with [FromBody] attribute. This tells Web API to get employee data from the request body.

At this point build the solution. Fireup Fiddler and issue a Post request
  1. Set the HTTP verb to POST
  2. Content-Type: application/json. This tells that we are sending JSON formatted data to the server
  3. In the Request Body, include the employee object that you want to add to the database in JSON format
  4. Finally click execute.

Issuing Post Request with fiddler
Issuing Post Request with fiddler

This works fine and adds the employee to the database as expected. But, Do you think everything is fine ?Remember what I had pointed out in get implementation, respect the http. So, what are the problems?
  • Since the return type of the Post() method is void, we get status code of 204 No Content. When a new item is created, we should actually be returning status code 201 Item Created.
  • With 201 status code we should also include the location i.e URI of the newly created item.

To achieve this, change the implementation of the Post() as shown below.


   public HttpResponseMessage Post([FromBody] Employee employee)
    {
      try
       {
           using (TestDBContext db = new TestDBContext())
        {
            db.Employees.Add(employee);
            db.SaveChanges();

            var message = Request.CreateResponse(HttpStatusCode.Created, employee);
            message.Headers.Location = new Uri(Request.RequestUri + "/"+
                employee.Id.ToString());

            return message;
        }
    }
    catch (Exception ex)
    {
        return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
    }
  }

At this point, issue another Post request from fiddler. Notice in the response header we have status code 201 Created and also the location i.e the URI of the newly created item.

Response Header of Post request
Response Header of Post Request

Implementing Put Method


  public void Put(int id, [FromBody]Employee employee)
    {
     using (TestDBContext db = new TestDBContext())
     {
         Employee emp = db.Employees.FirstOrDefault(e => e.Id == id);

         emp.Name = employee.Name;
         emp.Salary = employee.Salary;
         db.SaveChanges();
     }
    }

Note:- The id of the employee that we want to update and the Employee object with which we want to update are being passed as parameters to the Post method. The Employee parameter is decorated with [FromBody] attribute. This tells Web API to get employee data from the request body.

At this point build the solution. Fireup Fiddler and issue a Put request.
  1. Set the HTTP verb to PUT
  2. Content-Type: application/json. This tells that we are sending JSON formatted data to the server
  3. In the Request Body, include the employee object with which you want to update
  4. Finally click execute

Firing Put Request
Firing put request with fiddler

This works fine and updates the employee record in the database as expected. But again we have these problems.
  • Since the return type of the Put() method is void, we get status code of 204 No Content. When the update is successful, we should return status code 200 OK indicating that the update is successful.
  • when we try to update an employee whose Id does not exist we get back http status code 500 Internal Server Error. We get status code 500, because of a NULL reference exception. But, we should return 404 Not Found status code stating employee with such id is not found to update.

To fix both of these issues modify the code in Put() method as shown below.


   public HttpResponseMessage Put(int id, [FromBody]Employee employee)
    {
     try
     {
         using (TestDBContext db = new TestDBContext())
         {
             Employee emp = db.Employees.FirstOrDefault(e => e.Id == id);
             if (emp == null)
             {
                 return Request.CreateErrorResponse(HttpStatusCode.NotFound,
                     "Employee with Id " + id.ToString() + " not found to update");
             }
             else
             {
                 emp.Name = employee.Name;
                 emp.Salary = employee.Salary;
                 db.SaveChanges();
                 return Request.CreateResponse(HttpStatusCode.OK, emp);
             }
         }
     }
     catch (Exception ex)
     {
         return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
     }
   }

At this point, issue another Put request from fiddler. Notice in the response header we have status code 200 OK. Also, when we try to update an employee whose id does not exist, we get status code 404 Not Found instead of 500 Internal Server Error.

Firing Put Request
Response Header for Put Request

Implementing Delete Method


   public void Delete(int id)
    {
     using (TestDBContext db = new TestDBContext())
     {
         Employee empToDelete = db.Employees.FirstOrDefault(e => e.Id == id);
         db.Employees.Remove(empToDelete);
         db.SaveChanges();
     }
   }

There are 2 problems with the above code
  1. When the deletion is successful, since the method return type is void we get status code 204 No Content. We should be returning status code 200 OK.
  2. If the ID of the employee that we want to delete does not exist, there is an exception and as a result of it the Web API returns status code 500 internal server error. If an item is not found, we should be returning status code 404 Not Found.

To fix all the above problems, modify the code in Delete() method as shown below.


   public HttpResponseMessage Delete(int id)
   {
     try
     {
         using (TestDBContext db = new TestDBContext())
         {
             Employee emp = db.Employees.FirstOrDefault(e => e.Id == id);
             if (emp == null)
             {
                 return Request.CreateErrorResponse(HttpStatusCode.NotFound,
                     "Employee with Id = " + id.ToString() + " not found to delete");
             }
             else
             {
                 db.Employees.Remove(emp);
                 db.SaveChanges();
                 return Request.CreateResponse(HttpStatusCode.OK);
             }
         }
     }
     catch (Exception ex)
     {
         return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
     }
   }