OData Service using WCF Service
OData is a standardized protocol for creating and consuming data APIs. OData builds on core protocols like HTTP and commonly accepted methodologies like REST. The result is a uniform way to expose full-featured data APIs.
OData Service enables rich querying using URI query string parameters, For e.g.
http://ODataServiceTest/HumanResourceDataService.svc/Employees?$filter=JobTitle eq ‘Design Engineer’
More querying options are available here.
For this tutorial i am using Adventure Works Database, Download is available at codeplex, http://msftdbprodsamples.codeplex.com/releases/view/55330
Note: For below tutorial I used SQL Server 20008R2 with Visual Studio 2010 (.NET 4.0).
Below is the step-by-step tutorial to create a OData Service Class project.
- Download Adventure Works database and setup it in local SQL server.
- Create a new class library project “AdventureWorks.HumanResource.DataAccess”
- Delete Class1.cs file
- Right click on project in solution explorer and click Add –> New Item, Select Data from left tree and ADO.NET Entity Data Model from right pane. Give the name as “HumanResource.edmx” and click Add.
- In Entity Data Model wizard select Generate from database and click next.
- Create a new connection to Adventure Works database as below.Test connect and click OK to continue.
- You will see connection details as below
- Now option to choose database objects for OData, for this sample I am selecting only Tables (HumanResources schema), In later tutorial I will explore views and stored procedures for OData service.
- After clicking Finish button, VS will create Entity Diagram, for entities make sure you have proper relation between tables.
- Build the project, DataAccess project is ready with OData.
Now create a new WCF Data Service project and refer DataAccess dll in it.
- Go to solution explorer, Add –> New Project
- Delete Service1.svc & IService.cs files
- Add –> New Item… select WCF Service and named it as “HumanResourceDataService.svc”
- Add “AdventureWorks.HumanResource.DataAccess.dll” using Add Reference…
- Add below dlls reference (you can add these dlls using Manage NuGet packages)
- Microsoft.Data.Edm.dll
- Microsoft.Data.OData
- Microsoft.Data.Services
- Microsoft.Data.Services.Client
- System.Spatial.dll
- HumanResourceDataService.svc.cs code is as below:
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Runtime.Serialization;
5: using System.ServiceModel;
6: using System.Text;
7: using AdventureWorks.HumanResource.DataAccess;
8: using System.Data.Services;
9: using System.Data.Services.Common;
10: 11: namespace AdventureWorks.HumanResource.WCFService
12: {13: public class HumanResourceDataService : DataService<AdventureWorks2008R2Entities>
14: {15: public static void InitializeService(DataServiceConfiguration config)
16: {17: config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
18: config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3; 19: } 20: } 21: }7. HumanResourceDataService.svc code is as below:
1: <%@ ServiceHost Language="C#" Debug="true"
2: Factory="System.Data.Services.DataServiceHostFactory, System.Data.Services, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
3: Service="AdventureWorks.HumanResource.WCFService.HumanResourceDataService" %>
8. Add new endpoint for data service with webHttpBinding, code snippet will be as below:
1: <?xml version="1.0" encoding="utf-8"?>
2: <configuration> 3: <connectionStrings>4: <add name="AdventureWorks2008R2Entities" connectionString="metadata=res://*/HumanResourceModel.csdl|res://*/HumanResourceModel.ssdl|res://*/HumanResourceModel.msl;provider=System.Data.SqlClient;provider connection string="data source=RAJNEESH\SQLEXPRESS;initial catalog=AdventureWorks2008R2;integrated security=True;multipleactiveresultsets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
5: </connectionStrings> 6: <system.web>7: <compilation debug="true" targetFramework="4.0" />
8: </system.web> 9: <system.serviceModel> 10: <behaviors> 11: <serviceBehaviors> 12: <behavior>13: <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->
14: <serviceMetadata httpGetEnabled="true" />
15: <!-- To receive exception details in faults for debugging purposes, set the value below to true. Set to false before deployment to avoid disclosing exception information -->
16: <serviceDebug includeExceptionDetailInFaults="false" />
17: </behavior> 18: </serviceBehaviors> 19: </behaviors> 20: <services>21: <service name="KPMG.eAudIT.QueueService.WASHost.HumanResourceDataService">
22: <endpoint address="" binding="webHttpBinding" bindingConfiguration="webhttpBinding" contract="System.Data.Services.IRequestHandler">
23: </endpoint> 24: </service> 25: </services>26: <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
27: <bindings> 28: <webHttpBinding>29: <binding name="webhttpBinding" maxBufferSize="2147483647" maxBufferPoolSize="2147483647" maxReceivedMessageSize="2147483647" receiveTimeout="01:00:00" sendTimeout="01:00:00">
30: <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647" maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647" />
31: <security mode="TransportCredentialOnly">
32: <transport clientCredentialType="Windows" />
33: </security> 34: </binding> 35: </webHttpBinding> 36: </bindings> 37: </system.serviceModel> 38: <system.webServer>39: <modules runAllManagedModulesForAllRequests="true" />
40: </system.webServer> 41: </configuration> 9. WCF service is ready to host, host the service in local iis or run directly from visual studio and see the result as below.
10. Use LINQPad to see the results and query the data.
11. Source code download from here