Hi,


The BCS now starts to be well known by all the fellow SharePoint developers but what we often find on the web is how to interact with *basic* tables and *basic* field types.


What about binary streams? So, what about accessing blob data stored in a custom database and interact with them via the BCS? There is already a very good answer to this on MSDN at http://msdn.microsoft.com/en-us/library/ff634782(office.14).aspx . I encourage you to read this article first before going further unless you are already familiar with the StreamAccessor stereotype.

However, it focuses on how to build a model that includes the StreamAccessor stereotype, then how to import and use the model using the Business Data List webpart but not on how to use this StreamAccessor directly in your own components. While this is already a valuable information, it doesn't provide the full picture.

I'll try to shed some more light on the StreamAccessor. At the time of writing:

- StreamAccessor cannot be implemented with SharePoint Designer
- blobs can be used in external content types but cannot be used in External Lists. If you include such a field, this will break the forms associated to the operations (Create/Edit...).


So, as you can see, you cannot use such field types in External Lists. The purpose of this post is to show you how you can use the Business Data API in a custom webpart to perform read/write operations on blobs.


In this example, I'm using the same LOB System than the one found on MSDN:AdventureWorks and I'm also interacting with the table Production.Document. I've just added a Create operation with SharePoint Designer including the field Document in order to be able to add entries.

Here are two screenshots of the basic webpart we'll implement, a reading section:

and another section where you can upload a new document that will be pushed to the database:

The following piece ofcode retrieves all the production documents and returns a datatable.

public DataTable GetProducts()
{         
    IEntity ProductEntityObject = BCSCatalog.GetEntity(ProductEntity.NameSpace, ProductEntity.EntityName);
    INamedMethodInstanceDictionary MethodInstances =
        ProductEntityObject.GetMethodInstances(MethodInstanceType.Finder);
    IView ProductFinderView = ProductEntityObject.GetFinderView(MethodInstances[0].Key);
    DataTable ProductDataTable = new DataTable();

    for (int i = 0; i < ProductFinderView.Fields.Count; i++)           
        ProductDataTable.Columns.Add(ProductFinderView.Fields[i].TypeDescriptor.Name);

    
    IFilterCollection ProductFilters = ProductEntityObject.GetDefaultFinderFilters();
    ILobSystemInstance ProductInstance = ProductEntityObject.GetLobSystem().GetLobSystemInstances()[0].Value;
    IEntityInstanceEnumerator ProductInstances =
        ProductEntityObject.FindFiltered(ProductFilters,
        MethodInstances[0].Value.Name,
        ProductInstance,
        OperationMode.Online);

    IFieldCollection ProductFields = ProductFinderView.Fields;

    while (ProductInstances.MoveNext())
    {

        DataRow row = ProductDataTable.NewRow();

        foreach (IField ProductField in ProductFields)
        {

            row[ProductField.Name] = ProductInstances.Current[ProductField.Name];

        }
        foreach (IStreamableFieldDescriptor StreamFieldDesc in ProductInstances.Current.GetStreamableFields())
        {
            IStreamableField StreamField = ProductInstances.Current.GetStreamableField(StreamFieldDesc.StreamAccessorName);

            if (!ProductDataTable.Columns.Contains(StreamField.StreamAccessorName))
                ProductDataTable.Columns.Add(StreamField.StreamAccessorName);

            //Technique 1 : using the OOTB application page to download the file

            string ProductDownLoadLink = string.Format(
                     @"<a href='{0}/_layouts/DownloadExternalData.aspx?EntityNamespace={1}&
                      EntityName={2}&LobSystemInstanceName={3}&
                      StreamAccessorName={4}&
                      ItemId={5}'>",
                     SPContext.Current.Web.Url,
                     HttpUtility.UrlEncode(ProductEntity.NameSpace),
                     HttpUtility.UrlEncode(ProductEntity.EntityName),
                     HttpUtility.UrlEncode(ProductInstance.Name),
                     HttpUtility.UrlEncode(StreamField.StreamAccessorName),
                     EntityInstanceIdEncoder.EncodeEntityInstanceId(
                        new object[]
                        {
                         (
                            ProductInstances.Current.GetIdentity().GetIdentifierValues()[0]
                         )
                        }
                      ),
                        SPUtility.MapToIcon(SPContext.Current.Web, 
							row[ProductEntity.FileNameFieldName].ToString(),
							row[ProductEntity.FileNameFieldName].ToString())
                        );

            row[StreamField.StreamAccessorName] = ProductDownLoadLink;

            //Technique 2 : call the method GetData() to get the blob content.
            // StreamField.GetData();
        }
        ProductDataTable.Rows.Add(row);
    }
    return ProductDataTable;
}

The highlighted lines is where the code gets the binary objects. Each IEntityInstance exposes a collection of IStreamableFieldDescriptor and a method that returns one or more IStreamableField objects.

From those objects, you can call the method GetData() that returns the binary content. Alternatively, you can use the OOTB application page DownloadExternalData.aspx and pass the required parameters to build a download link (option shown in this code).

The following piece of code shows how to write into a BLOB via the BCS API:

public void AddProduct(string title,string filename,string fileextension, byte status,
						int changenumber,byte[] filecontents)
{
    if (filecontents == null || filecontents.Length == 0)
        throw new ArgumentException("File content cannot be null or zero length");

    IEntity ProductEntityObject = 
		BCSCatalog.GetEntity(ProductEntity.NameSpace, ProductEntity.EntityName);          
    
    IFieldValueDictionary ProductValues =
    ProductEntityObject.GetCreatorView(ProductEntity.CreateViewName).GetDefaultValues();
    ProductValues[ProductEntity.TitleFieldName] = title;
    ProductValues[ProductEntity.FileNameFieldName] = filename;
    ProductValues[ProductEntity.FileExtensionFieldName] = fileextension;
    ProductValues[ProductEntity.StatusFieldName] = status;
    ProductValues[ProductEntity.ChangeNumberFieldName] = changenumber;
    ProductValues[ProductEntity.DocumentFieldName] = filecontents;
    ProductEntityObject.Create(
        ProductValues, ProductEntityObject.GetLobSystem().GetLobSystemInstances()[0].Value);
}

As I mentioned earlier, I've added a Create operation to the AdventureWorks model (from MSDN) and I've included the document field to it. The good think is that I can now just add a new item by getting the creator view and by assigning my binary stream (highlighted line) to my binary field. The only *cons* of using this technique is that this external content type expose a Create operation but if you attach it to an external list, the new form will crash...because such fields are not compatible with external lists.

Of course, if you attach the external content type first without binary fields and then update your content type, the external lists won't crash anymore.

Happy coding!