Introduction

This article will demonstrate how to implement ASP.NET WEB API CRUD logics using Entity Framework without writing code and how to work with Postman to validate Web API services.

Prerequisites
  • Visual Studio
  • SQL Management Studio
  • Basics of C#
  • Basics of SQL Server
  • Basics of ASP.NET WEB API
  • Basics of Entity Framework
Article Flow
  • Create Table in SQL Server
  • Create ASP.NET WEB API Empty Project
  • Install Entity Framework from NuGet
  • Implement "Database First" approach with Entity Framework
  • Implement WEB PAI scaffolding with Entity Framework
  • HTTP Error 403.14 - Forbidden
  • Enabling Cross-Origin Requests in ASP.NET Web API 2
  • Validate Services using Postman

Create Table in SQL Server


First, we will create a table in SQL Server to implement the CRUD logic in ASP.NET WEB API. I have designed the below table.


Execute the below query to create a table with the above design.
  1. CREATE TABLE [dbo].[Employee](
  2. [ID] [bigint] IDENTITY(1,1) NOT NULL,
  3. [Name] [nvarchar](max) NULL,
  4. [Designation] [nvarchar](200) NULL,
  5. [Location] [nvarchar](200) NULL,
  6. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  7. (
  8. [ID] ASC
  9. ))
  10. GO
And, insert a few dummy records to view in Postmen. For that, execute the below query.
  1. GO
  2. SET IDENTITY_INSERT [dbo].[Employee] ON
  3. GO
  4. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (1, N'Gnanavel Sekar', N'Software Engineer', N'Chennai')
  5. GO
  6. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (3, N'Robert', N'Application Developer', N'Chennai')
  7. GO
  8. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (4, N'Ramar', N'TechLead', N'Chennai')
  9. GO
  10. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (10002, N'Subash', N'Software Engineer', N'Coimbatore')
  11. GO
  12. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (10003, N'Gokul', N'Team Lead', N'USA')
  13. GO
  14. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (10004, N'Karthi', N'Sr. Software Engineer', N'Coimbatore')
  15. GO
  16. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (10005, N'Sharma', N'Software Engineer', N'Banglore')
  17. GO
  18. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (10006, N'Ammaiyappan', N'Software Developer', N'Chennai')
  19. GO
  20. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (10007, N'Manoj', N'Sr.Software Engineer', N'Chennai')
  21. GO
  22. INSERT [dbo].[Employee] ([ID], [Name], [Designation], [Location]) VALUES (10008, N'Mr.Blue', N'Sr.Software Engineer', N'Coimbatore')
  23. GO
  24. SET IDENTITY_INSERT [dbo].[Employee] OFF
  25. GO
Once you execute the above query, you will get the employee record as below.


Create ASP.NET WEB API Empty Project


To create ASP.NET WEB API empty project, follow the below steps one by one. Here, I have used Visual Studio 2013.

Step 1 Select Language and Name Your Project

Select New Project -> Visual C# -> Web -> ASP.NET Web Application and enter your application name. Here, I named it "WEB-API-CRUD-Logics". Now, click OK.


Step 2

Select Empty Web API template and click OK to create the project.


Step 3

Once you click OK, the project will be created with the basic architecture of ASP.NET WEB API


Install Entity Framework From NuGet


To install Entity Framework from NuGet, right click on References and select Manage NuGet Packages.


And now, search EntityFramework online to install and add the reference to your project.


After installing the EntityFramework from NuGet, it will be added as a reference to our project.


Implement Database First Approach with Entity Framework


Before implementing the database first approach, we should know what the database first approach is.

Database First Approach

It creates model codes such as classes, properties, and DbContext etc. from the database in the project and all classes become the links between the database and Controller. It always deals with the existing database. Now, we will move to the steps to implement the database first approach.

Step1

Here, I am going to create the .edmx file under the model's folder. So, right click on Models folder and select Add->ADO.NET Entity Data
model

Step 2

Select Data->ADO.NET Entity Data Model-> name your edmx file (here, I mentioned as CRUDLogics.edmx), and click Add.


If you are working with a higher version than Visual Studio 2013, follow the below screen.


Step 4 Configure the connection to SQL server

To create a connection, click New Connection and select Microsoft SQL Server ->Continue.


Step 6

In the below image, you can see that the connection was successfully created.


Step 7

Now, select the table(s) which are needed to be configured with our application through Entity Framework and click Finish.


Step 8

Now, you can see in the below image that the EntityModels are configured with Classes, Properties, DataContext and etc. If you have more than one table, you can easily see the tables with relationships in a .exmx diagram. Here, we have one table only, so there is no possibility to see that.


Implement WEB API scaffolding with Entity Framework


What is Scaffolding?

This option is enabled from Visual Studio 2013 for MVC and Web-API. Scaffolding generates the code automatically for CRUD operations, here we will use the WEB API scaffolding with Entity Framework to create Dynamic code for doing create, read, update, and delete. We will see one by one of the following steps for better understanding.

Step 1 Create Controller

Right click on Controllers folder under your project-> select Add-> Controller


Step 2

To implement scaffolding Select "Web API 2 Controller with actions, using Entity Framework" and click add


Step 3

Name the Controller->Select the Models->Select Data context->and click add


You will fall into the below error if you did not rebuild your project after completion database first approach,


So now rebuilding the project and repeat the steps of creating controller and scaffolding.

After completion of step 3 by clicking the Add button, it will start the Scaffolding process


Step 4

In the below image you can see that CRUDController is created with Get, Put, Post, Delete, Dispose and IsExists Action method with Logics.


Generated Codes
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.Entity;
  5. using System.Data.Entity.Infrastructure;
  6. using System.Linq;
  7. using System.Net;
  8. using System.Net.Http;
  9. using System.Web.Http;
  10. using System.Web.Http.Description;
  11. using WEB_API_CRUD_Logics.Models;
  12. namespace WEB_API_CRUD_Logics.Controllers {
  13. public class CRUDController: ApiController {
  14. private CSharpCornerEntities db = new CSharpCornerEntities();
  15. // GET api/CRUD
  16. public IQueryable < Employee > GetEmployees() {
  17. return db.Employees;
  18. }
  19. // GET api/CRUD/5
  20. [ResponseType(typeof(Employee))]
  21. public IHttpActionResult GetEmployee(long id) {
  22. Employee employee = db.Employees.Find(id);
  23. if (employee == null) {
  24. return NotFound();
  25. }
  26. return Ok(employee);
  27. }
  28. // PUT api/CRUD/5
  29. public IHttpActionResult PutEmployee(long id, Employee employee) {
  30. if (!ModelState.IsValid) {
  31. return BadRequest(ModelState);
  32. }
  33. if (id != employee.ID) {
  34. return BadRequest();
  35. }
  36. db.Entry(employee).State = EntityState.Modified;
  37. try {
  38. db.SaveChanges();
  39. } catch (DbUpdateConcurrencyException) {
  40. if (!EmployeeExists(id)) {
  41. return NotFound();
  42. } else {
  43. throw;
  44. }
  45. }
  46. return StatusCode(HttpStatusCode.NoContent);
  47. }
  48. // POST api/CRUD
  49. [ResponseType(typeof(Employee))]
  50. public IHttpActionResult PostEmployee(Employee employee) {
  51. if (!ModelState.IsValid) {
  52. return BadRequest(ModelState);
  53. }
  54. db.Employees.Add(employee);
  55. db.SaveChanges();
  56. return CreatedAtRoute("DefaultApi", new {
  57. id = employee.ID
  58. }, employee);
  59. }
  60. // DELETE api/CRUD/5
  61. [ResponseType(typeof(Employee))]
  62. public IHttpActionResult DeleteEmployee(long id) {
  63. Employee employee = db.Employees.Find(id);
  64. if (employee == null) {
  65. return NotFound();
  66. }
  67. db.Employees.Remove(employee);
  68. db.SaveChanges();
  69. return Ok(employee);
  70. }
  71. protected override void Dispose(bool disposing) {
  72. if (disposing) {
  73. db.Dispose();
  74. }
  75. base.Dispose(disposing);
  76. }
  77. private bool EmployeeExists(long id) {
  78. return db.Employees.Count(e => e.ID == id) > 0;
  79. }
  80. }
  81. }
Now Rebuild and Run your application, we might get the below error if we did not enable directory browse in IIS or in our application


HTTP Error 403.14 - Forbidden


We can overcome this HTTP Error 403.14- Forbidden in two ways
  • Enable directoryBrowse in WebCofing system.webServer
  • Enable in IIS
Enable directoryBrowse in WebCofing system.webServer

Goto your Web API project->Open the WebConfig file and add the below code
  1. <system.webServer>
  2. <directoryBrowse enabled="true" />
  3. </system.webServer>
Enable in IIS

Refer this Link

Now Run your application, you will get the below error if you are using the Web API template first time or Visual Studio 2013
Could not load file or assembly 'System.Web.Http, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

To overcome this issue update or install below dll from NuGet package
  • Microsoft.AspNet.WebApi

Enabling Cross-Origin Requests in ASP.NET Web API 2


Cross-Origin Resource Sharing (CORS) is a W3C standard that allows a server to relax the same-origin policy. Using CORS, a server can explicitly allow some cross-origin requests while rejecting others. CORS is safer and more flexible than earlier techniques such as JSONP
  • Install below dll from NuGet package
  • Microsoft.AspNet.WebApi.Cors
And add below code to your WebApiConfig.cs file
  1. config.EnableCors(new EnableCorsAttribute("*", "*", "GET,PUT,POST,DELETE"));
Complete code of WebApiConfig.cs
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web.Http;
  5. using System.Web.Http.Cors;
  6. namespace WEB_API_CRUD_Logics {
  7. public static class WebApiConfig {
  8. public static void Register(HttpConfiguration config) {
  9. // Web API configuration and services
  10. // Web API routes
  11. config.MapHttpAttributeRoutes();
  12. config.Routes.MapHttpRoute(name: "DefaultApi", routeTemplate: "api/{controller}/{id}", defaults: new {
  13. id = RouteParameter.Optional
  14. });
  15. config.EnableCors(new EnableCorsAttribute("*", "*", "GET,PUT,POST,DELETE"));
  16. }
  17. }
  18. }
Now run your application, Yes! We got the successful services. We will validate all actions(CRUD) using postman
Attribute Routing

Attribute routing can be thought of as convention based routing. It means the developer can change the route's default pattern to a custom way. Attribute routing is used on the top of an action, controller name in WebAPI and MVC. Here we will see the action based attribute routing. In the below code, you can see that I have created attribute routing on top of the action.

Example [Route("api/GetEmployees")] to get all employees.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.Entity;
  5. using System.Data.Entity.Infrastructure;
  6. using System.Linq;
  7. using System.Net;
  8. using System.Net.Http;
  9. using System.Web.Http;
  10. using System.Web.Http.Description;
  11. using WEB_API_CRUD_Logics.Models;
  12. namespace WEB_API_CRUD_Logics.Controllers {
  13. public class CRUDController: ApiController {
  14. private CSharpCornerEntities db = new CSharpCornerEntities();
  15. // GET api/CRUD
  16. [ResponseType(typeof(IEnumerable < Employee > ))]
  17. [Route("api/GetEmployees")]
  18. public IQueryable < Employee > GetEmployees() {
  19. return db.Employees;
  20. }
  21. // GET api/CRUD/5
  22. [ResponseType(typeof(Employee))]
  23. [Route("api/GetEmployee")]
  24. public IHttpActionResult GetEmployee(long id) {
  25. Employee employee = db.Employees.Find(id);
  26. if (employee == null) {
  27. return NotFound();
  28. }
  29. return Ok(employee);
  30. }
  31. // PUT api/CRUD/5
  32. [Route("api/PutEmployee")]
  33. public IHttpActionResult PutEmployee(long id, Employee employee) {
  34. if (!ModelState.IsValid) {
  35. return BadRequest(ModelState);
  36. }
  37. if (id != employee.ID) {
  38. return BadRequest();
  39. }
  40. db.Entry(employee).State = EntityState.Modified;
  41. try {
  42. db.SaveChanges();
  43. } catch (DbUpdateConcurrencyException) {
  44. if (!EmployeeExists(id)) {
  45. return NotFound();
  46. } else {
  47. throw;
  48. }
  49. }
  50. return StatusCode(HttpStatusCode.NoContent);
  51. }
  52. // POST api/CRUD
  53. [Route("api/PostEmployee")]
  54. [ResponseType(typeof(Employee))]
  55. public IHttpActionResult PostEmployee(Employee employee) {
  56. if (!ModelState.IsValid) {
  57. return BadRequest(ModelState);
  58. }
  59. db.Employees.Add(employee);
  60. db.SaveChanges();
  61. return CreatedAtRoute("DefaultApi", new {
  62. id = employee.ID
  63. }, employee);
  64. }
  65. // DELETE api/CRUD/5
  66. [Route("api/DeleteEmployee")]
  67. [ResponseType(typeof(Employee))]
  68. public IHttpActionResult DeleteEmployee(long id) {
  69. Employee employee = db.Employees.Find(id);
  70. if (employee == null) {
  71. return NotFound();
  72. }
  73. db.Employees.Remove(employee);
  74. db.SaveChanges();
  75. return Ok(employee);
  76. }
  77. protected override void Dispose(bool disposing) {
  78. if (disposing) {
  79. db.Dispose();
  80. }
  81. base.Dispose(disposing);
  82. }
  83. [Route("api/EmployeeExists")]
  84. private bool EmployeeExists(long id) {
  85. return db.Employees.Count(e => e.ID == id) > 0;
  86. }
  87. }
  88. }
Enable Postman

Postman is a free API debugging tool. Install it for Chrome from Here. Or visit Here for others


After successful installation, open it and select HTTP Method and enter the URL of your Web API as shown below.


API URL: http://localhost:54259/

Get Employee List Routing: api/GetEmployees


After Clicked the send button it will make a request to server


And the Result will be


Now we will see how to pass the parameter, for that we will use the api/GetEmployee.


Yeah! we successfully made a WEB API services for CRUD operation. Now the client application can perform the CRUD operation by calling these APIs. In another article, I discussed how to make a standard form of services, the easiest way to make documentation and validate the API using Swagger can be found Here.

Summary

In this article, you learned to implement CRUD services in ASP.NET WEB API using Entity Framework without writing code and how to use the Postman and also validate the API Services.

I hope it's helpful, your valuable feedback and comments about this article are always welcome.