WebAPI: Restful CRUD Operations in WebAPI Using ADO.NET Objects and SQL Server

The Web API is a platform to develop HTTP / HTTPS based services that can be used by native applications like on smart phones, tablets and any browsers. The WebAPI is a core part of ASP.NET and provides ways to create Restful services and can be used by any applications that can understand HTTP communications.

Implementation of Restful operations using ASP.NET Web API

Let's create a sample application and do this step-by-step.

Step 1

Let's first create a sample web application and using "ASP.NET MVC 4 Web Application" and name it as you choose. I used "WebApiDemo" as shown in the following image:

Web Application

Step 2

Click OK and choose the Web API command from the templates shown in the wizard window.

Web API

Step 3

You will find the application structure as shown below at first sight.

application structure

Step 4

Right-click the Controllers folder in the Solution Explorer of Visual Studio. Select "Add" ➤ "Controller" and provide a name of EmployeesController for the controller. Leave the option "Empty API Controller" selected in the Template dropdown and click "Add", as shown in the figure below. Notice that the generated controller class inherits from ApiController, a class that is part of the ASP.NET Web API framework.

Controllers folder

Step 5

Right-click the Models folder in the Solution Explorer of Visual Studio. Select "Add" ➤ "Class" to add a new class with a name of Employee.

class

After creating the Employee class, kindly add the following code into this class.

Code Segment

  1. public class Employee  
  2. {  
  3.    public int EmployeeId { getset; }  
  4.    public string Name { getset; }  
  5.    public int ManagerId { getset; }  
  6. }  
emp class

This is the complete structure of the solution as depicted in the image below:

complete structure

Now we'll open the EmployeeController and implement the Get-Post-Delete-Update methods one by one.

Execution of Get Method of WebAPI:

Now open the EmployeeController and paste the following code into that as the Get WebAPI method:
  1. [HttpGet]  
  2.  [ActionName("GetEmployeeByID")]  
  3.  public Employee Get(int id)  
  4.  {  
  5.      //return listEmp.First(e => e.ID == id);  
  6.      SqlDataReader reader = null;  
  7.      SqlConnection myConnection = new SqlConnection();  
  8.      myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";  
  9.   
  10.      SqlCommand sqlCmd = new SqlCommand();  
  11.      sqlCmd.CommandType = CommandType.Text;  
  12.      sqlCmd.CommandText = "Select * from tblEmployee where EmployeeId=" + id + "";  
  13.      sqlCmd.Connection = myConnection;  
  14.      myConnection.Open();  
  15.      reader = sqlCmd.ExecuteReader();  
  16.      Employee emp = null;  
  17.      while (reader.Read())  
  18.      {  
  19.          emp = new Employee();  
  20.          emp.EmployeeId = Convert.ToInt32(reader.GetValue(0));  
  21.          emp.Name = reader.GetValue(1).ToString();  
  22.          emp.ManagerId = Convert.ToInt32(reader.GetValue(2));  
  23.      }  
  24.      return emp;  
  25.      myConnection.Close();  
  26.  }  
In the same way we will implement the Post and Delete employee. Now I have run the application and see the results. Press F5, it will show you the following screen:

run application

The preceding Blue screen shows that the Web API is up and running.

I've used Fiddler to trace the requests, so I will open Fiddler to execute the following link to retrieve the information of the EmployeeID “15”. http://localhost:57888/api/Employees/GetEmployeeById/15.

GetEmployeeById

Employees

Execution of AddEmployee (POST) Method of WebAPI

Now open the EmployeeController and paste the following code into that as the Post WebAPI method. The code is shown below:
  1. [HttpPost]  
  2. public void AddEmployee(Employee employee)  
  3. {  
  4.     //int maxId = listEmp.Max(e => e.ID);  
  5.     //employee.ID = maxId + 1;  
  6.     //listEmp.Add(employee);  
  7.   
  8.   
  9.     SqlConnection myConnection = new SqlConnection();  
  10.     myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";  
  11.     //SqlCommand sqlCmd = new SqlCommand("INSERT INTO tblEmployee (EmployeeId,Name,ManagerId) Values (@EmployeeId,@Name,@ManagerId)", myConnection);  
  12.     SqlCommand sqlCmd = new SqlCommand();  
  13.     sqlCmd.CommandType = CommandType.Text;  
  14.     sqlCmd.CommandText = "INSERT INTO tblEmployee (EmployeeId,Name,ManagerId) Values (@EmployeeId,@Name,@ManagerId)";  
  15.     sqlCmd.Connection = myConnection;  
  16.   
  17.   
  18.     sqlCmd.Parameters.AddWithValue("@EmployeeId", employee.EmployeeId);  
  19.     sqlCmd.Parameters.AddWithValue("@Name", employee.Name);  
  20.     sqlCmd.Parameters.AddWithValue("@ManagerId", employee.ManagerId);  
  21.     myConnection.Open();  
  22.     int rowInserted = sqlCmd.ExecuteNonQuery();  
  23.     myConnection.Close();  
  24. }  
Since the WebAPI is already in running mode, I'll execute the following link to add an employee to the database. http://localhost:57888/api/Employees/AddEmployee and will pass the given the following parameters {"EmployeeId":20,"Name":"Mike Prior","ManagerId":6}.

AddEmployee

Please open the database to confirm whether or not the user “Roger” has been added to the data base. Kindly look at the image shown below:

Roger

We are almost done with the execution of the Post method.

Execution of DeleteEmployeeById (Delete) Method of WebAPI

Now open the EmployeeController again and paste the following code into that as the Delete WebAPI method:
  1. [ActionName("DeleteEmployee")]  
  2. public void DeleteEmployeeByID(int id)  
  3. {  
  4.     SqlConnection myConnection = new SqlConnection();  
  5.     myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";  
  6.       
  7.     SqlCommand sqlCmd = new SqlCommand();  
  8.     sqlCmd.CommandType = CommandType.Text;  
  9.     sqlCmd.CommandText = "delete from tblEmployee where EmployeeId=" + id + "";  
  10.     sqlCmd.Connection = myConnection;  
  11.     myConnection.Open();  
  12.     int rowDeleted = sqlCmd.ExecuteNonQuery();  
  13.     myConnection.Close();  
  14. }  
DeleteEmployeeById

Since the WebAPI is already in running mode, I'll execute the following link to delete an employee by the employee id database. http://localhost:57888/api/Employees/DeleteEmployee/20.

Please open the database to confirm that EmployeeID “20” has been deleted. Kindly look at the image shown below:

DeleteEmployee

Note: I have intentionally left the implementation of the Update method so that the end user can have hands-on on this. I'll also add database backup for user help.

Go through with these detailed links to learn more about the WebAPI.  Detailed links to learn more about the WebAPI.

Points to consider:

  • You should have a little knowledge of the WebAPI.
  • You should have a knowledge of the Fiddler tool to execute the functionality.
  • There is a need for SQL Server also.

I hope it will help you somewhere down the line.

Keep coding and Smile.