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
Part 3 - HTTP GET PUT POST DELETE



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

Use EmployeeDB
Go

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

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)
Go

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

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

ASP.NET Web API tutorial for beginners

36 comments:

  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?

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

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

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

    ReplyDelete
    Replies
    1. Install Entity Framework to your project from NuGet PM

      Delete
    2. this one solves it!

      Delete
  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."

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

    thank you !

    ReplyDelete
  6. Great.Thank you very much Sir.

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

    ReplyDelete
    Replies
    1. try to install new entity framework ver 6

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

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

    ReplyDelete
  9. When i tried to navigate to api/employees i get the following exception.
    System.Data.Entity.Infrastructure.UnintentionalCodeFirstException
    Help me out

    ReplyDelete
  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'.

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

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

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

    ReplyDelete
  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=6.0.0.0, 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

    ReplyDelete
    Replies
    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

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

    ReplyDelete
  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?

    ReplyDelete
  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

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

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

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

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

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

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

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

    ReplyDelete
  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?

    ReplyDelete
  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 http://go.microsoft.com/fwlink/?LinkId=260882 for more information.
    System.Data.Entity.Core.MetadataException
    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.................

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

      Delete
    2. just Copy EntityFramework.SqlServer.dll from \EmployeeDataAccess\bin\Debug folder to
      EmployeeService\EmployeeService\bin folder

      Delete
  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

    ReplyDelete

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