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 the 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.
To create database and tables open SQL Server Management Studio (SSMS) and execute the following script. The script will create:-
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.
• Under installed Template select " Visual C#" and from the middle pane select "Asp.Net Web Application", name the project "MyFirstAPIProject" and Click Ok.
• On the next window, select "Empty template" and check "MVC" checkbox. And Click Ok.
At this point, we have an Empty Web API project created.
Step 3.Add Ado.Net Entity Data Model .
If you have ever worked on 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 always generate your Entity Data Model in a separate Layer, as this Layer will be responsible for interacting with your database, so you can call it a Data Access Layer or DAL.
• Right click on "MyFirstAPIProject" solution in the Solution Explorer and select Add - New Project.
• 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 .
• 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.
• On the next window that is on the Entity Data Model Wizard, select "EF Designer from database" option and click next.
• 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.
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.
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.
2. http://localhost:portNo/api/employee/4
It will return the below represntation.
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.
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. The [FormBody] attribute tells the 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. The content-type tells the web API that we are sending JSON formatted data to the server so that Web API could choose the right Media type Formatter
3. In the Request Body, include the employee object that you want to add to the database in JSON format
4. Finally click execute.
This works great and adds the employee's informatio 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 a status code of 204 No Content. but logically, When a new item is created, we should always return the status code 201 Item Created.
• If a new item is created on the server then, with 201 status code we should also include the location i.e URI of the newly created item, so that the client could know the URL of recently added resource.
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);
}
}
Now open fiddler and issue another Post request. Please notice the response header this time you will find have status code 201 Created and also the location i.e the URI of the newly created item.
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 the [FromBody] attribute. The [FromBody] attribute tells the Web API to get employee data from the request body and not from the query string.
At this point build the solution. Fire-up Fiddler and issue a Put request.
1. Set the HTTP verb to PUT
2. Content-Type: application/json. The Content-Type tells the web API that we are sending JSON formatted data to the server.
3. In the Request Body, place the employee object with which you want to update the Employee's data
4. Finally click on execute tab or button to issue the request.
This works fine and updates the related employee's record in the database as expected. But again we have these problems.
• Since the return type of the Put() method is void, we get a 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 of random Id means whose Id does not exist, then we get the HTTP status code 500 Internal Server Error. Also, We get the status code 500, because of the NULL reference exception. But, we should actually return the 404 Not Found status code stating an 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);
}
}
Now it is time to issue another Put request from fiddler. Please Notice that this time in the response header we have status code 200 OK. Also, notice that now when we are trying to update an employee whose id does not exist, we get status code 404 Not Found instead of 500 Internal Server Error.
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. It is not good to get an error from the server instead, we should be returning the status code 404 Not Found when the item is not found.
To fix all the above problems, we need to modify our code slightly, so Open the Delete() method and modify it 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);
}
}