Create Read Operation in Web API Using MVC 4

Today I will show you how to support a read operation from the SQL Server database using a HTTP service using the ASP.NET Web API in MVC 4. I will create a very simple web API to read a list of Employees details.

To do this first we must create a database in SQL Server.

Step 1: Create a new database in SQL Server. Then, create a new table named "Employee":



Step 2: Insert some records to the newly created table:



Now, We are going to create a HTTP Service using the ASP.Net Web API in Visual Studio 2012 RC.

Step 1: Open the Visual Studio 2012 RC.

• Go to the Visual C# -> File menu -> New-> Project.
• Then, select Web from the left pane.
• Select ASP.NET MVC 4 Web Application.
• Rename it which as want.
• Click Ok button.

Step 2 After that select Web API from the opened window. This will create a basic architecture of a MVC.

Step 3: Now, we are going to add a model class that contains the data in your application:

• In Solution Explorer, right-click the Models folder.
• Select Add, then select Class.
• Name the class "Employee".
• Click the Add button after that.

Step 4: Now we will create a class in the Employee.cs file that contains some properties related to the database fields:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

 

namespace WebApi.Models

{

    public class product

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string desig { get; set; }

        public decimal salary { get; set; }

    }

}

Step 5: Here, we use the Repository Design pattern to seperate our service implementation. Add a new class as in Step 3 to the Model folder. Give it the name EmployeeDetails.cs.



Step 6: It contains the methods that fetches the data from SQL Server database and creates a collection of Employees and stores it in a variable.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

 

namespace WebApi.Models

{

    public class productdetails

    {

        private List<product> products = new List<product>();

        private int _nextId = 1;

        SqlConnection con;

        SqlDataAdapter da;

        DataSet ds=new DataSet();
 

        public IEnumerable<product> GetAll()

        {

            con = new SqlConnection("Data Source=MYPC;Initial Catalog=MyDb;uid=sa;pwd=wintellect");

            da = new SqlDataAdapter("select * from Employee", con);

            da.Fill(ds);

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                products.Add(new product() { Id=int.Parse(dr[0].ToString()),Name=dr[1].ToString(), desig=dr[2].ToString(),salary=int.Parse(dr[3].ToString())});

           

            }

            return products;

        }

    }

}

Step 7: Now, its time to create a Web API Controller that handles HTTP requests from the client. The Web API Controller contains two controllers in the Solution Explorer.

1. HomeController is a traditional ASP.NET MVC controller.
2. ValuesController is an example WebAPI controller.

Delete the ValuesController from the Solution Explorer under the controller folder.

Step 8: Now add a new controller, as follows:

• In Solution Explorer, right-click the the Controllers folder.
• Select Add and then select Controller.
• Name the controller EmpolyeeController.
• Select Empty API controller from dropdown list.
• Click the Add button.

Step 9: In this controller you can add an action for Get. The Get action is for fetching all Employee details from the database. It contains HTTP GET methods.

Here is the method to get the list of all products:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Net.Http;

using System.Web.Http;

using WebApi.Models;

 

namespace WebApi.Controllers

{

    public class productController : ApiController

    {

        static readonly productdetails repository = new productdetails();

        public IEnumerable<product> GetAllProducts()

        {

            return repository.GetAll();

        }     

    }

}

In the above code we create a method name that starts with "Get" that automatically maps to GET requests from the client URI and has no parameters.

Step 10: Now, our Web API is ready to be used for read operations. Build and run this project on your local computer. It will generate a URL and the client will access the API at http://localhost:1240.

Step 11: The Client will get the complete list of Employees by entering this URI into your browser:

http://localhost:1240/api/product.