Implementing CRUD Operations using WEB API

code-adda web api crud

This article will help you to learn and understand how to Implementing CRUD Operations using WEB API. If you have not gone through my previous article I would like to suggest you please go through Creating your first WEB API Project ,you will get better understanding of “how to create and setup a web api project“. Just to make it simple and easy to understand we are going to use basic concept of ADO.Net in order to perform CRUD operations. There are various ways you can write your code. No worry! we will learn how to perform CRUD operations by using different ways of Entity Framework in later article. Now lets focus on basic first.

CRUD Stands for Create Read Update and Delete. The operations in this article will perform CRUD operation on SQL server database. You can download script and source code. Link is given at the end of article. To perform this task, We are going to use model which reflect the concept of repository pattern. In Model, we’ll have a class having all the table entities, One Interface defining CRUD operations and One class which will implement that Interface. One Controller where will have all actions Like GET, PUT, POST, DELETE.

Clients can access those operations in RESTful manner that is resource based. ASP.NET Web API framework will return response in XML format by default. We can change it as per our need like JSON. No worry! we are going to learn all these things in this article.

CRUD Operations using WEB API

Creating WEB API Application :

  • Open Visual Studio 2013/15/17 either it is Community version, professional version or enterprise version.
  • Click on File Menu
  • Click on New and then Click on Project menu.
  • This will open new window – New project, where you can select project type.
  • Expand Installed, Expand Language and select web.
  • Select .Net framework on which you are going to build your WEB API.
  • Select Template. Here I’ve selected ASP.NET Web Application(.Net Framework)
  • Enter Project Name. Here I’ve entered WEBAPI_CRUDOperations
  • Click on OK button.

Create Model Classes :

Why we need to create Model ?
Model is a class that maps to the data relation (table) and potentially bridge tables.  The model represents the data, and does nothing else. In short this is where you should keep your data model and logic’s to get and set data.

Create new folder with name Models  under root directory If it is not available in your project. Now create a new class “Employee“. You can name it as per your need.

To Create a new class, follow given steps.

  • Right click on Models
  • Click on Add
  • Click on Class and create a class name Employee
  • And write below code. You can change it as per your table structure.
namespace WEBAPI_CRUDOperations.Models
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string Designation { get; set; }
        public string Department { get; set; }
        public string EmailID { get; set; }
        public string PhoneNo { get; set; }
        public string Address { get; set; }
    }
}

Create an Interface with name “IEmployeeRepository“. You can name it as per your need.

namespace WEBAPI_CRUDOperations.Models
{
    public interface IEmployeeRepository
    {
        IEnumerable<Employee> GetAll();
        Employee Get(string customerID);
        Employee Insert(Employee item);
        bool Delete(string customerID);
        bool Update(Employee item);
    }
}

Create a class with name “EmployeeRepository” which will implement “IEmployeeRepository“. You can name it as per your need.

namespace WEBAPI_CRUDOperations.Models
{
    public class EmployeeRepository : IEmployeeRepository
    {
    // implement all methods you have defined in IEmployeeRepository Interface. 
    }
}

Write below code inside the EmployeeRepository class to access database. GetData function will return Datatable (Represent One Table off in Memory Data) whenever you request a query in order to read the table.


    public string Connection
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["con"].ConnectionString;
            }
        }
        public DataTable GetData(string query)
        {
            DataTable dt = new DataTable();
            using (SqlConnection myConnection = new SqlConnection(Connection))
            {
                using (SqlCommand myCommand = new SqlCommand(query, myConnection))
                {
                    myCommand.CommandType = CommandType.Text;
                    using (SqlDataAdapter da = new SqlDataAdapter(myCommand))
                    {
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }

Ok Fine! Now you have created models, classes with all entities mapped to table columns, defined Interfaces. Now you have to write code in order to implementation of CRUD operations.

Lets see below code to understand how to implement functions defined in “IEmployeeRepository”

// Return all records
        public IEnumerable<Employee> GetAll()
        {
            List<Employee> employees = new List<Employee>();
            string query = "select ID,Name, Gender, Designation, Department, EmailID, PhoneNo, Address from Employee";
            DataTable dt = GetData(query);
            if (dt.Rows.Count &gt; 0)
            {
                Employee employee;

                int n = 0;
                while (n > dt.Rows.Count)
                {
                    employee = new Employee();
                    employee.ID = Convert.ToInt16(dt.Rows[n]["ID"].ToString());
                    employee.Name = dt.Rows[n]["Name"].ToString();
                    employee.Gender = dt.Rows[n]["Gender"].ToString();
                    employee.Designation = dt.Rows[n]["Designation"].ToString();
                    employee.Department = dt.Rows[n]["Department"].ToString();
                    employee.EmailID = dt.Rows[n]["EmailID"].ToString();
                    employee.PhoneNo = dt.Rows[n]["PhoneNo"].ToString();
                    employee.Address = dt.Rows[n]["Address"].ToString();
                    employees.Add(employee);
                    n++;
                }
            }
            return employees.ToArray();
        }

// insert new record 
public Employee Insert(Employee item)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection(Connection);
cmd.CommandText = "insert into Employee(Name, Gender, Designation, Department, EmailID, PhoneNo, Address) values(@Name,@Gender,@Designation,@Department,@EmailID,@PhoneNo,@Address)";
cmd.Parameters.AddWithValue("@Name", item.Name);
cmd.Parameters.AddWithValue("@Gender", item.Gender);
cmd.Parameters.AddWithValue("@Designation", item.Designation);
cmd.Parameters.AddWithValue("@Department", item.Department);
cmd.Parameters.AddWithValue("@EmailID", item.EmailID);
cmd.Parameters.AddWithValue("@PhoneNo", item.PhoneNo);
cmd.Parameters.AddWithValue("@Address", item.Address);
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int n = cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open)
{
con.Close();
}
return item;
}
// Delete record by ID
public bool Delete(int ID)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection(Connection);
cmd.CommandText = "delete from Employee where ID = '"+ID+"'";
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int n = cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open)
{
con.Close();
}
if (n &gt; 0)
{
return true;
}
else
{
return false;
}
}
// Update record  
public bool Update(Employee item)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection(Connection);
cmd.CommandText = "update Employee set Name =@Name, Gender =@Gender, Designation = @Designation, Department = @Department, EmailID = @EmailID,PhoneNo = @PhoneNo, Address = @Address where ID = @ID";
cmd.Parameters.AddWithValue("@ID", item.ID);
cmd.Parameters.AddWithValue("@Name", item.Name);
cmd.Parameters.AddWithValue("@Gender", item.Gender);
cmd.Parameters.AddWithValue("@Designation", item.Designation);
cmd.Parameters.AddWithValue("@Department", item.Department);
cmd.Parameters.AddWithValue("@EmailID", item.EmailID);
cmd.Parameters.AddWithValue("@PhoneNo", item.PhoneNo);
cmd.Parameters.AddWithValue("@Address", item.Address);
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int n = cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open)
{
con.Close();
}
if (n > 0)
{
return true;
}
else
{
return false;
}
}


Create a Controller :

Now you have to create Controller where you can write action method in order to expose your data so client can consume it. To create a controller follow given steps.

  • Expand Installed
  • Click on Controller
  • Select Controller template. Here we are using Web API 2 Controller – Empty
  • Click on OK button.
The Controller class plays an important role, because requests coming from the client hits the controller first. Then the controller decides which model to use to serve the incoming request.

Add below code in “EmployeeController” to access required actions in order to perform CRUD operations.

using System;
using System.Collections.Generic;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using WEBAPI_CRUDOperations.Models;

namespace WEBAPI_CRUDOperations.Controllers
{
    public class EmployeeController : ApiController
    {
        readonly IEmployeeRepository repository = new EmployeeRepository();
     
        public IEnumerable<Employee> GetAllEmployee()
        {
            return repository.GetAll();
        }

        public Employee Get(int ID)
        {
            Employee emp = repository.Get(ID);
            if (emp == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
           return emp;
        }

       public HttpResponseMessage PostEmployee(Employee emp)
        {
            emp = repository.Insert(emp);
            var response = Request.CreateResponse<Employee>(HttpStatusCode.Created, emp);

            string uri = Url.Link("DefaultApi", new { customerID = emp.ID });
            response.Headers.Location = new Uri(uri);
            return response;
        }

        public HttpResponseMessage PutEmployee(int ID, Employee emp)
        {
            emp.ID = ID;
            if (!repository.Update(emp))
            {
                return Request.CreateErrorResponse(HttpStatusCode.NotFound," ID :"+ID);
            }
            else
            {
                return Request.CreateResponse(HttpStatusCode.OK);
            }
        }
        public HttpResponseMessage DeleteEmployee(int ID)
        {
            Employee emp = repository.Get(ID);
            if (emp == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
            else
            {
                if (repository.Delete(ID))
                {
                    return Request.CreateResponse(HttpStatusCode.OK);
                }
                else
                {
                    return Request.CreateErrorResponse(HttpStatusCode.NotFound," ID "+ID );
                }
            }
        }
    }
}
Why we use Request.CreateResponse and Request.CreateErrorResponse ?
CreateResponse – Creates an HttpResponseMessage wired up to the associated HttpRequestMessage.

CreateErrorResponse – Because it creates an HttpResponseMessage that represents an error.

Methods : 

The four main HTTP methods (GET, PUT, POST, and DELETE) can be mapped to CRUD(Create Read Update Delete) operations as follows:

  • GET retrieves the representation of the resource at a specified URI. GET should have no side effects on the server.
  • PUT updates a resource at a specified URI. PUT can also be used to create a new resource at a specified URI, if the server allows clients to specify new URIs.
  • POST creates a new resource. The server assigns the URI for the new object and returns this URI as part of the response message.
  • DELETE deletes a resource at a specified URI.
Action HTTP method Relative URI
Get a list of all Employee GET api/Employee/GET
Get an Employee by ID GET api/Employee/GET/ID
Create a new Employee POST api/Employee/PostEmployee
Update an Employee PUT api/Employee/PutEmployee
Delete an Employee DELETE api/Employee/DeleteEmployee

Configuring Routing Table 

Whenever HTTP(Hyper Text Trasfer Protocol) requests received by the Web API framework, it always uses the routing table to determine which action have to be invoke.

Web API framework first selects controller by IHttpControllerSelector.SelectController method and then selects action method by IHttpActionSelector.SelectAction depending on matching value in request uri and configured route table.

You can configure route table in WebApiConfig.cs file. So open this file from App_Start folder add below code.

using System.Net.Http.Headers;
using System.Web.Http;

namespace WEBAPI_CRUDOperations
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        { 
            config.Routes.MapHttpRoute(
                name: "WithActionApi",
                routeTemplate: "api/{controller}/{action}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );

// If you want your Web API should return JSON response as default you will have to configure JSON Media Type Formatter.
            config.Formatters.JsonFormatter.SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/html"));
        }
    }
}

After writing above code, make sure you have also mentioned below code in Global.asax.cs

using System.Web.Http;

namespace WEBAPI_CRUDOperations
{
    public class WebApiApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {         
            WebApiConfig.Register(GlobalConfiguration.Configuration);
        }
    }
}

Done! Now you have learned how to write code for basic crud operation using WEB API in Asp.Net. As we already discussed in my previous article based on WEB API, we can use tools to check WEB API is working correctly or not. Fiddler and Postman are two great and free available tools with the help you can easily check services.

URL to check WEB API CRUD Actions
http://localhost:PORT/api/Employee/Get/
http://localhost:PORT/api/Employee/Get/ID
http://localhost:PORT/api/Employee/PostEmployee
http://localhost:PORT/api/Employee/PutEmployee/ID
http://localhost:PORT/api/Employee/DeleteEmployee/ID
Now you can execute below RESTful Read operation using browser. The first url will return all Customers and second url will read single customer depending on customerID provided. You might have to change port number.

To get return response in JSON format you have to write one addition line in Register function of WebApiConfig class

config.Formatters.JsonFormatter.SupportedMediaTypes.Add(new MediaTypeHeaderValue(“text/html”));


Otherwise, whenever you execute GET request, ASP.NET Web API framework will return response in XML format by default.

You can download complete source code from here

Download Source Code

You can also use authentication and authorization in your WEB API CRUD application to make it more secure in order to expose your WEB API resources to client side. You can read our article based on WEB API Authentication.

How to secure ASP.NET Web API using basic authentication

How to secure ASP.NET Web API using Token Based Authentication

What Next ?

In Coming days, you can see a bunch of new articles.

  • How to Perform CRUD operation by using Fiddler. Here we’ve learn how to create WEB API but we didn’t learn how to test CRUD operation as a client.
  • We will learn how to perform CRUD operations using WEB API and Entity Framework Code First approach.

Till then stay tuned. Share our article among needy friends. Happy Coding !!

Share Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *