CRUD In Web API Using SQL Server

CRUD stands for Create, Read, Update and Delete. In this article, we will implement all these methods using SQL Server database. I will test the API using POSTMAN.

What is Web API?

 
Web API or Web Application Programming Interface is an extensible framework for building HTTP-based services that can be accessed in different applications on different platforms, such as Web, Mobile, or Windows. It works as ASP.NET MVC but instead of returning a View, it returns the data as a response. It is a Web Service and only supports HTTP protocol.
 

What is CRUD?

 
CRUD stands for Create, Read, Update and Delete. In HTTP, Create is a POST method, Read is a GET method, Update is a PUT method, and Delete is a DELETE method. An API performs these four functions. In this article, I will use the SQL Server database to implement all these methods. We will test the API using POSTMAN.
 

Implementation

 
Let us create a Web API using the following steps.
  1. Create a new project in Visual Studio. Go to New Project > ASP.NET Web application and name it as Web_API_CRUD (you can give a name according to your choice).
  2. On the next popup window, select the Web API template.

    CRUD In Web API Using SQL Server

  3. After selecting the template, click OK.
  4. Now, in Solution Explorer, you can see two controllers. Add a new API Controller.

    CRUD In Web API Using SQL Server

  5. Name it as CustomersController.
  6. Create a table in SQL Server and name it as Customer.

    CRUD In Web API Using SQL Server

  7. Now, add an ADO.NET Entity Data Model for your database.
  8. Create an object of the "Entities" class in your Customer Controller.

CRUD Implementation

  1. The Read operation is referred to as the GET method in API. We will add two Get methods in our Controller. The first Get method will be without parameters and it will return a list of customers. Whereas the second Get method will accept an id and return a single record against that id. Code snippet for Get methods is given below for your reference.
    1. //This method will return All customers' list  
    2.       public IEnumerable<Customer> Get()  
    3.       {  
    4.           return db.Customers.ToList();  
    5.       }  
    6.   
    7.   
    8.       //This method will return a single Customer against id  
    9.       public Customer Get(int id)  
    10.       {  
    11.           Customer customer = db.Customers.Find(id);  
    12.           return customer;  
    13.       }  
  2. The Create method is known as a POST method in API. I will add a POST method that will accept a "Customer" type object in parameters and add that Customer in the database.
    1. //This method will add a new Customer  
    2.       public void POST(Customer customer)  
    3.       {  
    4.           db.Customers.Add(customer);  
    5.           db.SaveChanges();  
    6.       }  
  3. The Put method will update a customer's record in our database.
    1. //This method to Update a Customer  
    2.       public void PUT(int id, Customer customer)  
    3.       {  
    4.           var customer1 = db.Customers.Find(id);  
    5.           customer1.Email = customer.Email;  
    6.           customer1.FullName = customer.FullName;  
    7.           customer1.Location = customer.Location;  
    8.           customer1.Mobile = customer.Mobile;  
    9.           db.Entry(customer1).State = System.Data.Entity.EntityState.Modified;  
    10.           db.SaveChanges();  
    11.       }  
  4. The Delete method will delete a record from the database.
    1. //This method will delete a customer  
    2.        public string Delete(int id)  
    3.        {  
    4.            Customer customer = db.Customers.Find(id);  
    5.            db.Customers.Remove(customer);  
    6.            db.SaveChanges();  
    7.            return "Customer Deleted";  
    8.        }  

Testing with POSTMAN

 
POSTMAN is a tool for testing the APIs. It is free and you can download it from their website. I will test all these operations one by one.
  1. Testing Get method without any parameter.

    CRUD In Web API Using SQL Server

  2. Testing Get method with id as a parameter.

    CRUD In Web API Using SQL Server

  3. Testing the POST method by passing an object of Customer.

    CRUD In Web API Using SQL Server

  4. Testing the PUT method by updating a Customer record.

    CRUD In Web API Using SQL Server

  5. Deleting a Customer record by id.

    CRUD In Web API Using SQL Server
After performing all CRUD operations, this is the final result.
 
CRUD In Web API Using SQL Server
That's it. This is how we can perform the CRUD operations in a Web API and SQL Server Database.