ASP NET Web API and SQL Server




About this tutorial:

Video duration: 11:17
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.

Text version of the video

Slides

Dot Net and SQL Server Tutorials

All ASP.NET Web API Text Articles and…

Incoming search terms:

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

43 thoughts on “ASP NET Web API and SQL Server

    Levi Waddingham

    (November 18, 2018 - 1:43 pm)

    Thank you very much, this made things really easy.

    Shrinath GK

    (November 18, 2018 - 1:43 pm)

    Why choosing both MVC and Webapi both while creating webapi. application

    Andre Masters

    (November 18, 2018 - 1:43 pm)

    Remember to add Entity Framework 6.2.00 to BOTH projects in the solution. Use Nuget Package manager to select ALL online for the correct Entity Framework package for the Employees Service Project.

    Smeshh Drop Hits

    (November 18, 2018 - 1:43 pm)

    Hello, I'm using access database and I get this inside my Public IEnumberable<Test> Get() method.

    cannot convert type 'system.collection.generic.list<>' to 'system.collection.generic.IEnumerable<>

    Tourism Travels

    (November 18, 2018 - 1:43 pm)

    <ExceptionMessage>
    Schema specified is not valid. Errors: employeeDataModel.csdl(3,4) : error 0019: Each type name in a schema must be unique. Type name 'EmployeeModel.Employee' was already defined.
    </ExceptionMessage>

    Henry Ngari

    (November 18, 2018 - 1:43 pm)

    Thanks a lot it really helped me.

    Ilan Perez

    (November 18, 2018 - 1:43 pm)

    Dont know if you are still around…I am using EF 6 and i have foriegn keys. When i run the simple get method "'ObjectContent`1' type failed to serialize the response body" error. If i cut the FK connection it works fine. Can you explain how to fix

    Vishal Makam

    (November 18, 2018 - 1:43 pm)

    Hello Sir, I M New to programming i want to upload this web api with sql server on my static ip can you help me or upload any video.

    Mladen Tasic

    (November 18, 2018 - 1:43 pm)

    VS2017 – Install-Package EntityFramework must be run in Packet Manager in order to work

    Abhijeet Ghatage

    (November 18, 2018 - 1:43 pm)

    I am going to execute get(int id) but still it executing get()
    below is my code plz help me

    public IEnumerable<Master_NewBusinessUserRegistration> get()
    {
    using (CityBookEntities entites = new CityBookEntities())
    {
    return entites.Master_NewBusinessUserRegistration.ToList();
    }
    }

    public Master_NewBusinessUserRegistration get(int code)
    {
    using (CityBookEntities entites = new CityBookEntities())
    {
    //int uc = Convert.ToInt32(userCode);
    return entites.Master_NewBusinessUserRegistration.FirstOrDefault(a => a.userCode == code);
    }
    }

    Neeraj Kulkarni

    (November 18, 2018 - 1:43 pm)

    One of the best videos i have seen. Got to learn for first time in my life about how API 's are developed. I could develop API watching the videos

    aswathy achu

    (November 18, 2018 - 1:43 pm)

    if mutiple tables means how to do??

    yamlCase

    (November 18, 2018 - 1:43 pm)

    If anyone is interested in MySQL instead, here's the query to build the same tables db/table/data:

    Create Database EmployeeDB;

    Use EmployeeDB;

    Create table Employees
    (
    ID int NOT NULL AUTO_INCREMENT,
    FirstName varchar(50),
    LastName varchar(50),
    Gender varchar(50),
    Salary int,
    PRIMARY KEY (ID)
    );
    Insert into Employees(FirstName, LastName, Gender, Salary) values
    ('Mark', 'Hastings', 'Male', 60000),
    ('Steve', 'Pound', 'Male', 45000),
    ('Ben', 'Hoskins', 'Male', 70000),
    ('Philip', 'Hastings', 'Male', 45000),
    ('Mary', 'Lambeth', 'Female', 30000),
    ('Valarie', 'Vikings', 'Female', 35000),
    ('John', 'Stanmore', 'Male', 80000);

    chamila Pathirana

    (November 18, 2018 - 1:43 pm)

    great tutorial !! how do i return the all the matching rows from the table? firstofDefaulte returns only first row .

    Kr4iT 11

    (November 18, 2018 - 1:43 pm)

    For those who did not got ADO.net entity in class file select Class Library (.Net Framework)

    feroz mohammad

    (November 18, 2018 - 1:43 pm)

    Hi, I am facing this error…
    System.Data.Entity.Core.MetadataException
    HResult=0x80131939
    Message=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.
    Source=<Cannot evaluate the exception source>
    StackTrace:
    <Cannot evaluate the exception stack trace>
    Please it would be helpful if someone posts a solution

    naresh nimmagadda

    (November 18, 2018 - 1:43 pm)

    Hi Venkat Garu,

    I am getting below issue .Even I add the Entity Framework from NuGet Package and Connection string also added to webconfig file also.

    Even i add the Newtonsoft.Json Code to WebApiConfig .cs also.

    Please give the solution for the below issue.

    {"$id":"1","Message":"An error has occurred.","ExceptionMessage":"The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; charset=utf-8'.","ExceptionType":"System.InvalidOperationException","StackTrace":null,"InnerException":{"$id":"2","Message":"An error has occurred.","ExceptionMessage":"Error getting value from 'Contacts' on

    ferdi cuyar

    (November 18, 2018 - 1:43 pm)

    everybody makes similar video. One table and get metod, but its not working on multy and releatinal tables.

    Sneha Das

    (November 18, 2018 - 1:43 pm)

    i am using VS 2017 and i am getting an error.
    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.
    EDIT: i got the solution

    Beatrix Ducz

    (November 18, 2018 - 1:43 pm)

    Why did you put it in a separate project?

    sanatan kumar

    (November 18, 2018 - 1:43 pm)

    <Error>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>
    The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.
    </ExceptionMessage>
    <ExceptionType>System.InvalidOperationException</ExceptionType>
    <StackTrace/>
    <InnerException>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>
    Type 'System.Data.Entity.DynamicProxies.Emp_1E6B7FC9C644212EDA69566211DA1B7D1850CF4A293E5E824AA81FA5B6AD77AE' with data contract name 'Emp_1E6B7FC9C644212EDA69566211DA1B7D1850CF4A293E5E824AA81FA5B6AD77AE:http://schemas.datacontract.org/2004/07/System.Data.Entity.DynamicProxies&#39; is not expected. Consider using a DataContractResolver if you are using DataContractSerializer or add any types not known statically to the list of known types – for example, by using the KnownTypeAttribute attribute or by adding them to the list of known types passed to the serializer.
    </ExceptionMessage>
    <ExceptionType>
    System.Runtime.Serialization.SerializationException
    </ExceptionType>
    <StackTrace>
    at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeAndVerifyType(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, Boolean verifyKnownType, RuntimeTypeHandle declaredTypeHandle, Type declaredType) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithXsiType(XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle objectTypeHandle, Type objectType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle, Type declaredType) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.InternalSerialize(XmlWriterDelegator xmlWriter, Object obj, Boolean isDeclaredType, Boolean writeXsiType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle) at WriteArrayOfEmpToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext , CollectionDataContract ) at System.Runtime.Serialization.CollectionDataContract.WriteXmlValue(XmlWriterDelegator xmlWriter, Object obj, XmlObjectSerializerWriteContext context) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.WriteDataContractValue(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeAndVerifyType(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, Boolean verifyKnownType, RuntimeTypeHandle declaredTypeHandle, Type declaredType) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithXsiTypeAtTopLevel(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle originalDeclaredTypeHandle, Type graphType) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObjectContent(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObject(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.XmlObjectSerializer.WriteObjectHandleExceptions(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.WriteObject(XmlWriter writer, Object graph) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken) — End of stack trace from previous location where exception was thrown — at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()
    </StackTrace>
    </InnerException>
    </Error>

    Carlos Parmeggiani

    (November 18, 2018 - 1:43 pm)

    Congrats Great documentation

    Vikas Singh

    (November 18, 2018 - 1:43 pm)

    i have Error in Execution of this project
    System.Data.Entity.Core.MetadataException
    HResult=0x80131939
    Message=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.
    Source=<Cannot evaluate the exception source>
    StackTrace:
    <Cannot evaluate the exception stack trace>
    Help me sir ……………………………………

    Vikas Singh

    (November 18, 2018 - 1:43 pm)

    In My project Occour some error on adding controller in Employee service project. what i do ?

    Error: There was en error running the selected code generator.
    'could not load files or assembley ' 'EmployeedataAccess.dll' or on of dependencias rhe system canot find the files specefied. plz help me sir…

    May Akin

    (November 18, 2018 - 1:43 pm)

    I have watched your tutorial and followed you step by step.I have got this error while i was launching url
    Server Error in '/' Application.

    The resource cannot be found.

    Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

    Requested URL: /

    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.3056.0

    Anas Hussain Aktaa

    (November 18, 2018 - 1:43 pm)

    Install EntityFramework 6 if you work on Visual Studio 2017

    Supren915

    (November 18, 2018 - 1:43 pm)

    When adding the connection to the DB on VS 2017. I had to include the full server name to my local system to get it to work. (server) or "." would not work and test connection would fail.
    Example: User/SQLExpress
    Hope this helps.

    prasad v j Jayaprakash

    (November 18, 2018 - 1:43 pm)

    Auto generated employee.cs file dose not get.

    Sydur Rahman

    (November 18, 2018 - 1:43 pm)

    "Employee Service" project under the solution will require entity framework installed… Otherwise data will not be available and an error will raise!!!

    B Kishor

    (November 18, 2018 - 1:43 pm)

    wow its working thank you..

    Rene Braun

    (November 18, 2018 - 1:43 pm)

    Hi. Thanks for the video. How do I 'publish'/transfer this from my computer to an existing web server ?

    Harsimran Singh

    (November 18, 2018 - 1:43 pm)

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

    eslam Akrm

    (November 18, 2018 - 1:43 pm)

    I Can't Find class library In VS2017

    Melinda Reiz

    (November 18, 2018 - 1:43 pm)

    Hi, thank you for this tutorial.
    After rebuilding my solution I keep receiving 2 errors that I can't figure out how to solve.
    I already looked at the comments and try them out, but it seem not to work for me.
    Here's the error output:
    1>—— Rebuild All started: Project: EmployeeDataAccess, Configuration: Debug Any CPU ——
    1>C:UsersMyNamesourcereposEmployeeServiceEmployeeDataAccessEmployeeDataModel.Context.cs(28,30,28,38): error CS0246: The type or namespace name 'Employee' could not be found (are you missing a using directive or an assembly reference?)
    2>—— Rebuild All started: Project: EmployeeService, Configuration: Debug Any CPU ——
    2>CSC : error CS0006: Metadata file 'C:UsersMyNamesourcereposEmployeeServiceEmployeeDataAccessbinDebugEmployeeDataAccess.dll' could not be found
    ========== Rebuild All: 0 succeeded, 2 failed, 0 skipped ==========
    I would really appreaciate your help!

    Kushagra Singh

    (November 18, 2018 - 1:43 pm)

    Can't add controller classname.dll is missing

    Anindita Ghosh

    (November 18, 2018 - 1:43 pm)

    how will i get your blog for sql query

    rushikesh sangvikar

    (November 18, 2018 - 1:43 pm)

    couldn't find ado.net entity data model…………also searched in nuget package manager

    Sabab Zulfiker

    (November 18, 2018 - 1:43 pm)

    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.

    Getting this error, while loading, api/employees

    Bart Heimenberg

    (November 18, 2018 - 1:43 pm)

    What I would love to know is: why is the Model NOT added to the web.api directly? In stead you are creating a new project. Can you explain that please?

    Preemi P

    (November 18, 2018 - 1:43 pm)

    web api 2 controller -empty unable to add… Please suggest

    Aaron Arnold

    (November 18, 2018 - 1:43 pm)

    It was a mess trying to add a class library and then reference it's ADO.NET Entity Framework model. I just added Entity Framework reference directly into the main project and was just fine in VS 2017.

    smart bzsh

    (November 18, 2018 - 1:43 pm)

    Malfunction Behavior: I am getting all records returned even after adding Id in my request. Here is my request http://localhost:55406/Api/Invoice/1341 but i get all invoices in this case as well. Please suggest

    bubai banerjee

    (November 18, 2018 - 1:43 pm)

    You are one of the best instructors I have come across in my life. Thank you Sir.

Leave a Reply

Your email address will not be published. Required fields are marked *