Share via

Entity Framework: Asp.net Web API Using Sql Server

  • Article

Introduction

Web API concepts in different fields and Its implementation using Asp.Net MVC and Entity Framework . Web API Controller connects with Sql Server by Entity Framework. Web API Route can be used in different types of clients Like IOTs. ASP.NET Web API is a framework for building Web API’s, i.e. HTTP based services on top of the .NET Framework. 

Description

These Web API services can then be Used by below mentioned lists,

  • Browsers
  • Mobile applications
  • Desktop applications

In Client-Server constraint, Client sends a request and the server sends a response. This separation of concerns supports the independent evolution of the client-side logic and server-side logic.

In Stateless constraint, the client and the server must be stateless between requests. This means we should not be storing anything on the server related to the client. The request from the client should contain all the necessary information for the server to process that request. This ensures that each request can be treated independently by the server.

In Cacheable constraint, the client knows how long this data is good for so that the client does not have to come back to the server for that data over and over again. In Uniform Interface The HTTP verb like GET, PUT, POST, DELETE that is sent with each request tells the API what to do with the resource. Each resource is identified by a specific URI stands for Uniform Resource Identifier.

Why Asp.Net Web API faster than WCF?

WCF was created to develop SOAP-based services and bindings. Since WCF is SOAP-based, which uses standard XML schema over HTTP, it could lead to slower performance. WEB API is a better choice for simpler, lightweight services. WEB API can use any text format including XML and is faster than WCF. WEB API doesn’t require any data contracts and doesn’t require configurations to the level of WCF.

Prerequisite

  • Visual Studio 2017
  • Microsoft SQL Server 2016 (SP1)

Steps To Be Followed

Step 1

Create a table named Employee2 using the below script.

SET ANSI_NULLS ON
GO  
    
SET QUOTED_IDENTIFIER ON
GO  
    
CREATE TABLE  [dbo].[Employee2](  
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,  
    [FirstName] [nvarchar](50) NOT NULL,  
    [LastName] [nvarchar](50) NOT NULL,  
    [EmailID] [nvarchar](200) NULL,   
    [City] [nvarchar](50) NULL,   
    [Country] [nvarchar](50) NULL,   
PRIMARY KEY  CLUSTERED   
(  
    [EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON  [PRIMARY]  
) ON  [PRIMARY]  
    
GO

Now Insert some dummy records into that table using the below script. 

GO  
SET IDENTITY_INSERT [dbo].[Employee2] ON 
    
GO  
INSERT [dbo].[Employee2] ([EmployeeID], [FirstName], [LastName], [EmailID], [City], [Country]) VALUES  (1, N'Satyaprakash', N'Samantaray', N'Satya@gmail.com', N'Bengaluru', N'India')  
GO  
INSERT [dbo].[Employee2] ([EmployeeID], [FirstName], [LastName], [EmailID], [City], [Country]) VALUES  (2, N'Satya', N'Saman', N'Satya.Saman@gmail.com', N'Bhubaneswar', N'India')   
GO  
INSERT [dbo].[Employee2] ([EmployeeID], [FirstName], [LastName], [EmailID], [City], [Country]) VALUES  (3, N'sa', N'sa', N's@gmail.com', N'bangalore', N'india')   
GO  
SET IDENTITY_INSERT [dbo].[Employee2] OFF
GO

Step 2

Add Entity data model named "Satyadatabasemodel.edmx". Inside that model name create entity name "CrystalGranite2016Entities". Inside Model already a autogenerate class Employee.cs as I renamed it Employee2 to Employee same as database object like Table Employee2 with related variables.

 

Code Ref

namespace SatyaWebApi   
{   
    using System;   
    using System.Collections.Generic;   
         
    public partial  class Employee   
    {   
        public int  EmployeeID { get; set; }   
        public string  FirstName { get; set; }   
        public string  LastName { get; set; }   
        public string  EmailID { get; set; }   
        public string  City { get; set; }   
        public string  Country { get; set; }   
    }   
}

Step 3

Create an empty API Controller called "SatyaController.cs". Then, I added a new action to the API Controller for fetch data from database and return to the client application. 

Code Ref

using System;   
using System.Collections.Generic;   
using System.Linq;   
using System.Net;   
using System.Net.Http;   
using System.Web.Http;   
     
namespace SatyaWebApi.Controllers   
{   
    public class  SatyaController : ApiController   
    {   
             
        public HttpResponseMessage Get()   
        {   
            List<Employee> allEmp = new  List<Employee>();   
            using (CrystalGranite2016Entities dc = new CrystalGranite2016Entities())    
            {   
                allEmp = dc.Employees.OrderBy(a => a.FirstName).ToList();    
                HttpResponseMessage response;   
                response = Request.CreateResponse(HttpStatusCode.OK, allEmp);   
                return response;   
            }   
        }   
    }   
}

Code Description

We added strongly typed list of objects that can be accessed by Index.

List<Employee> allEmp = new  List<Employee>();

Here i added action for get to fetch data from database and return to the client.

public HttpResponseMessage Get()   
{   
   //code here....   
}

Here CrystalGranite2016Entities is our DataContext.

using (CrystalGranite2016Entities dc = new CrystalGranite2016Entities())    
{   
    //code here....   
}

Here i have added Linq code for fetch data.

List<Employee> allEmp = new  List<Employee>();        
allEmp = dc.Employees.OrderBy(a => a.FirstName).ToList();

HttpResponseMessage represents http response message. Then, create an HttpResponseMessage wired up to the associated HttpRequestMessage. The HttpStatusCode contains the value of status code defined for HTTP. All these classes and enum shoud need below namespaces.  

using System.Net;   
using System.Net.Http;   
using System.Web.Http;

Step 4

Then, check connectionstring inside web.config file as it is autogenerated during creation of Entity Data Model (.edmx file) .

<connectionStrings>   
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-SatyaWebApi-20180326100928.mdf;Initial Catalog=aspnet-SatyaWebApi-20180326100928;Integrated Security=True" providerName="System.Data.SqlClient" />   
    <add name="CrystalGranite2016Entities" connectionString="metadata=res://*/Satyadatabasemodel.csdl|res://*/Satyadatabasemodel.ssdl|res://*/Satyadatabasemodel.msl;provider=System.Data.SqlClient;provider connection string="data source=SODN-PAVILION\SQLEXPRESS;initial catalog=CrystalGranite2016;user id=sa;password=;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />   
  </connectionStrings>

Step 5

Check the Reference folder and findout two assembly (.Dll files) are related to entity framework related information.

  • EntityFramework
  • EntityFramework.SqlServer 

Step 6

Check out the WebApiConfig.cs to find out Web API routes or URL path of WEB API to get records in XML format.

Code Ref

config.Routes.MapHttpRoute( 

   ``name: ``"DefaultApi"``, 

   ``routeTemplate: ``"api/{controller}/{id}"``, 

   ``defaults: ``new { id = RouteParameter.Optional } 

);

Code Description

Here, path should be http://site_name/api/API_Controller_Name.

Output

The Web API Route is : http://localhost:47250/api/satya .Here satya is name of API Controller name. I got all records in XML format in browser.

Summary

  • What is Asp.Net Web API.
  • How to implement using Entity Framework and MVC.
  • Web API in Real-Time Scenario.
  • Benefits Of Asp.Net Web API.