ASP.NET Web API and SQL Server

Leave a Comment
we will discuss creating ASP.NET Web API service that returns data from a SQL Server database. We will be using this service as the basis for understanding many of the Web API concepts in our upcoming videos.

The Web API EmployeeService that we will be building will retrieve and return the data from the following SQL Server Employees table. We will be using Entity Framework to retrieve data from the database. You can use any technology of your choice to retrieve data from the database. For example you can even use raw ADO.NET.
asp.net web api and sql server

Execute the following SQL Script using SQL Server Management studio. This script creates
1. EmployeeDB database
2. Creates the Employees table and populate it with sample data

Create Database EmployeeDB

Use EmployeeDB

Create table Employees
     ID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Salary int

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000)

Creating a new ASP.NET Web API Project
1. Open Visual Studio and select File - New - Project

2. In the "New Project" window
    Select "Visual C#" under "Installed - Templates"
    From the middle pane select, ASP.NET Web Application
    Name the project "EmployeeService" and click "OK"
asp.net web api entity framework example

3. On the next window, select "Web API" and click "OK"

At this point you should have the Web API project created.

Adding ADO.NET Entity Data Model to retrieve data
1. We will have the Entity Model in a separate project.

2. Right click on EmployeeService solution in the Solution Explorer and select Add - New Project

3. In the Add New Project window
    Select Visual C# from the left pane
    Class Library Project from the Middle pane
    Name the project EmployeeDataAccess and click OK
connect web api to sql server database

4. Right click on EmployeeDataAccess project and select Add - New Item

5. In the "Add New Item" window
    Select "Data" from the left pane
    Select ADO.NET Entity Data Model from the middle pane
    In the Name text box, type EmployeeDataModel and click Add
using entity framework with web api

6. On the Entity Data Model Wizard, select "EF Designer from database" option and click next
asp.net web api entity framework database first

7. On the next screen, click "New Connection" button

8. On "Connection Properties" window, set
    Server Name = (local)
    Authentication = Windows Authentication
    Select or enter a database name = EmployeeDB
    Click OK and then click Next

9. On the nex screen, select Entity Framework 6.x
adding entity framework to web api

10. On the nex screen, select "Employees" table and click Finish.
web api and entity framework 6

Using the Entity Data Model in EmployeeService project
1. Right click on the references folder in the EmployeeService project and select "Add Reference"

2. On the "Reference Manager" screen select "EmployeeDataAccess" project and click OK. 
adding project reference visual studio

Adding Web API Controller
1. Right click on the Controllers folder in EmployeeService project and select Add - Controller

2. Select "Web API 2 Controller - Empty" and click "Add"

3. On the next screen set the Controller Name = EmployeesController and click Add

4. Copy and paste the following code in EmployeesController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using EmployeeDataAccess;

namespace EmployeeService.Controllers
    public class EmployeesController : ApiController
        public IEnumerable<Employee> Get()
            using(EmployeeDBEntities entities = new EmployeeDBEntities())
                return entities.Employees.ToList();

        public Employee Get(int id)
            using (EmployeeDBEntities entities = new EmployeeDBEntities())
                return entities.Employees.FirstOrDefault(e => e.ID == id);

5. At this point build the solution and navigate to /api/employees. You will get the following error.
No connection string named 'EmployeeDBEntities' could be found in the application config file.

6. This is because "Entity Framework" is looking for EmployeeDBEntities connection string in the web.config file of EmployeeService project. EmployeeDBEntities connection string is actually in App.config file of EmployeeDataAccess class library project. Include a copy of this connection string in web.config file.

At this point when you navigate to /api/employees you should see all employees and when you navigate to /api/employees/1 you should see all the details of the employee whose Id=1


Post a Comment

Note: only a member of this blog may post a comment.