AdventureWorksOData sample-Named Resource Streams
Overview
As a continuation of myprevious blog post,the AdventureWorks QueryFeed OData sample now shows how to implementNamed Resource Streams to stream AdventureWorks product images.In addition, I added the ability to select Named Resource Streams (product images) within a business workflow to the QueryFeed activity, and render a stream in a Word document.
The AdventureWorksQueryFeedOData sample illustrates an end to end OData workflow with Office scenario. Starting with SQL Server views, the sample shows how to expose selective AdventureWorks views as an OData service. The AdventureWorks product catalog contains two product images: LargePhoto and ThumbNailPhoto. By implementing WCF Data Services 5.0 Named Resource Streams, the sample shows how to stream LargePhoto and ThumbNailPhoto into a Word document. You can select named resources in the QueryFeed workflow activity and select the defaultnamed resource that the client host will render in the TablePartPublisher workflow activity.
Source Code
AdventureWorks OData Feed (
OData QueryFeed workflow activity (
Requirements
- Visual Studio .NET 2012
- .NET Framework 4.0 (QueryFeed activity).
- .NET Framework 4.5 (AdventureWorks2012 OData Service).
- WCF Data Services 5.0 for OData V3 located at
- Open XML SDK located at
- Microsoft Excel 2010 or above (optional for ExcelAddin).
- Microsoft Word 2010 or above (optional for WordAddin).
Integrated Technologies
The sample shows how to integrate the following technologies to address the business scenario of consuming any OData service in an Office application. The sample uses the AdventureWorks database as an example line-of-business database with selective views exposed as OData resources.
- SQL Server 2008R2 or above including views with joins, ROW_NUMBER() OVER, functions, xpath queries, and configuring a NT AUTHORITY\NETWORK SERVICE login and role for IIS.
- Windows Workflow 4.0 including custom activities, activity designers, OData schema aware expression lists, practical use of ModelItem and ModelItemTree, child activities, XML LINQ projections into entity properties, a custom workflow designer, and variables.
- Office (Excel and Word) including hosting an OData related workflow, consuming workflow activity states using tracking participants, extension methods, embedding OData properties into Content Controls, and Open XML.
User stories addressed in the blog series
This blog series addressed a partial list of user stories. More detailed user stories would be defined in an agile production application.
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.
As a backend developer, I want the AdventureWorks OData service to expose multiple versions of the product photo so that client applications can have a thumbnail view and a detail view with large photos.
As a Developer, I want to create a product catalog view model so that other developers can bind device views to the view model.
As a developer, I want to create an OData feed activity so that an IT Analyst can consume any AdventureWorks OData feed within a business workflow.
As an Office developer, I want to create an Addin that consumes the AdventureWorks OData service product photos so that our marketing department can insert photos into product related documents.
How to install the sample
1)Install WCF Data Services 5.0 for OData V3 (WcfDataServices.exe ) located at
2)Install OpenXMLSDKv2.msi located at
3)Open \AdventureWorks.OData.Service\AdventureWorks.OData.Service.sln in Visual Studio 2012.
4)Build the AdventureWorks.OData.Servicesolution.
5)Open \Microsoft.Samples.SqlServer.OData\Microsoft.Samples.SqlServer.Workflows.OData.sln in Visual Studio 2012.
6)Build the solution.
7)For this iteration of the sample, you will need to install the activity designer assembly into the GAC. See How to install the activity designer assemblies into the GAC.
How to install the activity designer assemblies into the GAC
From the Visual Studio Command Prompt, enter:
gacutil /i {FullPath}\Microsoft.Samples.SqlServer.Activities.dll
For Example:
gacutil /i C:\Projects\Microsoft.Samples.SqlServer.OData\Common\Activities\bin\Debug\Microsoft.Samples.SqlServer.Activities.dll
How to remove Microsoft.Samples.SqlServer.Activities from the GAC
From the Visual Studio Command Prompt, enter:
gacutil /u Microsoft.Samples.SqlServer.Activities
Note
If you get the Visual Studio error below, then you will need to install Microsoft.Samples.SqlServer.Activities into the GAC. A future release should resolve this issue.
How to run the sample
The AdventureWorks OData service sample is hosted on the ASP.NET Development Server. You can also consume the AdventureWorks OData feed from
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
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
AdventureWorks OData Service Resources
Named Resource Streams and product photos
A data service can expose binary data. Starting with version 3 of OData, an entity can have multiple related resource streams, which are accessed by name. The sample illustrates how to implement IDataServiceStreamProvider2 to expose AdventureWorks ThumbNailPhoto and LargePhoto product images.
Configuring a data service to support the streaming of binary data requires four steps.
- Attribute the targetentity that has resource streams.
- Implement the following stream provider interfaces:
- IDataServiceStreamProvider2 – required only for named resource streams.
- IDataServiceStreamProvider – required to support both kinds of binary resource streams.
- Define a data service that implements the IServiceProvider interface. The data service uses the GetService implementation to access the streaming data provider implementation. This method returns the appropriate streaming provider implementation.
- Enable large message streams in the Web application configuration and access to binary resources on the server or in a data source.
Step 1 - Attribute the target entity that has resource streams.
The AdevntureWorks sample attributes vProductCatalog. See AdventureWorksModel.Extensions.cs within the code sample.
using System.Data.Services.Common;
namespace Microsoft.Samples.SqlServer.AdventureWorksService
{
[NamedStream("LargePhoto")]
[NamedStream("ThumbNailPhoto")]
publicpartialclass vProductCatalog { }
}
Step 2 - Implement the following stream provider interfaces:
- IDataServiceStreamProvider2 – required only for named resource streams.
- IDataServiceStreamProvider – required to support both kinds of binary resource streams.
//The sample uses .NET Framework 4.5 SqlDataReader.GetStream();
private Stream ProductPhoto(int productID, string columnName)
{
Stream productPhoto = null;
using (SqlConnection connection = new SqlConnection
(Properties.Settings.Default.Setting.ToString()))
{
using (SqlCommand command = connection.CreateCommand())
{
// Setup the command
command.CommandText =
string.Format("SELECT {0} FROM Production.vProductCatalogImages WHERE ProductID=@ProductID", columnName);
command.CommandType = CommandType.Text;
// Declare the parameter
SqlParameter paramID = new SqlParameter("@ProductID", SqlDbType.Int);
paramID.Value = productID;
command.Parameters.Add(paramID);
connection.Open();
try
{
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
reader.Read();
if (reader.HasRows)
productPhoto = reader.GetStream(0);
}
}
catch (SqlException ex)
{
//Log the SqlException, such as Invalid column name, in a production application
}
return productPhoto;
}
}
}
Step 3 - Define a data service that implements the IServiceProvider interface.
publicobjectGetService(Type serviceType)
{
if(serviceType == typeof(IDataServiceStreamProvider2))
{
//Return the stream provider to the data service.
returnnew ProductCatalogResourceProvider();
}
returnnull;
}
Step 4 - Enable large message streams in the Web application configuration.
When you create a data service in an ASP.NET Web application, Windows Communication Foundation (WCF) is used to provide the HTTP protocol implementation. By default, WCF limits the size of HTTP messages to only 65K bytes. To stream large binary data to and from the data service, you configure the Web application to enable large binary files and to use streams for transfer. To do this, add <services> element and <bindings> elementto the application's Web.config file:
system.serviceModel
services
<!--The name of the service-->
servicename="AdventureWorks_ODataService.AdventureWorks">
<!--you can leave the address blank or specify your end point URI-->
endpointbinding="webHttpBinding"bindingConfiguration="higherMessageSize"contract="System.Data.Services.IRequestHandler"/>
</service
</services
bindings
webHttpBinding
<!-- configure the maxReceivedMessageSize value to suit the max size of
the request (in bytes) you want the service to recieve-->
bindingname="higherMessageSize"maxReceivedMessageSize="500000"/>
</webHttpBinding
</bindings
</system.serviceModel
See AdventureWorks.OData.Service.sln for a complete sample service. For more information, see Streaming Provider (WCF Data Services).
Example QueryFeed workflows
The sample includes three example workflows that consume the AdventureWorks Odata service: ProductCatalog Activity Example.xaml, ManufacturingInstructions Activity Example.xaml, and WorkOrderRouting Activity Example.xaml.
How to run the ProductCatalog Activity Example
To run the ProductCatalog activity example, you will need to start the sample AdventureWorks OData feed. After the AdventureWorks OData service starts, you can run the ExcelAddin or WordAddin project.
To run the service on a local ASP.NET Development Server
1)Open \Microsoft.Samples.SqlServer.OData\AdventureWorks.OData.Service\AdventureWorks.OData.Service.sln in Visual Studio 2012.
2)In Solution Explorer, select AdventureWorks.svc.
3)Press F5 to run the service on
To run the ExcelAddin project
1)Open \Microsoft.Samples.SqlServer.OData\Microsoft.Samples.SqlServer.Workflows.OData.sln in Visual Studio 2012.
2)Right click the Microsoft.Samples.SqlServer.ExcelAddIn project.
3)Click Set as Startup Project.
4)Press F5 to run the sample ExcelAddin.
5)Click the Developer ribbon tab.
6)Click the Configuration button.
7)Select the path containing ProductCatalog Activity Example.xamlby clicking the (…) button next to the Workflow property. Click the Close button.
Note
The example QueryFeed workflow path is \Microsoft.Samples.SqlServer.OData\Configuration
8)Select a cell.
9)Click the Get Feed ribbon button and select ProductCatalog Activity Example.xaml.
10)The example QueryFeed workflow renders Product Catalog entity properties as an Excel ListObject table.
To run the WordlAddin project
1)Open \Microsoft.Samples.SqlServer.OData\Microsoft.Samples.SqlServer.Workflows.OData.sln in Visual Studio 2012.
2)Right click the Microsoft.Samples.SqlServer.WordAddIn project.
3)Click Set as Startup Project.
4)Press F5 to run the sample WordAddin.
5)Click the Developer ribbon tab.
6)Click the Configuration button.
7)Select the path containing ProductCatalog Activity Example.xaml by clicking the (…) button next to the Workflow property. Click the Close button.
Note
The example QueryFeed workflow path is \Microsoft.Samples.SqlServer.OData\Configuration
8)Click the Get Feed ribbon button and select ProductCatalog Activity Example.xaml.
9)The example QueryFeed workflow renders Product Catalog entity properties as aWord table using Open Xml.
How to render images in the sample Word document
After running the sample QueryFeed workflow, the Word addin host embeds an OData resource Uri in each ProductID content control. The resource Uri is obtained from the TablePartPublisher default resource.
The sample illustrates two methods to render an AdventureWorks image from a content control tag containing a resource uri: Double click the ProductID Content Control or right click the the ProductID Content Control and select a named resource. Named resource context buttons are obtained from the QueryFeed Select expression.
To insert an OData resource into the sample Word document
After running the ProductCatalog Activity Example.xaml, right click on a ProductID Content Control and select LargePhoto or ThumbNailPhoto.
How to create a QueryFeed workflow using the hosted Windows Workflow designer
You can open the custom Workflow designer as a stand alone Windows application, from within Excel or from within Word. After following these steps, you will be able to create a new OData QueryFeed workflow, and run the OData workflow to render entity properties in Excel and Word.
When rendering entity properties in Word, you can stream a product catalog ThumbNailPhoto or LargePhoto directly into Word.
To create an AdventureWorks product catalog workflow
1)Open \Microsoft.Samples.SqlServer.OData\Microsoft.Samples.SqlServer.Workflows.OData.sln in Visual Studio 2012.
2)Right click the Microsoft.Samples.SqlServer.ExcelAddIn or Microsoft.Samples.SqlServer.WordAddIn project.
3)Click Set as Startup Project.
4)Press F5 to run the sample ExcelAddin or WordAddin.
5)Click the Developer ribbon tab.
6)Click the Design button.
7)Click the New button.
8)Click the Activities button.
9)Drag a QueryFeed activity onto the default Sequence activity.
10)Select ProductCatalog for the ResourceComboBox expression.
11)Enter 15 for the Top expression
12)Drag a Filteractivity onto theDrop Filter Here drop zone in the QueryFeedactivity.
13)Select CultureID, Eq, "En", And on the Filteractivity.
14)Drag a Filteractivity onto theDrop Filter Here drop zone in the QueryFeedactivity.
15)Select ListPrice, Gt, 1000on the Filteractivity.
16)Collapse the two Filter activities.
17)Select ListPricefor Order Byexpression.
18)Select ThumbNailPhoto, LargePhoto, ProductID, Description, and ListPrice for Select expression.
19)Drag an EntityProperties activity under the QueryFeed activity.
20)Enter "EntityProperties" for the Properties expression.
21)You will see a Type 'EntityProperty' is not defined error. This error demonstrates a Workflow Designer IValidationErrorService.
22)Click Imports, enter "Microsoft.Samples.SqlServer.Activities.Designers.OData" in the Enter or Select namespace ComboBox control.Click Imports to collapse the namespace list.
23)Drag a TablePartPublisher activity onto the EntityProperties ContentPartdrop zone.
24)Select a Style such as "Medium List 2 - Accent 5" for Word or "TableStyleMedium24" for Excel.
25)Select a default Resource such as "ThumbNailPhoto". The default resource is used by the client hosting the workflow to render one of many named resources.
26)Click the Run button.
The new workflow application produces a fully qualified OData uri, and renders entity properties in a Word table using the TablePartPublisher style. In addition, the default resource uri is embedded into the ProductID Content Control. Double click or right click the ProductID Content Control to render an AdventureWorks image stream.
Word table produced from new OData workflow application
ServiceQueryString= eq 'en' and ListPrice gt 1000&$top=15&$orderby=ListPrice asc&$select=ThumbNailPhoto,LargePhoto,ProductID,Description,ListPrice
Final OData workflow application
Code Snippets
An upcoming blog post or article will discuss the source code used for this sample. For now, here are some code snippets.
Production.vProductCatalog
CREATEVIEW [Production].[vProductCatalog]
AS
SELECT ROW_NUMBER() OVER (ORDERBY [ProductID] DESC) AS ID, P.ProductID, P.ProductNumber, P.Name AS ProductName, PM.Name AS ProductModel,
PC.Name AS ProductCategory, PS.Name AS ProductSubcategory, PD.Description, PMPDCL.CultureID, P.Color, P.Size, P.Weight, P.ListPrice
FROM Production. Product AS P INNERJOIN
Production.ProductSubcategory AS PS INNERJOIN
Production.ProductCategory AS PC ON PS.ProductCategoryID = PC.ProductCategoryID ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNERJOIN
Production.ProductDescription AS PD INNERJOIN
Production.ProductModel AS PM INNERJOIN
Production.ProductModelProductDescriptionCulture AS PMPDCL ON PM.ProductModelID = PMPDCL.ProductModelID ON
PD.ProductDescriptionID = PMPDCL.ProductDescriptionID ON P.ProductModelID = PM.ProductModelID;
AdventureWorksModel.Extensions.cs
using System.Data.Services.Common;
namespace Microsoft.Samples.SqlServer.AdventureWorksService
{
[NamedStream("LargePhoto")]
[NamedStream("ThumbNailPhoto")]
publicpartialclass vProductCatalog { }
}
publicobject GetService(Type serviceType)
{
if(serviceType == typeof(IDataServiceStreamProvider2))
{
//Return the stream provider to the data service.
returnnew ProductCatalogResourceProvider();
}
returnnull;
}
private Stream ProductPhoto(int productID, string columnName)
{
Stream productPhoto = null;
using (SqlConnection connection =
new SqlConnection
(Properties.Settings.Default.Setting.ToString()))
{
using (SqlCommand command = connection.CreateCommand())
{
// Setup the command
command.CommandText =
string.Format
("SELECT {0} FROM Production.vProductCatalogImages WHERE
ProductID=@ProductID", columnName);
command.CommandType = CommandType.Text;
// Declare the parameter
SqlParameter paramID = new SqlParameter("@ProductID",
SqlDbType.Int);
paramID.Value = productID;
command.Parameters.Add(paramID);
connection.Open();
try
{
using (SqlDataReader reader =
command.ExecuteReader
(CommandBehavior.CloseConnection))
{
reader.Read();
if (reader.HasRows)
productPhoto = reader.GetStream(0);
}
}
catch (SqlException ex)
{
//In Log the SqlException, such as Invalid column
name, in a production application
}
return productPhoto;
}
}
}
public Stream GetReadStream(object entity, ResourceProperty resourceProperty, string etag, bool? checkETagForEquality, DataServiceOperationContext operationContext)
{
vProductCatalog image = entity as vProductCatalog;
if (checkETagForEquality != null)
{
// This stream provider implementation does not support
// ETag headers for media resources. This means that we do not track
// concurrency for a media resource and last-in wins on updates.
thrownew DataServiceException(400, "This sample service does not
support the ETag header for a media resource.");
}
if (image == null)
{
thrownew DataServiceException(500, "Internal Server Error.");
}
// Return a stream that contains the requested ThumbnailPhoto or
LargePhoto
return ProductPhoto(image.ProductID, resourceProperty.Name);
}
How to get the selected model item
privatevoid ActivityDesigner_Loaded(object sender, RoutedEventArgs e)
{
selectedModelItem = (sender as TablePartPublisherDesigner).ModelItem;
}
How to Named Resources expression items
privatevoid resourceCombobox_DropDownOpened(object sender, EventArgs e)
{
ComboBox resourceCombobox = (sender as ComboBox);
QueryFeed queryFeed = null;
resourceCombobox.Items.Clear();
//Get Sequence parent
ModelItem sequence = selectedModelItem.GetParent(typeof(Sequence));