AdventureWorks OData sample with Named Resource Streams
As a continuation of my previous blog post, the AdventureWorks QueryFeed OData sample now shows how to implement Named 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 AdventureWorks QueryFeed OData 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 default named resource that the client host will render in the TablePartPublisher workflow activity.
By providing feedback on this blog, or sending me an email, you can help define any part of the sample. My email address is derrickv@microsoft.com.
The source code is available at CodePlex.
- AdventureWorks OData Feed (https://msftdbprodsamples.codeplex.com/releases/view/94064).
- OData QueryFeed workflow activity (https://msftdbprodsamples.codeplex.com/releases/view/94486).
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.
The attached document explains more about the latest iteration. Also, I added a section on creating a new QueryFeed OData workflow application using the sample workflow designer.
The sample uses several LINQ queries to combine elements to form IEnumerable<EntityProperty>. I would like to use fewer LINQ queries, but ran out of time. The QueryFeed.Execute() method shows how various metadata elements are combined to form an IEnumerable<EntityProperty>.
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 five steps.
- Attribute the target entity that has resource streams.
- Implement IDataServiceStreamProvider2 – required only for named resource streams.
- Implement IDataServiceStreamProvider – required to support both kinds of binary resource stream.
- 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")]
public partial class vProductCatalog { }
}
Step 2 - Implement IDataServiceStreamProvider2 – required only for named resource streams.
public class ProductCatalogResourceProvider : IDataServiceStreamProvider2
{
…
public Stream GetReadStream(object entity, ResourceProperty resourceProperty, string etag, bool? checkETagForEquality, DataServiceOperationContext operationContext)
{
vProductCatalog image = entity as vProductCatalog;
if (image == null)
{
throw new DataServiceException(500, "Internal Server Error.");
}
// Return a stream that contains the requested ThumbnailPhoto or
LargePhoto
return ProductPhoto(image.ProductID, resourceProperty.Name);
}
//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 - Implement IDataServiceStreamProvider – required to support both kinds of binary resource stream. The sample only implements IDataServiceStreamProvider2.GetReadStream().
Step 4 - Define a data service that implements the IServiceProvider interface.
public object GetService(Type serviceType)
{
if(serviceType == typeof(IDataServiceStreamProvider2))
{
//Return the stream provider to the data service.
return new ProductCatalogResourceProvider();
}
return null;
}
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> element to the application's Web.config file:
<system.serviceModel>
<services>
<!--The name of the service-->
<service name="AdventureWorks_ODataService.AdventureWorks">
<!--you can leave the address blank or specify your end point URI-->
<endpoint binding="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-->
<binding name="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).
QueryFeed activity
The sample QueryFeed activity now allows you to select Named Resources.
In addition, you can select a default resource to render in the TablePartPublisher activity.
Finally, as mentioned in my previous post, the attached document details how to edit a QueryFeed workflow using a hosted Windows Workflow designer. After creating a product catalog workflow application, you can render a Word table. The images in the sample table were rendered using Named Resource Streams.
