In Focus

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

In this article, I will share my thoughts of how to implement Restful operations in the 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 database. 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.