WCF Data Services and OData for Oracle Database

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

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

Overview

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 website 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:

.

Install Microsoft Visual Studio 2010 with .NET Framework 4 or higher.

.

Install Oracle Database 10.2 or later or Oracle Database XE.

.

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

.

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

.

Extract these files on to your working directory

Creating a New Website Project

.

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

 

.

Select ASP.NET Web Site and click OK. You have now created a new ASP.NET Web Site 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.

.

In the Solution Explorer, right-click on the newly created website project. Select Add New Item.

 

 

.

Select ADO.NET Entity Data Model and click Add.


.

Click Yes in the dialog box.


.

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

 

.

If you do not have a HR data connection, then click New Connection. Select an available Data source name, such as ORCL. Enter HR for the User Name and enter a password. Click OK.

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.


.

Choose the Oracle table to map to the EDM . Expand the Tables node and select EMPLOYEES and DEPARTMENTS. Click Finish.

 

.

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

 

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.

.

Right-click the website in the Solution Explorer window to select Add New Item

 

.

Select WCF Data Service from the Installed Templates and click Add.


.

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 <Model.Entities>. This is the EDM you just created.

Uncomment the config.SetEntitySetAccessRule and replace "MyEntitySet" with a "*". Click to run the application.


.

The Website opens up with the address bar having the following URL: http://localhost:<portname>/<website project name>/


.

In the URL, enter /WcfDataService.svc after the name of the WebSite in order to view the Entities that have been included.

 

.

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:

Select Tools > Internet Options


.

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


.

Deselect "Turn on feed reading view" and click OK.


.

To execute some sample queries via the URL, open the Sample Queries - HR.txt from your working directory. The port number and website project name may be different from your projects. 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 website will execute the query as shown below.


.

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


.

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 website will execute the query as shown below.


.

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


.

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


.

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


.

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


.

To perform a join on EMPLOYEES and DEPARTMENTS on EMPLOYEE_ID = 100, copy /EMPLOYEES(100)?$expand=DEPARTMENTS from the Sample Queries - HR.txt and paste it in the URL. The website will execute the query as shown below.


Summary

In this tutorial you have learned how to:

 

Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved