Announcing the AdventureWorks OData Feed sample
Overview
As a continuation of my previous blog post, we created a live AdventureWorks OData feed at https://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 https://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
- Download the sample from CodePlex.
- Attach the AdventureWorks2012 database. The AdventureWorks2012 database can be downloaded from https://msftdbprodsamples.codeplex.com/releases/view/93587
- From Microsoft SQL Server Management Studio, run \AdventureWorks.OData.Service\SQL Scripts\Views.sql to create the OData feed SQL views.
- Open \AdventureWorks.OData.Service\AdventureWorks.OData.Service.sln in Visual Studio 2012.
- In Solution Explorer, select AdventureWorks.svc.
- Press F5 to run the service on https://localhost:1234/AdventureWorks.svc/.
AdventureWorks OData Service Resources
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
QueryFeed Url
https://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
QueryFeed Url
https://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
QueryFeed Url
https://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
QueryFeed Url
https://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
QueryFeed Url
https://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 |
Comments
- Anonymous
September 09, 2012
This is indeed a great post and I have made it a sticky on the SQL Server Samples and Community Projects forum (social.msdn.microsoft.com/.../threads). Thanks for sharing