Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

ASP.NET Web API and SQL Server

Suggested Videos
Part 1 - What is ASP.NET Web API
Part 2 - Creating a Web API Project

In this video 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. 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" 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 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

In our next video we will discuss the concept of Content Negotiation in Web API

ASP.NET Web API tutorial for beginners


  1. Dear Venkat,

    It is very well explained. Thank you for making us understand the concept with ease. However, you have used database first approach in the demo. If possible can you please explain the concept with code first approach?

  2. When I try to add the GetMethod, I am getting this error "'EmployeeDataAccess.EmployeeDBEntities': type used in a using statement must be implicitly convertible to 'System.IDisposable'". Can you tell me the solution as I already have done the basic error fixing but still not got to the point of solving it.

  3. When I try to add the GetMethod, I am getting this error "'EmployeeDataAccess.EmployeeDBEntities': type used in a using statement must be implicitly convertible to 'System.IDisposable'". Can you tell me the solution as I already have done the basic error fixing but still not got to the point of solving it.

    1. Install Entity Framework to your project from NuGet PM

  4. I am getting the below error while navigating to the url /api/employees. what could be the issue? pls help.
    "The type initializer for 'System.Data.Entity.Internal.AppConfig' threw an exception."

  5. Help me please why return null data in Get(int id).

    thank you !


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.