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 smartphones, 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 using "ASP.NET MVC 4 Web Application" and name it as you choose. I used "WebApiDemo" as shown in the following image.

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

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

Step 4. Right-click the Controllers folder in the Solution Explorer of Visual Studio. Select "Add" ➤ "Controller" and provide the 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.

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

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

Code Segment

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public int ManagerId { get; set; }
}

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

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.

[HttpGet]
[ActionName("GetEmployeeByID")]
public Employee Get(int id)
{
    //return listEmp.First(e => e.ID == id);
    SqlDataReader reader = null;
    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";

    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "Select * from tblEmployee where EmployeeId=" + id + "";
    sqlCmd.Connection = myConnection;

    myConnection.Open();
    reader = sqlCmd.ExecuteReader();
    Employee emp = null;
    while (reader.Read())
    {
        emp = new Employee();
        emp.EmployeeId = Convert.ToInt32(reader.GetValue(0));
        emp.Name = reader.GetValue(1).ToString();
        emp.ManagerId = Convert.ToInt32(reader.GetValue(2));
    }

    myConnection.Close();
    return emp;
}

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.

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.

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.

[HttpPost]
public void AddEmployee(Employee employee)
{
    //int maxId = listEmp.Max(e => e.ID);
    //employee.ID = maxId + 1;
    //listEmp.Add(employee);

    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";

    //SqlCommand sqlCmd = new SqlCommand("INSERT INTO tblEmployee (EmployeeId,Name,ManagerId) Values (@EmployeeId,@Name,@ManagerId)", myConnection);
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "INSERT INTO tblEmployee (EmployeeId,Name,ManagerId) Values (@EmployeeId,@Name,@ManagerId)";
    sqlCmd.Connection = myConnection;

    sqlCmd.Parameters.AddWithValue("@EmployeeId", employee.EmployeeId);
    sqlCmd.Parameters.AddWithValue("@Name", employee.Name);
    sqlCmd.Parameters.AddWithValue("@ManagerId", employee.ManagerId);

    myConnection.Open();
    int rowInserted = sqlCmd.ExecuteNonQuery();
    myConnection.Close();
}

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}.

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.

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.

[ActionName("DeleteEmployee")]
public void DeleteEmployeeByID(int id)
{
    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";

    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "delete from tblEmployee where EmployeeId=" + id + "";
    sqlCmd.Connection = myConnection;

    myConnection.Open();
    int rowDeleted = sqlCmd.ExecuteNonQuery();
    myConnection.Close();
}

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:

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 a database backup for user help.

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

• How MediaTypeFormatter and MediaTypeMapping Are Associated With Each Other in WebAPI.
• WebAPI: MediaTypeFrommatters in WebAPI.

Detailed links to learn more about the WebAPI.

Points to consider

• You should have a little knowledge of the WebAPI.
• You should have 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.