WCF Data Services and OData for Oracle Database

Overview

Purpose

This tutorial covers developing WCF Data Services and Open Data Protocol (OData) applications for the Oracle Database using Visual Studio.

Time to Complete

Approximately 15 mins

Introduction

Microsoft WCF Data Services enables creating and consuming Web data services. To do so, it uses OData, which exposes data as URI-addressable resources, such as web site URLs. Entity Data Models (EDMs) via Microsoft Entity Framework can expose data through WCF Data Services and OData to allow EDMs to be more widely consumed.

In this tutorial, you learn how to expose Oracle Database data via WCF Data Services and OData through Oracle's Entity Framework support.

You will start by creating a new EDM from the HR schema. Next, you will create a WCF Data Service that uses OData to expose this EDM via the web. Last, you will run the Web application and execute URL queries to retrieve data from the database.

Prerequisites

Before starting this tutorial, you should:

  1. Install Microsoft Visual Studio 2013 or later with .NET Framework 4.5.1 or higher.

  2. Install Oracle Database 12c or later.

  3. Install Oracle Data Access Components (ODAC) 12.1.0.1.2 or later from OTN. The ODAC download includes Oracle Developer Tools for Visual Studio and ODP.NET that will be used in this lab.

  4. Install Internet Explorer 9 or later versions or any browser that supports modern Web protocols.

  5. Extract these files on to your working directory.

Creating a New Web Site Project

  1. Open Visual Studio. Click File. Select New > Web Site....

    Creating a New Website Project

  2. Select ASP.NET Web Forms Site and click OK. You have now created a new ASP.NET Web Forms Site project.

    Creating a New Website Project

Creating a new ADO.NET Entity Data Model

An EDM is required to expose Oracle data via WCF Data Services. In this section, you will create an EDM from the EMPLOYEES and DEPARTMENTS tables in the HR schema using the Entity Data Model Wizard.

  1. In the Solution Explorer, right-click on the newly created web site project. Select Add > Add New Item....

    Creating a new ADO.NET Entity Data Model

  2. Select ADO.NET Entity Data Model and click Add.

    Creating a new ADO.NET Entity Data Model

  3. Click Yes in the dialog box.

    Creating a new ADO.NET Entity Data Model

  4. In the Entity Data Model Wizard, select Generate from database and click Next.

    Creating a new ADO.NET Entity Data Model

  5. If you do not have a HR data connection, then click New Connection. Enter hr for both the User name and Password. Check the Save password checkbox. Select an available Data source name, such as ORCL. Click Test Connection. The Test Connection succeeded dialog box appears, click OK. In the Connection Properties, click OK.

    Creating a new ADO.NET Entity Data Model
    Creating a new ADO.NET Entity Data Model
    Creating a new ADO.NET Entity Data Model
    Creating a new ADO.NET Entity Data Model

    If you already have a HR connection, select the data connection, such as HR.ORCL in the screen shot. Select "Yes, include the sensitive data in the connection string". Click Next.

    Creating a new ADO.NET Entity Data Model

  6. Choose the version as Entity Framework 5.0 and click Next.

    Creating a new ADO.NET Entity Data Model

  7. Choose the Oracle tables to map to the EDM. Expand Tables > HR and select DEPARTMENTS and EMPLOYEES. Click Finish.

    Creating a new ADO.NET Entity Data Model

  8. The Model.edmx page will appear with the new EDM.

    Creating a new ADO.NET Entity Data Model

Creating a WCF Data Service with EDM data

In this section, you create a WCF Data Service. Then, you configure the data service to use the EDM to retrieve EMPLOYEES and DEPARTMENTS data from the database. Last, you will retrieve Oracle data using a browser by modifying the URL to perform queries. Underlying the data access is the use of OData.

  1. In the Solution Explorer, right-click on the web site project. Select Add > Add New Item....

    Creating a WCF Data Service with EDM data

  2. Scroll down and select WCF Data Service 5.6 from the Installed Visual C# Templates and click Add.

    Creating a WCF Data Service with EDM data

  3. Configure the WcfDataService.cs code page to retrieve data and provide access privileges. To retrieve data for WCFDataService : DataService, replace the section < /* TODO: put your data source class name here */ > with <Entities>. This is the EDM you just created.

    Creating a WCF Data Service with EDM data

    Uncomment the config.SetEntitySetAccessRule and replace "MyEntitySet" with a "*".

    Creating a WCF Data Service with EDM data
    Creating a WCF Data Service with EDM data

    Click to run the application.

    Creating a WCF Data Service with EDM data

  4. The Web site opens up with the URL: http://localhost:<portname>/WcfDataService.svc. You can view the entities that have been included in the web site.

    Creating a WCF Data Service with EDM data

  5. Before executing queries via the URL, make sure that Feed reading view has been disabled for Internet Explorer. Otherwise, it will treat the returned AtomPub encoded document as an XML feed instead of displaying the raw XML data.

    To do this:

    Click > Internet Options

    Creating a WCF Data Service with EDM data

  6. Select the Content tab. Select Settings from the Feeds and Web Slices section.

    Creating a WCF Data Service with EDM data

  7. Deselect Turn on feed reading view and click OK. Click OK in the Internet Options window.

    Creating a WCF Data Service with EDM data

  8. To execute some sample queries via the URL, open the Sample Queries - HR.txt from your working directory. The port number may be different from your project. Thus, use these sample queries as a guide, rather than copying and pasting them directly to the browser.

    To query the EMPLOYEES entity, add /EMPLOYEES to the URL. The web site will execute the query as shown below.

    Creating a WCF Data Service with EDM data

  9. To query for EMPLOYEE_ID = 100, copy /EMPLOYEES(100) from the Sample Queries - HR.txt and paste it in the URL. The web site will execute the query as shown below.

    Creating a WCF Data Service with EDM data

    Note: The web site returns the first name, last name, email and other details of the employee whose EMPLOYEE_ID=100.

  10. To query for the FIRST_NAME of EMPLOYEE_ID = 100, copy /EMPLOYEES(100)/FIRST_NAME from the Sample Queries - HR.txt and paste it in the URL. The web site will execute the query as shown below.

    Creating a WCF Data Service with EDM data

  11. To count the number of rows, copy /EMPLOYEES/$count from the Sample Queries - HR.txt and paste it in the URL. The web site will execute the query as shown below.

    Creating a WCF Data Service with EDM data

  12. To query for the first 2 employees, copy /EMPLOYEES?$top=2 from the Sample Queries - HR.txt and paste it in the URL. The web site will execute the query as shown below.

    Creating a WCF Data Service with EDM data

  13. To query for EMPLOYEES with the KING surname, copy /EMPLOYEES?$filter=LAST_NAME eq 'King' from the Sample Queries - HR.txt and paste it in the URL. The web site will execute the query as shown below.

    Creating a WCF Data Service with EDM data

  14. To query for EMPLOYEES paid more than 10,000 per pay period, copy EMPLOYEES?$filter=SALARY gt 10000 from the Sample Queries - HR.txt and paste it in the URL. The web site will execute the query as shown below.

    Creating a WCF Data Service with EDM data

  15. To stop debugging in Visual Studio, close the browser.

Summary

In this tutorial you have learned how to:

  • Create a new Web site project in Visual Studio.
  • Create a new ADO.NET Entity Data Model from an Oracle schema.
  • Create an Oracle WCF Data Service with EDM data and execute various queries with OData by modifying the URL.

Resources

Credits

  • Lead Curriculum Developer: Ashwin Agarwal
  • Other Contributors: Alex Keh, Christian Shay

To navigate this Oracle by Example tutorial, note the following:

Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.