Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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.

    1. Anonymous February 8, 2017 at 2:56 PM
      Install Entity Framework to your project from NuGet PM

  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

    2. this one solves it!

  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 !

  6. Great.Thank you very much Sir.

  7. I am getting an error while adding a controller. It says Could not load file or assambly 'EmployeeDataAccess.dll' or one of its dependencies.

    1. try to install new entity framework ver 6

    2. before your create controller you should do build solution and the you can create controller

  8. Hello. Can you please tell me what 'e' references in 'e => e.ID == id'? I am not able to display a particular record when I enter in a key. Thanks.

  9. When i tried to navigate to api/employees i get the following exception.
    Help me out

  10. I am getting below error while trying http://localhost:60661/api/employees

    Please help.

    This XML file does not appear to have any style information associated with it. The document tree is shown below.

    No HTTP resource was found that matches the request URI 'http://localhost:60661/api/employees'.

    No type was found that matches the controller named 'employees'.

  11. When I try for Get method , there is an error :
    An exception of type 'System.Data.Entity.Core.EntityException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

    Additional information: The underlying provider failed on Open.

  12. When I was trying do this then I got Content type error like this..

    The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.

    That was becoz I generated Model from DB which has FK releations as well. so to solve this you have to create Model View Classes..

  13. If u start teaching in Ameerpet of Hyderabad all the DotNet training institues will be closed due to your teaching effect.Great Explanation..Sirrrrrrrrrr....

  14. Severity Code Description Project File Line Suppression State
    Error CS0012 The type 'DbSet<>' is defined in an assembly that is not referenced. You must add a reference to assembly 'EntityFramework, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'. EmployeeService c:\users\salman\documents\visual studio 2015\Projects\EmployeeService\EmployeeService\Controllers\EmployeesController.cs 18 Active

    can anyone help me out plzzz

    1. Guys, to resolve the error of "'System.Data.Entity.DbContext' is defined in an assembly that is not referenced", you just need to do these steps:
      1. in VS menu go to tools ==> NuGet Package Manager ==> Package manager console
      2. enter this command: Install-Package EntityFramework
      3. rebuild the solution and you will be good to go!

      @Louie B

  15. Thank you. very nice document. You make the complex topics appear easy with simple to understand examples.

  16. Thank you very much Sir, I have a doubt that if we can make it possible to add the entity framework in the same API, why can we take it separately? Is there any special reason to do this?

  17. sir I am Getting an error.
    System.Data.Entity.Core.EntityException: 'The underlying provider failed on Open.'

    SqlException: :Login failed for user 'sa'.
    how Can i resolved it

  18. Getting this error as i am accessing SQL database from other machine locally. Done the Web.config file and App.config file part too but still giving this error please explain.

    An exception of type 'System.Data.Entity.Core.EntityException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

    Additional information: The underlying provider failed on Open.

    1. it seems to me that you are using the .NET core libraries for EF. Use EF6 libraries and try again

  19. How to use web api to retrieve data from Sql database without using entity framework?
    Please provide any website link which you know.

  20. Hi good sir, how can i receive a database connection? I mean I can't find EmployeeDB anywhere

  21. how can i add .cer certificates to my web application and my web application is http and by using the certificates how can i change web site http to https.

  22. Which version of EntityFramework are you working on? I do not have "connectionstrings" option in web config file. Added connectionstrings in sub folders Web.Debug.Config & WebRelease.Config files but doesn't work.

  23. my get(int id) method not working, it always return the full data

  24. I liked tutorial, but I cannot have results in last step. When I addeded the code of EmployeesController.cs controller I had the following error (3 times): Error CS0246 the type name or namespace 'Employee' could not be found (are you missing a using directive or an assembly reference?).
    I tried and tried to fix but I can't understand what is missing or if I have missing some action.
    Could you help me?

  25. I've done everything as per the video but getting following error:

    Schema specified is not valid. Errors: EmployeeDataModel.ssdl(2,2) : error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See for more information.
    at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.ThrowOnNonWarningErrors() at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.LoadItems(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths) at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader..ctor(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths, Boolean throwOnError, IDbDependencyResolver resolver) at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Init(IEnumerable`1 xmlReaders, IEnumerable`1 filePaths, Boolean throwOnError, IDbDependencyResolver resolver, DbProviderManifest& providerManifest, DbProviderFactory& providerFactory, String& providerInvariantName, String& providerManifestToken, Memoizer`2& cachedCTypeFunction) at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection..ctor(IEnumerable`1 xmlReaders, IEnumerable`1 filePaths) at System.Data.Entity.Core.Metadata.Edm.MetadataCache.LoadStoreCollection(EdmItemCollection edmItemCollection, MetadataArtifactLoader loader) at.................

    1. it will be easier if you add your entity framework directly in web need to create other class library and all other stuffs

  26. the project's target fremwork dose not contain entity fremwork runtime assemblies please review the traget freamwork information in project'sproperty page
    how to solve it


It would be great if you can help share these free resources