OData Using ASP.NET Web API 2

Introduction

In this post, we will learn about OData by using ASP.Net Web API 2 in MVC application.

What’s OData protocol?

The Open Data Protocol (OData) is a data access protocol for the web. OData provides a uniform way to query and manipulate data sets through CRUD operations (create, read, update, and delete).

Prerequisites

As I said earlier, we are going to use OData protocol in our MVC application. For this, you must have Visual Studio 2015 (.NET Framework 4.5.2) and SQL Server.

SQL Database part

Here, find the script to create database.

Create Database

1. USE [master]  
2. GO  
3.   
4. /****** Object: Database [EmployeeDB] Script Date: 9/27/2016 2:58:52 AM ******/  
5. CREATE DATABASE [EmployeeDB]  
6. CONTAINMENT = NONE  
7. ON PRIMARY   
8. ( NAME = N'EmployeeDB', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EmployeeDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
9. LOG ON   
10. ( NAME = N'EmployeeDB_log', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EmployeeDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  
11. GO  
12.   
13. ALTER DATABASE [EmployeeDB] SET COMPATIBILITY_LEVEL = 110  
14. GO  
15.   
16. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
17. begin  
18. EXEC [EmployeeDB].[dbo].[sp_fulltext_database] @action = 'enable'  
19. end  
20. GO  
21.   
22. ALTER DATABASE [EmployeeDB] SET ANSI_NULL_DEFAULT OFF   
23. GO  
24.   
25. ALTER DATABASE [EmployeeDB] SET ANSI_NULLS OFF   
26. GO  
27.   
28. ALTER DATABASE [EmployeeDB] SET ANSI_PADDING OFF   
29. GO  
30.   
31. ALTER DATABASE [EmployeeDB] SET ANSI_WARNINGS OFF   
32. GO  
33.   
34. ALTER DATABASE [EmployeeDB] SET ARITHABORT OFF   
35. GO  
36.   
37. ALTER DATABASE [EmployeeDB] SET AUTO_CLOSE OFF   
38. GO  
39.   
40. ALTER DATABASE [EmployeeDB] SET AUTO_CREATE_STATISTICS ON   
41. GO  
42.   
43. ALTER DATABASE [EmployeeDB] SET AUTO_SHRINK OFF   
44. GO  
45.   
46. ALTER DATABASE [EmployeeDB] SET AUTO_UPDATE_STATISTICS ON   
47. GO  
48.   
49. ALTER DATABASE [EmployeeDB] SET CURSOR_CLOSE_ON_COMMIT OFF   
50. GO  
51.   
52. ALTER DATABASE [EmployeeDB] SET CURSOR_DEFAULT GLOBAL   
53. GO  
54.   
55. ALTER DATABASE [EmployeeDB] SET CONCAT_NULL_YIELDS_NULL OFF   
56. GO  
57.   
58. ALTER DATABASE [EmployeeDB] SET NUMERIC_ROUNDABORT OFF   
59. GO  
60.   
61. ALTER DATABASE [EmployeeDB] SET QUOTED_IDENTIFIER OFF   
62. GO  
63.   
64. ALTER DATABASE [EmployeeDB] SET RECURSIVE_TRIGGERS OFF   
65. GO  
66.   
67. ALTER DATABASE [EmployeeDB] SET DISABLE_BROKER   
68. GO  
69.   
70. ALTER DATABASE [EmployeeDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF   
71. GO  
72.   
73. ALTER DATABASE [EmployeeDB] SET DATE_CORRELATION_OPTIMIZATION OFF   
74. GO  
75.   
76. ALTER DATABASE [EmployeeDB] SET TRUSTWORTHY OFF   
77. GO  
78.   
79. ALTER DATABASE [EmployeeDB] SET ALLOW_SNAPSHOT_ISOLATION OFF   
80. GO  
81.   
82. ALTER DATABASE [EmployeeDB] SET PARAMETERIZATION SIMPLE   
83. GO  
84.   
85. ALTER DATABASE [EmployeeDB] SET READ_COMMITTED_SNAPSHOT OFF   
86. GO  
87.   
88. ALTER DATABASE [EmployeeDB] SET HONOR_BROKER_PRIORITY OFF   
89. GO  
90.   
91. ALTER DATABASE [EmployeeDB] SET RECOVERY SIMPLE   
92. GO  
93.   
94. ALTER DATABASE [EmployeeDB] SET MULTI_USER   
95. GO  
96.   
97. ALTER DATABASE [EmployeeDB] SET PAGE_VERIFY CHECKSUM   
98. GO  
99.   
100. ALTER DATABASE [EmployeeDB] SET DB_CHAINING OFF   
101. GO  
102.   
103. ALTER DATABASE [EmployeeDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )   
104. GO  
105.   
106. ALTER DATABASE [EmployeeDB] SET TARGET_RECOVERY_TIME = 0 SECONDS   
107. GO  
108.   
109. ALTER DATABASE [EmployeeDB] SET READ_WRITE   
110. GO  

Create your MVC application

Open Visual Studio and select File >> New Project.

The "New Project" window will pop up. Select ASP.NET Web Application (.NET Framework), name your project, and click OK.



Now, new dialog will pop up for selecting the template. We are going to choose Web API template and click OK.



Add a Model class

In Solution Explorer, right click on Models folder > Add > Class > Name your class.



Employee.cs

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel.DataAnnotations.Schema;  
4. using System.Linq;  
5. using System.Web;  
6.   
7. namespace WebAPIODataApp.Models  
8. {  
9.     [Table("Employee")]  
10.     public class Employee  
11.     {  
12.         public int EmployeeID { get; set; }  
13.         public string FirstName { get; set; }  
14.         public string LastName { get; set; }  
15.         public string Gender { get; set; }  
16.         public string Designation { get; set; }  
17.         public int Salary { get; set; }  
18.         public string City { get; set; }  
19.         public string Country { get; set; }  
20.   
21.   
22.     }  
23. }  

Create a Controller

Now, we are going to create a Controller. Right click on the Controllers folder > Add > Controller> selecting Web API 2 OData v3 Controller with actions, using Entity Framework > click Add.



After clicking on Add button, window will pop up, as shown below.

We need to specify our Model class (in this case Employee.cs) and name for our Controller.



Finally, in order to add data context class, click on new data context > given a name for our new data context > Click Add.



The scaffolding adds two code files to the project.

• EmployeesController.cs - defines the Controller which implements the OData endpoint.
• EmployeeServContext.cs - ensures that our application is connected to the database.

EmployeesController.cs

1. using System;  
2. using System.Collections.Generic;  
3. using System.Data;  
4. using System.Data.Entity;  
5. using System.Data.Entity.Infrastructure;  
6. using System.Linq;  
7. using System.Net;  
8. using System.Net.Http;  
9. using System.Threading.Tasks;  
10. using System.Web.Http;  
11. using System.Web.Http.ModelBinding;  
12. using System.Web.Http.OData;  
13. using System.Web.Http.OData.Routing;  
14. using WebAPIODataApp.Models;  
15.   
16. namespace WebAPIODataApp.Controllers  
17. {  
18.       
19.     public class EmployeesController : ODataController  
20.     {  
21.         private EmployeeServContext db = new EmployeeServContext();  
22.   
23.         // GET: odata/Employees  
24.         [EnableQuery]  
25.         public IQueryable<Employee> GetEmployees()  
26.         {  
27.             return db.Employees;  
28.         }  
29.   
30.     }  

EmployeeServContext.cs

1. using System;  
2. using System.Collections.Generic;  
3. using System.Data.Entity;  
4. using System.Linq;  
5. using System.Web;  
6.   
7. namespace WebAPIODataApp.Models  
8. {  
9.     public class EmployeeServContext : DbContext  
10.     {  
11.         // You can add custom code to this file. Changes will not be overwritten.  
12.         //   
13.         // If you want Entity Framework to drop and regenerate your database  
14.         // automatically whenever you change your model schema, please use data migrations.  
15.         // For more information refer to the documentation:  
16.         // http://msdn.microsoft.com/en-us/data/jj591621.aspx  
17.       
18.         public EmployeeServContext() : base("name=EmployeeServContext")  
19.         {  
20.         }  
21.   
22.         public System.Data.Entity.DbSet<WebAPIODataApp.Models.Employee> Employees { get; set; }  
23.     }  
24. }  

Add EDM and route

First of all, we need to add connection string.

In Solution Explorer, open web.config file and add inside configuration element in the following section.

1. <connectionStrings>  
2.         <add name="EmployeeServContext" connectionString="Data Source=.;Initial Catalog=EmployeeDB;Integrated Security=True" providerName="System.Data.SqlClient" />  
3.   </connectionStrings>   

Note - You must specify the connection string from EmployeeDB database which has been created above.

Next step is - In Solution Explorer, select App_Start > double click on WebApiConfig.cs, then we should add the following code to the register method:

1. public static void Register(HttpConfiguration config)  
2.       {  
3.           ODataConventionModelBuilder builder = new ODataConventionModelBuilder();  
4.           builder.EntitySet<Employee>("Employees");  
5.           config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());  
6.   
7.       }  

Explanation - Register method ensures two things,

 

• Creates an EDM (Entity Data Model).
• Adds a route for OData service.

The EDM is used to create the service metadata document. At this level, we have two possibilities by using,

• The ODataConventionModelBuilder class which creates an EDM by using default naming conventions.
• The ODataModelBuilder class to create the EDM by adding properties, keys, and navigation properties.

In the last, we need to call the MapOdataRoute extension method for routing. We can conclude that our URI for Employees EntitySet is http://localhost:56262/odata/Employees.

Note - When you run our URI first time, the database table will be created as follow. Don’t forget add some records into Employee table for demo.



Consuming OData Service

In order to consume OData service, we will work to display data by using jqxGrid plugin.

Let’s GO.

Create controller

Now, we are going to create a controller. Right click on the controllers folder > Add > Controller> selecting MVC 5 Controller – Empty > click Add.

HomeController.cs

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Web;  
5. using System.Web.Mvc;  
6.   
7. namespace WebAPIODataApp.Controllers  
8. {  
9.     public class HomeController : Controller  
10.     {  
11.         public ActionResult Index()  
12.         {  
13.   
14.             return View();  
15.         }  
16.     }  
17. }  

Adding View

In HomeController, just right click on Index() action, select Add View and window will pop up. Write a name for your View. Finally, click Add.

Index cshtml

1. @{  
2.     ViewBag.Title = "Data Employees";  
3. }  
4.   
5. <h2> Data Employees </h2>  
6.   
7. <div id="gridEmployee" style="margin:20px auto;"></div>  
8.   
9. @section scripts {  
10.   
11.   
12.     <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/scripts/jquery-1.11.1.min.js"></script>  
13.   
14.     <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/jqx-all.js"></script>  
15.   
16.     <link rel="stylesheet" type="text/css" href="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/styles/jqx.base.css" />  
17.   
18.     <script type="text/javascript">  
19.   
20.     $(document).ready(function () {  
21.   
22.         // In this part, you need to prepare your data  
23.         var source =  
24.             {  
25.                 datatype: "json",  
26.   
27.                 // Here you will declare all fields that must be used in the grid  
28.                 datafields: [  
29.   
30.                { name: 'EmployeeID', type: 'number' },  
31.                { name: 'FirstName', type: 'string' },  
32.                { name: 'LastName', type: 'string' },  
33.                { name: 'Gender', type: 'string' },  
34.                { name: 'Designation', type: 'string' },  
35.                { name: 'Salary', type: 'number' },  
36.                { name: 'City', type: 'string' },  
37.                { name: 'Country', type: 'string' }  
38.   
39.                 ],  
40.                 // call the action which retrieve data employees in json format  
41.                 url: '/odata/Employees'  
42.             };  
43.   
44.         var dataAdapter = new $.jqx.dataAdapter(source);  
45.   
46.         // displaying data in the grid with jqxGrid  
47.         $("#gridEmployee").jqxGrid(  
48.             {  
49.                 width: 800,  
50.                 source: dataAdapter,  
51.                 pageable: true,  
52.                 sortable: true,  
53.   
54.                 columns: [  
55.   
56.                     { text: "Employee ID", datafield: "EmployeeID" },  
57.                     { text: "FirstName", datafield: "FirstName" },  
58.                     { text: "LastName", datafield: "LastName" },  
59.                     { text: "Gender", datafield: "Gender" },  
60.                     { text: "Designation", datafield: "Designation" },  
61.                     { text: "Salary", datafield: "Salary" },  
62.                     { text: "City", datafield: "City" },  
63.                     { text: "Country", datafield: "Country" }  
64.   
65.                 ]  
66.             });  
67.     });  
68.     </script>  
69. }  

Output - That’s all. Please send your feedback and queries in comments box.

For more Odata content follow:

https://psvdevelopers.blogspot.com/

•