Accessing images and other binary files in blob fields through SharePoint BDC
There are many applications that store binary data in blob or varbinary fields of a database, including pictures, Office documents, PDFs and other types of documents. Recently, a customer had the need to display, in SharePoint, an image stored in an Oracle database. They were already indexing and displaying the other fields from the database. Since there is no out-of-box support for displaying blobs in SharePoint from the Business Data Catalog (BDC), I decided to see if I could find some way to retrieve the data in a format that could be used by SharePoint.
I discovered that you can get to blob or varbinary fields through the BDC as byte arrays (type byte[] in C#). Once you have this byte array, you can output it directly to the browser, or any other streaming end point as raw data.
In this post, I will walk through how this can be done and how it can be used to display binary documents in SharePoint. The steps are: 1) Creating the BDC application definition file, 2) Creating the ASPX page class, and 3) Displaying the Image on a page.
Products used:
· Microsoft Office SharePoint Server 2007 (MOSS 2007)
· Microsoft Visual Studio 2008 C#
· Microsoft SQL Server 2005
Step 1 – Creating the BDC application definition file
In this article I am using the always-handy AdventureWorksDW sample database that comes with SQL Server 2005. To set up the initial BDC connection to this database, use the BDC Application Definition file from AdventureWorksDW SQL Server 2005 Sample at https://msdn2.microsoft.com/en-us/library/ms494876.aspx.
NOTE: there is a link to a walkthrough from that page, but the walkthrough applies to the AdventureWorks database, while the 2005 update applies to the AdvantureWorksDW database (the data warehouse). The steps still work with some minor changes, but just follow the steps to the point where you have a Products list on a page (select the Key column instead of the ID column for display – I also added the Name column to the list).
NOTE: To make this work with Oracle and other databases, see How to: Adapt the Samples to Connect to Oracle and Other Databases at https://msdn2.microsoft.com/en-us/library/aa673236.aspx.
Once you have the BDC connection working and SharePoint is able to show the Products list, we are ready to add a method to the BDC Application Definition File that will retrieve the blob.
Add a new method called GetProductImage on the Product entity with an instance called GetProductImageInstance of type ViewAccessor. This method will take a parameter that identifies the record to be retrieved and then return the blob field containing the picture.
You can do most of this through the Business Data Catalog Definition Editor which is included in the Microsoft Office SharePoint Server 2007 Software Development Kit (SDK). See Business Data Catalog Definition Editor in SharePoint Server 2007 at https://msdn2.microsoft.com/en-us/library/bb802680.aspx for information on this tool.
NOTE: if you use the BDC Definition Editor, you cannot select byte[] as the return type of the field. You can select byte, but not a byte array; you must export the application definition and edit that part by hand.
While you’re editing the definition file, increment the version of the file in the LobSystem tag (for example, to 1.1.0.0). This way you don’t have to delete the BDC application before importing it again.
The following defines the method and the method instance. Insert these lines in the <methods> section of the Product <Entity…> section:
XML
<Method Name="GetProductImage">
<Properties>
<Property Name="RdbCommandText" Type="System.String">SELECT LargePhoto FROM DimProduct WHERE ProductKey=@ProductKey</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
</Properties>
<Parameters>
<Parameter Direction="In" Name="@ProductKey">
<TypeDescriptor TypeName="System.Int32" IdentifierName="ProductKey" Name="ProductKey" />
</Parameter>
<Parameter Direction="Return" Name="ProductImage">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="ProductImageDataReader">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="ProductImageDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Byte[]" Name="LargePhoto" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="ViewAccessor" ReturnParameterName="ProductImage" ReturnTypeDescriptorName="ProductImageDataReader" ReturnTypeDescriptorLevel="0" Name="GetProductImageInstance" />
</MethodInstances>
</Method>
Import the BDC application definition file through the SharePoint Central Administration site the same way it was done in the walk-through.
Step 2 – Creating the ASPX page class
There are several ways to expose the binary data. I’ve chosen one that is straight-forward and fairly efficient, but there may be higher-performing ways to do it. In the case of my customer, the pages were not in a high-use part of their site so simplicity won out over high-performance.
The code overrides the page’s Render method. Using the BDC API, it retrieves the blob field based on URL parameters. Once it does that, it clears the Response output cache, sets the application type, and streams the blob field to the Response. There’s also error handling code to output the error as either an image or as HTML, depending on the mime type that was requested. Note that this code could also be extended to rename the output file based on a field in the data record or a URL parameter, by using the Context.RewritePath method. You could also pull the mime type from the BDC record if it is available there.
Using the tool of your choice, create a new C# class (any other .NET language will work as well, but the sample code shown here is all C#). At the customer, I originally used Visual Studio 2005; for this example, I’ve used Visual Studio 2008. Replace the class file code with the following (using your own namespace):
C#
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Text;
using System.Web.UI;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.SharePoint;
using System.IO;
namespace ValdonBlogSamples
{
// The GetBlobAs class is used to send blob data from a BDC Entity to
// the browser as a specified file type.
// The URL must contain the following parameters:
// type - the mime type of the blob (for example "image/jpeg")
// app - The name of the BDC application instance to connect to
// entity - The name of the BDC entity to access
// method - The name of the BDC method instance to call
// id - The id of the entity to retrieve
// field - The name of the field that contains the blob data. This
// must be declared as type byte[] in the application definition file.
// Example: /GetBlobAs.aspx?type=image%2fjpeg&app=AdventureWorksDWInstance&
// entity=Product&method=GetProductImageInstance&id=1234&field=LargePhoto
public class GetBlobAs : Page
{
public GetBlobAs() : base() { }
protected override void Render(HtmlTextWriter writer)
{
string errMsg = string.Empty;
string contentType = string.Empty;
try
{
// Get the URL parameters
errMsg = "Error retrieving the URL parameters. Details: ";
contentType = Request.Params["type"].ToLower();
string bdcApplication = Request.Params["app"];
string bdcEntity = Request.Params["entity"];
string bdcMethod = Request.Params["method"];
string entityId = Request.Params["id"];
string fieldName = Request.Params["field"];
if (string.IsNullOrEmpty(contentType))
throw new Exception("Missing content type (type URL parameter)");
if (string.IsNullOrEmpty(bdcApplication))
throw new Exception("Missing BDC Application Instance (app URL parameter)");
if (string.IsNullOrEmpty(bdcEntity))
throw new Exception("Missing BDC Entity (entity URL parameter)");
if (string.IsNullOrEmpty(bdcMethod))
throw new Exception("Missing BDC Method Instance (method URL parameter)");
if (string.IsNullOrEmpty(entityId))
throw new Exception("Missing BDC Entity ID (id URL parameter)");
if (string.IsNullOrEmpty(fieldName))
throw new Exception("Missing BDC Blob Field Name (field URL parameter)");
// Get the BDC entity
errMsg = "Error retrieving the BDC entity. Details: ";
LobSystemInstance app =
ApplicationRegistry.GetLobSystemInstanceByName(bdcApplication);
Entity entity = app.GetEntities()[bdcEntity];
MethodInstance method = entity.GetMethodInstances()[bdcMethod];
Object[] args = method.GetMethod().CreateDefaultParameterInstances(method);
args[0] = Convert.ChangeType(entityId, args[0].GetType());
IEntityInstanceEnumerator entities = (IEntityInstanceEnumerator)
entity.Execute(method, app, ref args);
if (!entities.MoveNext())
throw new Exception("Unable to find Entity instance from the ID.");
// Get the field value
errMsg = "Error retrieving the entity field value. Details: ";
IEntityInstance entityInst = entities.Current;
Object field = entityInst[fieldName];
byte[] bytes = field as byte[];
if (bytes == null)
throw new Exception("Entity field is not the correct type (byte[])");
// Output the field value with the content type specified
errMsg = "Error writing to the output. Details: ";
Response.ClearHeaders();
Response.Clear();
Response.ContentType = contentType;
Response.BufferOutput = true;
using (BinaryWriter bWriter = new BinaryWriter(Response.OutputStream))
{
bWriter.Write(bytes);
}
}
catch (Exception e)
{
if (contentType.StartsWith("image"))
{
OutputErrorAsImage(errMsg + e.ToString());
}
else
{
OutputErrorAsHtml(errMsg + e.ToString());
}
}
}
private void OutputErrorAsImage(string errMsg)
{
Response.ClearHeaders();
Response.Clear();
Response.ContentType = "image/jpeg";
Response.BufferOutput = true;
// Create an image. Note: you could calculate an image size
// based on the size of the text using System.Drawing classes
Bitmap errBitmap = new Bitmap(300, 300);
Graphics gdi = Graphics.FromImage(errBitmap);
gdi.FillRectangle(new SolidBrush(Color.DarkRed), 0, 0,
errBitmap.Width, errBitmap.Height);
// Put the message on the bitmap
gdi.DrawString(errMsg, new Font("Tahoma", 10, FontStyle.Bold),
new SolidBrush(Color.White),
new RectangleF(5, 5, errBitmap.Width - 10, errBitmap.Height - 10));
// Stream the error message image to the web response
errBitmap.Save(Response.OutputStream,
System.Drawing.Imaging.ImageFormat.Jpeg);
}
private void OutputErrorAsHtml(string errMsg)
{
Response.ClearHeaders();
Response.Clear();
Context.Response.ContentType = "text/html";
Response.Write(@"
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<title>Error Retrieving BLOB</title>
</head>
<body>
" + errMsg + @"
</body>
</html>");
}
}
}
Make sure you code sign the assembly so it can go in the Global Assembly Cache (GAC) then add it to the GAC using “gacutil.exe /i <filename>”. Alternatively, you can copy the assembly to the SharePoint’s bin directory for development and testing without signing it.
Add the new class to the web.config file as a trusted type.
XML
<SafeControl Assembly="GetBlobAs,Version=1.0.0.0,Culture=neutral,PublicKeyToken=ffffffffffffffff" Namespace="ValdonBlogSamples" TypeName="*" Safe="True" />
Replace ffffffffffffffff with your PublicKeyToken (use sn.exe –T <assembly path> to get the token), or if it isn’t code signed, use PublicKeyToken=null.
Create a new ASPX page and set the page class to the newly created class (I used SharePoint Designer and placed the page at the root of the site). The rest of the HTML in the page is not important and can be anything that you choose. I called the page GetBlobAs.aspx.
ASP.NET
<%@ page language="C#" inherits="ValdonBlogSamples.GetBlobAs,GetBlobAs,Version=1.0.0.0,Culture=neutral,PublicKeyToken= ffffffffffffffff" %>
You could set up this page to be a nicely formatted error page and fill it out if an error occurs. You could also redirect to the SharePoint error page instead of outputting HTML in the code.
Step 3 – Displaying the Image on a page
The final step is to pull everything together to display the image on a page. On the page you created during the walk-through that displays the Products list, add a Business Data Item web part and select the Product entity type.
With the page in edit mode, on the Products list web part, select edit/Connections/Send Selected item to/Product. This will add a radio button to the list which, when selected, will display the details of the item in the Business Data Item part. This should look something like Figure 1.
Figure 1 - BDC Web Parts on a Page
Now we are going to edit the Product web part’s XSL to insert the image.
Click the web part’s menu and select Modify Shared Web Part.
In the Data View Properties section, click the XSL Editor… button
Select all the text in the dialog (press Ctrl-a) then copy it (Ctrl-c) to your favorite XSL editor tool.
In the dv_1.rowview template section, add a new table row at the top (or wherever you want it) to display the image.
XML
<tr>
<td class="ms-descriptiontext ms-alignright">
<nobr>
Product Image:
</nobr>
</td>
<td class="ms-descriptiontext ms-alignleft" width="100%">
<img src="/sites/blogs/GetBlobAs.aspx?type=image%2fjpeg&app=AdventureWorksDWInstance&entity=Product&method=GetProductImageInstance&id={@ProductKey}&field=LargePhoto" />
</td>
</tr>
At this point, you can just copy the file back into the properties of the web part (which is good for quick testing), but I would recommend saving the file and uploading it to the site’s Style Library list. This allows you to reuse the XSL file for multiple instances of the Business Data Item web part. I’ve also found that just putting it in through the XSL Editor doesn’t always stick; it seems to get overwritten at times by the default XSL.
Link the Business Data Item web part to the XSL file by specifying the relative path in the XSL Link field of the Miscellaneous section. For example:
/sites/blogs/Style Library/XSL Style Sheets/ProductImageDisplay.xsl
Click OK and select a Product that has an image (I found that #559 – Chain and #306 – HL Mountain Frame – Black, 38 have images). Your page should now look something like:
Figure 2 - BDC Web Parts With an Image
If the database includes a thumbnail image, you could modify the XSL of the Business Data List web part to retrieve that field. You’d only need to add a new method to the BDC Application Definition file to execute the required SQL.
You could also extend the C# code to do some server-side manipulation of the image, such as enforcing a standard size, creating a thumbnail on the fly or adding a watermark.
Conclusion
This solution is fairly generic; it can return blobs of any mime type stored in the database by simply linking to an aspx page and providing the BDC information as URL parameters. If you are already using the BDC to retrieve the Entity, it is easy to add a new method to retrieve blob data.