Welcome to my blog, stay tunned :
Home | Blogs | Stephane Eyskens's blog

One step further with the BCS and the StreamAccessor

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!

Comments

Fast Search to search BLOB

Hi,

Just to confirm, are their any other modifications to search BLOB using Fast Search.
Please let me know.

Thanks
Varun V

Search blobs

Hi,

I've never tried to use BCS together with Blobs and leverage the search engine against the BCS data source. I've only couped Search & BCS on regular data and there, except configuring a content source that points to the BCS data, there is nothing else to do.

For blobs, SharePoint search makes use of IFilter so you should make sure that the blobs you're talking about can be indexed first. I suppose that if it's the case, you'll be able to crawl your BCS.

Best Reegards

Write BLOB to external database using Sharepoint BCS

Hi Stephane,

Your article looks like something I could use to add file attachments to my external database through Sharepoint bcs. I am already using the streamaccessor to download attachments, but I have to manually add the attachments using SQL. Could you show the code for the upload form/webpart?

Regards
Hallvard

Writing binary content to external database via the BCS

Hi Hallvard,

There is a sample already :). The AddProduct() method example shows how to write binary content to external databases via the BCS. Didn't you see it or isn't it enough?

Cheers

re: Writing binary content to external database via the BCS

Thanks for quick reply :)

I'm quite new to Sharepoint so I was looking for complete code for the webpart. However I managed to create a webpart, add a FileUpload component and using the System.Data.SqlClient to write the file to my database. I am currently trying to do the same with the BCS API, but I'm starting to doubt it's worthwhile and might just stick to sqlclient.

regards
Hallvard

SQL or BCS

Hi Halvard,

It's an architectural choice. If you make your component available through SharePoint, the BCS might be a better option also because you can configure it from CA and you centralize your interaction with the external world in SharePoint. Then, you can just use the BCS API.

It's up to you to chose what's the best suited option in your particular context.

Best Regards

Publishing file system through BCS

Thanks for sharing the idea and the code! You can find another example of using StreamAccessor that demonstrates how to publish file system folders through external list / BCS here:
http://pholpar.wordpress.com/2010/09/19/publishing-files-stored-in-the-f...

Currently my solution is read only. I might check how to extend it with the add / alter file content feature using the technique you described.

Peter

Your solution

Hi Peter,

Thanks for sharing!

Best Regards