Exposing BLOB Data in Child Entities With Business Connectivity Services

Another interesting issue arose the last week. I was tasked with implementing a BCS .NET connector to a OTRS web issue tracking service, as I mentioned earlier. The icing on the cake was extracting the binary data (issue attachments and images) and showing them in SharePoint leveraging Business Connectivity Services (BCS) in SharePoint.

I found out a post on how to use BCS to expose SQL Server data, which was not applicable in my case. I also had the extra difficulty of having the attachments in a child entity. The OTRS Ticket was the primary entity in my model, with OTRS Article being a child entity with 1:N relation in between). The attachments were properties of the article in OTRS but in the model I attached them to the Ticket in order to be more accessible.

So, I struggled to build a model that had to comply with 2 goals:

• expose entities fields with BLOB data (Attachments and their binary data)
• the BLOB entities should be on the child side of the relation

In this post I will show you how to achieve these goals step by step with Visual Studio.

What we want to achieve?

This is the model that I'd like to end with. It has a main entity called Product and a set of child entities called Photo. The child entity has a compound key of both the ProductId and it's own PhotoId. It also has a binary field called PhotoData together with MIMEType and FileName fields that will govern how to expose the photo to the browser.

Those are the minimum three components for binary data BCS compatibility: MIME type, binary content and a file name.

We will model these two entities in a custom NET assembly connector. For brevity, I will fake the external service and return hardcoded data read from picture files embedded inside the connector.

Building the Connector

The first step is to create a new Business Data Connectivity Model named ProductModel in Visual Studio.

Visual Studio will create a new entity called Entity1 and will implement the entity sample methods X and Y, together with a NET implementation of the entity. The main problem with BCS development is that the metadata has to match hand-in-glove with the implementation, and also the BCS metadata has to match internally.

We will begin filling the Entity1 and changing its name to Product. We'll also change the name of the model from BdcModel1 to ProductModel. After the renaming of several files and nodes in the BDC Explorer, we'll have something like this.

As you can see, the Product entity has ReadItem (gets a product by its ID) and ReadList methods (gets a list of products). The methods are declared in the BDC model (left side) and their code will reside in ProductService.cs class.

We will model the Product entity operations first, as every change in the model triggers a change in the code that is generated in ProductService class. First, we'll change the Identifier1 field of the Product entity into a ProductId of Int32 type.

Modeling Mayhem

Then, in the BDCM editor we'll select the methods and fill their details in the BDC Method Details pane. This is the tricky part of modeling BDC: it's very easy to do this wrong. Luckily, the BDC Explorer lets us copy and paste metadata to save time. First, we will model a ReadList operation. It will take one no parameters and will return a "Return" parameter of type "Collection of Product", which will be our entity metadata. Take a look.

When we edit the metadata, we have the following BDC Explorer tree:

Here we have to change the type and the name of Entity1List, Entity1, Identifier1 and Message. They should be: ProductList, Product, ProductId (Int32) and ProductName (String). The change is done in Properties window (F4) and we should change the Name and Type Name property. When changing the Type Name you have to choose the entities from "Current Project" tab. For collections (such as ProductList) you should select the entity and check "Is Enumerable" list.

Note: when modeling the ProductId, you also have to specify that that property maps to the Identifier (and the entity that it refers to, i.e. Product).

Now, we have to change the same thing (without the collection, of course) for the ReadItem method. It should take one parameter (mapped to String) and return a Product. The good news is that we can copy and paste the Product node from ReadList into ReadItem method.

The underlying code class Product.cs and the service ProductService.cs have to be changed to include "hardcoded" data:

public partial class ProductService  
    {  
       public static Product ReadItem(int id)  
        {  
            return new Product()  
            {  
                ProductId = id,  
                ProductName = "Product " + id.ToString()  
            };  
        }  
  
       public static IEnumerable<Product> ReadList()  
       {  
           List<Product> list = new List<Product>();  
           var product = new Product()  
           {  
               ProductId = 1,  
               ProductName = "Product 1"  
           };  
           list.Add(product);  
           return list;  
       }  
    }    public partial class Product  
    {  
        public int ProductId { get; set; }  
        public string ProductName { get; set; }  
    }

At this moment we have a workable connector that exposes products and product details, but nothing else. We will do a quick check by deploying the connector and creating a new external list in SharePoint.

Well done! Now we have to model the photos :-)

Adding the Photo Entity to the model

First, we'll add a new class to the project, with the following simple properties:

public class Photo  
{  
    public int PhotoId { get; set; }  
    public int ProductId { get; set; }  
    public byte\[\] PhotoData { get; set; }  
    public string MIMEType { get; set; }  
    public string FileName { get; set; }  
}

Then, we add a entity in the BDCM model canvas right-clicking it and choosing "Add / Entity":

Of course, we have to change the name and add the properties of the entity. We have to add both identifiers, the PhotoId and the ProductId. They both have to refer to the Photo entity, and in the association we will let BDC know that it will provide the value of ProductId when the association is navigated.

I have also added a ReadItem method.

Even if the association is necessary, you still have to model the ReadItem method in advance and add an instance of that method, which should be of SpecificFinder type. We will take 2 In parameters with the 2 identifiers of the Photo entity and we will return an instance of Photo class, with all its fields.

We'll add the association between Product and Photo entities now, right-clicking again on the BDCM canvas:

In the dialog, we'll make sure that the association is correct and in this case we will only have the navigation from Product to the Photo, not the other way around. We'll remove the extra navigation method (the last one) and we will uncheck the Foreign Key association, as the ProductIds are returned in the code for the association method in the Product class.

Now we have a new method called ProductToPhoto in the Product entity that returns a list of photos for that product.

We still have to do the "boring" stuff of mapping the return types in the BDC Explorer pane:

After that, we have to write the code for the ProductToPhoto method. At the moment we won't be showing the photo yet, so we can set the BLOB array to null.

public static IEnumerable<Photo> ProductToPhoto(int productId)  
{  
    List<Photo> result = new List<Photo>();  
  
    var photo = new Photo()  
    {  
        FileName = String.Format("Product{0}.png", productId),  
        MIMEType = "image/png",  
        PhotoData = null,  
        PhotoId = 1,  
        ProductId = productId  
    };  
  
    result.Add(photo);  
  
    return result;  
}

Ready to roll! Deploy the solution to SharePoint and create External Content Type Profile pages in the BDC Service Application (Central Administration). It will automatically add the related Photos to the Product in its profile page.

We have to delete and recreate the external list. Now we can go to the View Profile action and see the details of the product and its photos:

Reading the photos

The only thing missing is the link to see the actual photo (the BLOB content). We have to add a StreamAccessor method and a method instance.

We can't add this method in the entity designer. We have to open the BDCM file as an XML file and then add the Method and MethodInstance nodes to it.

We will add our method under the existing ReadItem method:

The XML snippet to insert is this one:

<Method Name\="ReadPhoto" IsStatic\="false"\>  
  <Parameters\>  
    <Parameter Name\="PhotoId" Direction\="In"\>  
      <TypeDescriptor Name\="PhotoId" TypeName\="System.Int32" IdentifierEntityNamespace\="ProductModel.ProductModel" IdentifierEntityName\="Photo" IdentifierName\="PhotoId" />  
    </Parameter\>  
    <Parameter Name\="photo" Direction\="Return"\>  
      <TypeDescriptor Name\="photoTypeDescriptor" TypeName\="System.Stream" />  
    </Parameter\>  
    <Parameter Name\="ProductId" Direction\="In"\>  
      <TypeDescriptor Name\="ProductId" TypeName\="System.Int32" IdentifierEntityNamespace\="ProductModel.ProductModel" IdentifierEntityName\="Photo" IdentifierName\="ProductId" IsCollection\="false" />  
    </Parameter\>  
  </Parameters\>  
  <MethodInstances\>  
    <MethodInstance Name\="ReadPhotoInstance" Type\="StreamAccessor" ReturnParameterName\="photo" Default\="true" DefaultDisplayName\="View Photo" ReturnTypeDescriptorPath\="photoTypeDescriptor"\>  
      <Properties\>  
        <Property Name\="MIMETypeField" Type\="System.String"\>MIMEType</Property\>  
        <Property Name\="FileNameField" Type\="System.String"\>FileName</Property\>  
      </Properties\>  
    </MethodInstance\>  
  </MethodInstances\>  
</Method\>  

As you can see, we return a Stream with the data. We have two additional instance properties that specify which entity property is the MIME type and which one is the file name.

Check the mappings: both identifiers should be mapped to Photo entity and both as parameters and return values in the entities (for ReadItem method). If not, it will complain in runtime about "Expected 2 identifiers and found only 1". It took me some time to solve that one!

In our PhotoService.cs class we have to add the method that returns a Stream with the data. In my case I use a Base64 string with a small sailboat image in PNG format, using the excellent web site that allows you to encode the image into a string http://www.base64-image.de/step-1.php. I use the Convert .NET class to convert that string into the original array of bytes. (In this snippet I have shortened the string for legibility):

public Stream ReadPhoto(int  PhotoId, int ProductId)  
{  
    string imageString = "AABJRU5......ErkJggg==";  
    var photoData = System.Convert.FromBase64String(imageString);  
    return new MemoryStream(photoData);  
}

Deploy again to SharePoint, rebuild the external content type profile pages and it's done!

The complete code for this example is available on my SkyDrive.