CRUD In Web API Using SQL Server

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.
   
   
   
   
3. After selecting the template, click OK.
4. Now, in Solution Explorer, you can see two controllers. Add a new API Controller.
   
   
   
   
5. Name it as CustomersController.
6. Create a table in SQL Server and name it as Customer.
   
   
   
   
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.

   //This method will return All customers' list
         public IEnumerable<Customer> Get()
         {
             return db.Customers.ToList();
         }
   
   
         //This method will return a single Customer against id
         public Customer Get(int id)
         {
             Customer customer = db.Customers.Find(id);
             return customer;
         }

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.

   //This method will add a new Customer
         public void POST(Customer customer)
         {
             db.Customers.Add(customer);
             db.SaveChanges();
         }

3. The Put method will update a customer's record in our database.

   //This method to Update a Customer
         public void PUT(int id, Customer customer)
         {
             var customer1 = db.Customers.Find(id);
             customer1.Email = customer.Email;
             customer1.FullName = customer.FullName;
             customer1.Location = customer.Location;
             customer1.Mobile = customer.Mobile;
             db.Entry(customer1).State = System.Data.Entity.EntityState.Modified;
             db.SaveChanges();
         }

4. The Delete method will delete a record from the database.

   //This method will delete a customer
          public string Delete(int id)
          {
              Customer customer = db.Customers.Find(id);
              db.Customers.Remove(customer);
              db.SaveChanges();
              return "Customer Deleted";
          }

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.
   
   
   
   
2. Testing Get method with id as a parameter.
   
   
   
   
3. Testing the POST method by passing an object of Customer.
   
   
   
   
4. Testing the PUT method by updating a Customer record.
   
   
   
   
5. Deleting a Customer record by id.
   
   

After performing all CRUD operations, this is the final result.

 

That's it. This is how we can perform the CRUD operations in a Web API and SQL Server Database.