How to connect Business Data Catalog (BDC) lists to other SharePoint lists

Recently I had to create Web Part connection between BDC based list and usual SharePoint list. BDC list was provider and usual SharePoint list was consumer. I was pretty surprised if found out that I was able to connect BDC based list only to other BDC based Web Parts. All the other Web Parts were not able to be consumers for them. Same time BDC lists were able to be consumers of the other web parts.

Usual web parts doesn't implement IEntityInstanceProvider 

This time Google wasn’t my help and documentation. Also MSDN wasn’t able to help me – there were documents for everything but no texts or explanations about how and what I can use. Here is the example of perfect documentation. After hours of investigating and experimenting and hacking everything started working. As a result I created a web part that reads data from BDC list and provides it to other Web Parts.

Wrapper Web Part

The Web Part I created works as proxy between BDC lists and usual SharePoint lists. If you need common solution you can easily extend the code I will give here. Schematically works my wrapper like this.

Wrapper web part between BDC and regular web parts

Business Data Catalog consumer

Let’s start with the darkest part of this journey – BDC consumer and its completely undocumented API. There are some things I want to say before we go and look at the code. Number one thing is – I am not 100% sure if this solution is 100% correct. But this is the way I got things to run.

In the constructor of Web Part I will initialize DataTable where we hold the value that provider part of our Web Part provides. If there is no connection from BDC list then we will provide default value to consumers of our Web Part. Consumer part of our Web Part follows ASP.NET Web Part consumers and providers way. We will define method SetConnectionPoint and accept IEntityInstanceProvider as its argument. Through this interface we are able to find out what object was selected from BDC list.

Now, here is the code.

BDC Consumer part of Web Part


using System;
using System.Collections;
using System.Data;
using System.Runtime.InteropServices;
using System.Security;
using System.Web.UI;
using
AspWebParts = System.Web.UI.WebControls.WebParts;

using MdModel = Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.SharePoint.Portal.WebControls;
using SPWebParts = Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint.WebPartPages.Communication;
using
Microsoft.SharePoint;

namespace
MyWebPart
{    
    [
Guid("YOUR-GUID-HERE"
)]
    [
Obsolete
()]
   
public class BDCWrapper : SPWebParts.WebPart, IRowProvider
    {
       
private string serviceCaseId = string
.Empty;
       
private string defaultValue = string
.Empty;
       
private string filterField = string
.Empty;
       
private DataTable
filterTable;

       
/// <summary>
        /// Constructor of class. Let's initialize data table that holds filter value.
        /// </summary>
        public
BDCWrapper()
        {
           
this.filterTable = new DataTable
();

           
DataColumn column = new DataColumn
();
            column.DataType = System.
Type.GetType("System.string"
);
            column.ColumnName =
this
.filterField;
            column.Caption =
this
.filterField;
           
this
.filterTable.Columns.Add(column);

           
DataRow
dataRow = filterTable.NewRow();
            dataRow[
this.filterField] = this
.defaultValue;
            filterTable.Rows.Add(dataRow);
        }

       
/// <summary>
        /// Render Web Part. Write out filter field's current value.
        /// </summary>
        protected override void Render(HtmlTextWriter
writer)
        {
            writer.Write(
this
.filterField);
            writer.Write(
"="
);
            writer.Write(
this.filterTable.Rows[0][this
.filterField]);
        }

       
/// <summary>
        /// Connection point for Business Data Catalog Web Parts.
        /// </summary>
        /// <param name="provider">Connecting BDC provider.</param>
        /// <remarks>
        /// Keep the ID (currently Cons2193838) and don't delete it or
        /// you may face somenasty problems. You can always change
        /// this ID.
        /// </remarks>
        [AspWebParts.ConnectionConsumer("BDCConsumer", "Cons2193838"
)]
       
public void SetConnectionPoint(IEntityInstanceProvider
provider)
        {
           
// Method is called but there is no provider.
            if (provider == null) return
;

           
// Method is called but there is no selected entity.
            MdModel.Entity ent = provider.SelectedConsumerEntity;
           
if (ent == null) return
;

           
// Get a view we have to use to ask selected entity.
            MdModel.View view = ent.GetSpecificFinderView();
           
if (view == null) return
;

           
// Now we have view, let's ask entity instance.
            // NB! View cannot be null!
            IEntityInstance
inst = provider.GetEntityInstance(view);
           
if (inst == null) return
;

           
// Let's ask entity as data table.
            DataTable
dt = inst.EntityAsDataTable;
           
if (dt == null) return
;

           
// Check if data table has filter field.           
            if (dt.Columns.IndexOf(this.filterField) < 0) return
;

           
// Check if data table has rows.
            if (dt.Rows.Count == 0) return
;

           
// Everything is okay, let's save filter value;
            this.serviceCaseId = dt.Rows[0][this.filterField].ToString();
        }
    }
}

Of course, for production code you should have here also lines of code for logging anomalies. Also some error handling will be fine. I removed this code because otherwise it is too long to show here.

Consumer part of Web Part

The other part of wrapper works as row provider and is understandable to all usual SharePoint lists. I had some troubles getting it work using IWebPartRow, so I stayed on older and unfortunately deprecated IRowProvider interface. The last one of them is very well documented, by the way. I think this part of my code doesn’t need further explanations, so here it is.

NB! Add this code to the end of class given below.


/// <summary>
///
Provider initialization event.
/// </summary>
[Obsolete]
public event
RowProviderInitEventHandler RowProviderInit;

/// 
<summary>
///
Provider is ready to provide data.
/// </summary>
[Obsolete]
public event
RowReadyEventHandler RowReady;

private bool connected = false;
private string connectedWebPartTitle = string.Empty;
private string consumerMsg = string
.Empty;

/// 
<summary>
///
Register provider interfaces.
/// </summary>
[Obsolete]
public override void
EnsureInterfaces()
{
   
try
    {
        RegisterInterface(
"MyRowProviderInterface"
,
           
InterfaceTypes
.IRowProvider,
            SPWebParts.
WebPart
.UnlimitedConnections,
           
ConnectionRunAt
.Server,
           
this
,
           
""
,
           
"Provide filter to"
,
           
"Provides a row to a consumer Web Part."
,
           
true
);
    }
   
catch (SecurityException
ex)
    {
       
this
.consumerMsg = ex.ToString();
        registrationErrorOccurred =
true
;
    }
}

/// 
<summary>
///
Web Part can run only on server.
/// </summary>
[Obsolete]
public override ConnectionRunAt
CanRunAt()
{
   
return ConnectionRunAt
.Server;
}

/// 
<summary>
///
Web Part starts connecting.
/// </summary>
[Obsolete]
public override void
PartCommunicationConnect(
   
string
interfaceName,
    SPWebParts.
WebPart
connectedPart,
   
string
connectedInterfaceName,
   
ConnectionRunAt
runAt)
{
   
if (interfaceName != "MyRowProviderInterface") return
;
   
this.connected = true
;
}

/// 
<summary>
///
Initialize communication.
/// </summary>
[Obsolete]
public override void
PartCommunicationInit()
{
   
try
    {
       
if (!this.connected) return
;

       
if (RowProviderInit != null
)
        {
           
RowProviderInitEventArgs initArgs = new RowProviderInitEventArgs
();
            initArgs.FieldList =
new string[] { this
.filterField };
            initArgs.FieldDisplayList =
new string[] { this
.filterField };

            RowProviderInit(
this
, initArgs);
        }
    }
   
catch (Exception
ex)
    {
       
this
.consumerMsg = ex.ToString();
    }
}

/// 
<summary>
///
Called when provider is ready and consumer is waiting data.
/// </summary>
[Obsolete]
public override void
PartCommunicationMain()
{
   
if (!this.connected) return
;
   
if (this.RowReady == null) return
;

   
try
    {
       
RowReadyEventArgs
initArgs;
        initArgs =
new RowReadyEventArgs
();
        initArgs.Rows =
new DataRow
[] { };
        initArgs.SelectionStatus =
"Standard"
;
        RowReady(
this
, initArgs);
    }
   
catch (Exception
ex)
    {
       
this
.consumerMsg = ex.ToString();
    }
}

/// 
<summary>
///
Returns initialization arguments.
/// 
</summary>
///
 <param name="interfaceName">Name of interface.</param>
[Obsolete]
public override InitEventArgs GetInitEventArgs(string
interfaceName)
{
   
if (interfaceName != "MyRowProviderInterface") return null
;

   
try
    {
        EnsureChildControls();

       
RowProviderInitEventArgs
initArgs;
        initArgs =
new RowProviderInitEventArgs
();
        initArgs.FieldList =
new string[] { this
.filterField };
        initArgs.FieldDisplayList =
new string[] { this
.filterField };

       
return
(initArgs);
    }
   
catch (Exception
ex)
    {
       
this
.consumerMsg = ex.ToString();
       
return null;
    }
}

Now should everything be done. If you want you can use attribute consumerMsg in Render method to show consuming status of wrapper Web Part.

Conclusion

Nothing is impossible if you know reflecton and you have good sense when there is no more point to struggle in search engines hoping to find an answer. If you have any comments about this code please feel free to drop me a line here. Happy connecting!


11 thoughts on “How to connect Business Data Catalog (BDC) lists to other SharePoint lists

  • Pete H says:

    Thanks! This was really helpful. I had also been trying to figure this out, and was totally baffled by the lack of resource on the interenet, and the useless MSDN information.

    Then I saw your code – hooray! I grabbed the consumer part code, tinkered with it, and I now have a custom web part that connects to a BDC list! Thanks!

    As well as tailoring your code for my needs, I found what I guess are a couple of typos: specifically, I had to change "portal.IEntityInstanceProvider" to "IEntityInstanceProvider", and "System.string" to "System.String". Nice work!

  • Prashant says:

    Thanks!

    Post was very helpful for us. There are very less resources on these topics.

    After reading your post we managed to connect our web part with bdc list.

    Thanks Again

  • Hobby says:

    Thank you for this post !

    Not to repeat predecessors, but straight to the point.

    I made a Web part out of this code, but the routine in constructor seems to fail, because I get Error message:

    Unable to add selected web part(s).

    BDCXmlViwer: Cannot import this web part.

    P.S. "BDCXmlViewer is the name of my Web part

    Is there anyone who can help ?

    Thanx in advance…

  • Hobby says:

    Can we "mortal poeple" get class above in one piece perhapse.

    I’m a little confused about this "NB! Add this code to the end of class given below.". Maybe it’s just a typo…

    I am trying to understand machanics of your code.

    Maybe a really simple (if it can be more simple) example just to be able to connect to BDC Web Part?

    Tahnk you

  • Gunnar says:

    I wrote this code just to connect one BDC web part to "usual" web part. There are many ways how one can extend this functionality. Web parts have different connection interfaces. My implementation, as you can see when looking at class (the first code listing) uses row provider interface. There are also interfaces to provide cell values, filters, lists etc.

    It is really up tu you what kind of functionality you need. Of course, it is also possible to write universal web part but it takes time because also BDC has many features you have to consider and test blindly (okay, through reflection).

  • Pierre says:

    Hi

    Great post!

    I’m trying to do something similar but in the same time different.

    Well i have a workflow designed with SPD who will compare data between a list A and data from SQL database(4 tables =~1000 000 lines).

    One solution was to upload the data on daily basis in a sharepoint list, but the problem is that it takes aprox 15h to upload the data.On update it takes the double!

    Another solution is to create a custom action which will pull data from a BDC web part(connect to MS SQL).

    What do you think about my challenge? Is it possible? How should i proceed?

    Any ideas? Code? Links?  

  • Gunnar says:

    If you can do it in database server then do it there. SharePoint lists are very resource expensive when you want to do bulk operations on larger lists.

  • niceguymattx says:

    Just wanted say this post is awesome – really helped me in building custom business data consumer web parts. There’s not a lot of stuff out there on this (that I could find) and this article really helped!

    Cheers again

  • Sarah Hanna says:

    I searched a lot over the internet to find a solution for this issue and finally I found this post. But when I applied the code I found that the inst always with null value.
    Any hints?

  • Gunnar says:

    Sarah, can you give me more details about your problem? In what method what variable or argument is always null?

Leave a Reply

Your email address will not be published. Required fields are marked *