CRUD in Web API Using SQL Server and C#


Before we move on to discuss the web API, we should know what API means is. Application Programming Interface (API) is a collection of protocols, tools, and definitions that are used to create applications. API exposes interfaces which can be used to access the information of a service. Back to the Web API, it simply using the services provided by the API over an HTTP protocol. We can use different programming languages to build Web APIs’. For an example GraphAPI provided by Facebook, exposing services to post to the wall, group etc.

What is CRUD Operations?

It is a known fact that each and every person who uses programming language has used at least one of the CRUD operation. CRUD refers to the Create, Read, Update and Delete operations. For each of this operation, there is a particular HTTP method.

  • Create – POST
  • Read – GET
  • Update – PUT
  • Delete – DELETE

Before we start to create the CRUD application, we need to create a connection with the SQL Server. First, we need to create our own database in the SQL Server. Let’s create a database with name “Company” and table as “Employee”.

Now insert data to the table we just created

Next, open the visual studio to create a new ASP.Net project. But you can use any IDE as you prefer which supports .NET technologies. Next, follow the steps provided below.

Select the template “Web API”

Now add a new controller

Select a Web API 2 Controller – Empty

After creating the project and controller, we need to connect the database with the controller class. To import the SQL Server connection string, we need to add an import below. This namespace contains a specific version of ADO.NET which used to connect the database.

using System.Data.SqlClient;

Then we need to specify our connection string in the controller class:

namespace CRUDApplication.Controllers
{
    public class CRUDController: ApiController
    {
        SqlConnection conn = new SqlConnection(new SqlConnectionStringBuilder()
    {
        DataSource = "VIRAJ\VIRA",
        InitialCatalog = "Company",
        UserID = "admin",
        Password = "admin123"
    }.ConnectionString);
    }
}

To get the data source, open the SQL Server and right click on the main server. Then go to the properties window and there you will find the server name. Follow the below procedure to get it clearly done.

After setting up the connection, let’s write some APIs to post, get, update and delete data from the table we created.

Create – POST

First, we will look at the POST method which is used to insert data into the table. Let’s take that we want to add a new employee to the Employee table, for that we need to pass an employee object to the method.

    public void POST(Employee employee)
    {
        conn.Employee.Add(employee);
        conn.SaveChanges();
    }

In here we use the Add() method to insert a new record to the existing tables. Now let’s retrieve data from the table using the GET method. Get method can either have a method with parameters or parameterless. If the get method is parameterless, it means we are retrieving all the data in the table, not the specific data.

  public Employee Get(int id)
    {
        Employee employee = conn.Employee.Find(id);
        return employee;
    }

Read – GET

Let’s try to get a specific employee out of the records.

  public Employee Get(int id)
    {
        Employee employee = conn.Employee.Find(id);
        return employee;
    }

It will find the particular employee and returns the employee object. Afterward, we can update any employee in the table. For that, we can use the PUT method and we have to pass the updated employee object to it. Let’s have a look at how it’s been done.

Update – PUT

  public void PUT(int id, Employee employee)
    {
        var updatedEmployee = conn.Employee.Find(id);

        updatedEmployee.empID = employee.empID;
        updatedEmployee.LastName = employee.LastName;
        updatedEmployee.FirstName = employee.FirstName;
        updatedEmployee.City = employee.City;
        updatedEmployee.age = employee.age;
        conn.Entry(updatedEmployee).State = System.Data.Entity.EntityState.Modified;
        conn.SaveChanges();
    }

First, we find the employee to be updated and populate the properties of the employee with the new values. Finally, we can use the DELETE method to delete a specific employee from the table. Delete method always takes a parameter and that parameter used to identify a particular row from the table.

Delete – DELETE

Same as the update method, first we have to find the particular employee to be deleted using the find() method.

 public string Delete(int empID)
    {
        Employee employee = conn.Employee.Find(empID);
        conn.Employee.Remove(employee);
        conn.SaveChanges();
        return "Employee Deleted Successfully";
    }

This is how we use SQL Server to create a CRUD application. CRUD operations are the base of any web application and we can use any client application to test the operations we created.

Related Articles

Basic Authentication in Web API

Exception Handling in C#

Create a newline in rich text box in C#

Tutorials Panel

About Tutorials Panel

programming tutorial and source code for Software Engineers and developers. https://twitter.com/TutorialsPanel

View all posts by Tutorials Panel →

Leave a Reply