Overview

As a continuation of my previous blog post, we created a live AdventureWorks OData feed at http://services.odata.org/AdventureWorksV3/AdventureWorks.svc. The AdventureWorks OData service exposes resources based on specific SQL views. The SQL views are a limited subset of the AdventureWorks database that results in several consuming scenarios:

  • CompanySales
  • Documents
  • ManufacturingInstructions
  • ProductCatalog
  • TerritorySalesDrilldown
  • WorkOrderRouting

We will be iterating on this sample, so expect it to improve over time. For instance, the Documents feed currently exposes the document entity type property as an Edm.Binary type. An upcoming iteration will show how to implement Named Resource Streams for the Documents.Document entity type property.

User Story

This sample iteration, on our CodePlex site, addresses the following user story:

As a backend developer, I want to only allow views of the AdventureWorks2012 database to be exposed as an OData public resource so that the underlying schema can be modified without affecting the service.

Source Download

How to install the sample

You can consume the AdventureWorks OData feed from http://services.odata.org/AdventureWorksV3/AdventureWorks.svc. You can also consume the AdventureWorks OData feed by running the sample service from a local ASP.NET Development Server.

To run the service on a local ASP.NET Development Server

  1. Download the sample from CodePlex.
  2. Attach the AdventureWorks2012 database. The AdventureWorks2012 database can be downloaded from http://msftdbprodsamples.codeplex.com/releases/view/93587
  3. From Microsoft SQL Server Management Studio, run \AdventureWorks.OData.Service\SQL Scripts\Views.sql to create the OData feed SQL views.
  4. Open \AdventureWorks.OData.Service\AdventureWorks.OData.Service.sln in Visual Studio 2012.
  5. In Solution Explorer, select AdventureWorks.svc.
  6. Press F5 to run the service on http://localhost:1234/AdventureWorks.svc/.

AdventureWorks OData Service Resources

image

Iteration 2 will address the following user story:

As a frontend developer, I want to consume the AdventureWorks OData feed so that I can use entity data within a business workflow.

The sample will show how to create a QueryFeed workflow activity that can consume any OData feed url and return an enumeration of entity properties. The sample further shows how to create an EntityProperties activity which allows a user designing a workflow to drop a TablePartPublisher into the activity. A TablePartPublisher can be consumed by any client to render entity properties. For example, a Word Add-in can consume EntityProperties and a TablePartPublisher to render an Open XML table. Iteration 2 will show how this is done.

I have most of the code for iteration 2 complete, but not quite ready to publish. I plan to publish iteration 2 in about two weeks. For now, I’ll show you a sample of each AdventureWorks OData resource. Each example table was created by running the custom activities from within Word.

My next blog post will dive into how to create OData workflow activates. Custom activity designers that consume OData metadata and use LINQ to project entity xml into entity classes will be discussed. The metadata entity classes are used to provide expression editor items.

CompanySales Example

image

QueryFeed Url

http://localhost:1234/AdventureWorks.svc/CompanySales?$top=2&$orderby=OrderYear asc

Result

ProductCategory

ProductSubCategory

OrderYear

OrderQtr

Sales

Accessories

Bike Racks

2007

Q4

60883.2000

Accessories

Bike Racks

2007

Q3

75920.4000

  

ManufacturingInstructions Example

image

QueryFeed Url

http://localhost:1234/AdventureWorks.svc/ManufacturingInstructions?$top=2&$select=ProductName,Instructions,SetupHours

Result

ProductName

Instructions

SetupHours

HL Touring Frame

Adventure Works CyclesFR-210B Instructions for Manufacturing HL Touring Frame. Summary: This document contains manufacturing instructions for manufacturing the HL Touring Frame, Product Model 7. Instructions are work center specific and are identified by Work Center ID. These instructions must be followed in the order presented. Deviation from the instructions is not permitted unless an authorized Change Order detailing the deviation is provided by the Engineering Manager.

0.5000

HL Touring Frame

Adventure Works CyclesFR-210B Instructions for Manufacturing HL Touring Frame. Summary: This document contains manufacturing instructions for manufacturing the HL Touring Frame, Product Model 7. Instructions are work center specific and are identified by Work Center ID. These instructions must be followed in the order presented. Deviation from the instructions is not permitted unless an authorized Change Order detailing the deviation is provided by the Engineering Manager.

0.5000

ProductCatalog Example

image

QueryFeed Url

http://localhost:1234/AdventureWorks.svc/ProductCatalog?$filter=CultureID eq 'en' and ProductSubcategory eq 'Pedals'&$top=2&$select=ProductName,ProductSubcategory,Description,ListPrice

Results

ProductName

ProductSubcategory

Description

ListPrice

Touring Pedal

Pedals

A stable pedal for all-day riding.

80.9900

LL Road Pedal

Pedals

Clipless pedals - aluminum.

40.4900

LL Mountain Pedal

Pedals

Expanded platform so you can ride in any shoes; great for all-around riding.

40.4900

ML Road Pedal

Pedals

Lightweight aluminum alloy construction.

62.0900

ML Mountain Pedal

Pedals

Lightweight, durable, clipless pedal with adjustable tension.

62.0900

HL Mountain Pedal

Pedals

Stainless steel; designed to shed mud easily.

80.9900

HL Road Pedal

Pedals

Top-of-the-line clipless pedals with adjustable tension.

80.9900

TerritorySalesDrilldown Example

image

QueryFeed Url

http://localhost:1234/AdventureWorks.svc/TerritorySalesDrilldown?$filter=SalesPersonID eq 275&$top=2

Result

TerritoryName

SalesPersonID

EmployeeFirstName

EmployeeLastName

SalesOrderNumber

Total

Northeast

275

Michael

Blythe

SO43670

6893.2549

Northeast

275

Michael

Blythe

SO43673

4216.0258

  

WorkOrderRouting Example

image

QueryFeed Url

http://localhost:1234/AdventureWorks.svc/WorkOrderRouting?$top=2&$select=WorkOrderID,ProductNumber,ProductName,ScheduledStartDate,ActualStartDate,ScheduledEndDate,ActualEndDate

Result

WorkOrderID

ProductName

ScheduledStartDate

ActualStartDate

36651

LL Mountain Seat Assembly

2007-07-04T00:00:00

2007-07-04T00:00:00

36651

LL Mountain Seat Assembly

2007-07-04T00:00:00

2007-07-04T00:00:00