Writing Custom Connector for BCS

BCS in SharePoint 2010 provides following connectors - Database, WebService, WCF, .NET and custom connector. This blog explains how you can write and deploy your own custom connector, if one of the BCS connectors do not meet your requirements.

This documentation (http://msdn.microsoft.com/en-us/library/ee554911(office.14).aspx) explains when to use .NET assembly connector and when to write your own custom connector.

Assuming that you are required to write your own custom connector, lets look at the steps to achieve the goal.

The steps for custom connectors are

• Write code for custom connector
• Deploy custom connector in SharePoint
• Write model for custom connector

Writing Custom Connector

Writing custom connector requires implementing ISystemUtility (http://msdn.microsoft.com/en-us/library/microsoft.businessdata.runtime.isystemutility(office.14).aspx) interface. The simple most custom connector requires implementing ExecuteStatic method in the interface. Other methods/properties can be boiler plate code.

Here is the simple most custom connector. Although the connector does not do anything, technically it can be deployed in SharePoint 2010.

using System;
using System.Collections;
using System.Collections.Generic;

using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.Runtime;
using Microsoft.BusinessData.Infrastructure;

namespace SharePointConnector
{
    public class Connector : ISystemUtility
    {

        #region ISystemUtility Members

        public IEnumerator CreateEntityInstanceDataEnumerator(object rawStream, 
            ISharedEntityState sharedEntityState)
        {
            // implement your enumerator
        }

        public IConnectionManager DefaultConnectionManager
        {
            get { return null; }
        }

        public ITypeReflector DefaultTypeReflector
        {
            get { return null; }
        }

        public void ExecuteStatic(IMethodInstance mi, 
            ILobSystemInstance si, 
            object[] args, 
            IExecutionContext context)
        {
            // implement your logic
        }

        #endregion
    }
}

Figure 1: Simple most custom connector

Let's take a look at the various methods and properties in the ISystemUtility interface.

CreateEntityInstanceDataEnumerator

This method converts line-of-business data stream into an enumerator of raw AdapterObjects. Implementing this method is necessary. If your connector gets IEnumerable as raw stream, you can just return the corresponding enumerator from the method.

DefaultConnectionManager 

This property allows you to manage connections for your external system. You can return null if you don't want connection management. BDC (Business Data Connectivity) will use its own default connection manager.

DefaultTypeReflector 

This property allows you manage the type reflection for your objects. For example, if your external system returns stream, BDC will unable to do a meaningful type reflection and you will have to write your own type reflection. If your external system returns .NET types than you don't need to provide any type reflection. For default implementation this property should return null.

ExecuteStatic

This method is the most important method in the interface. In the method, you should implement CRUDQ (create, read, update, delete and query) stereotypes for the connector.

public void ExecuteStatic(IMethodInstance mi, 
            ILobSystemInstance si, 
            object[] args, 
            IExecutionContext context)

Lets take a look at the method parameters

mi : This parameter represents the MethodInstance that is being executed by BDC. This parameter corresponds to the

<MethodInstance>

element with the LobSystem/Entity/Method in the BDC metadata model.

si : This parameter represents the LobSystemInstance the method instance is being executed against. This parameter corresponds to the

<LobSystemInstance>

element with the LobSystem in the BDC metadata model.

args : This parameter is the Parameters of the MethodInstance that is being executed. The last item in the array is reserved for the return parameter for the method instance.

context : This parameter sets the execution context of BDC. For all practical purposes this parameter can be ignored because External List in SharePoint does not set the execution context and you or BDC will not control this variable. The context can be different for BDC running under Office client.

Ok, now that we understand ISystemUtility interface, lets do some fun stuff.

Sample
In this sample, I will create a custom connector that will return "Movie" entity from an external system. For simplicity, the connector will support only "Finder" and "SpecificFinder" stereotypes. This sample will use in-memory data (external system).

Since the external system returns .NET types, implementation of the custom connector is simple.

public IEnumerator CreateEntityInstanceDataEnumerator(object rawStream, 
    ISharedEntityState sharedEntityState)
{
    IEnumerable enumerableStream = rawStream as IEnumerable;
    if (enumerableStream != null)
    {
        return enumerableStream.GetEnumerator();
    }

    throw new InvalidOperationException("not valid stream returned");
}

Figure 2: CreateEntityInstanceDataEnumerator implementation

In the method CreateEntityInstanceDataEnumerator, the base Enumerator is returned. Off course, for a real external system you may have to write an enumerator.

ExecuteStatic method will check what kind of stereotype is being executed and will execute corresponding methods on the external system. As said before, this connector just supports Finder/SpecificFinder stereotype, so it will throw for other stereotypes.

public void ExecuteStatic(IMethodInstance mi, 
    ILobSystemInstance si, 
    object[] args, 
    IExecutionContext context)
{
    // provide only read functionality
    switch(mi.MethodInstanceType)
    {
        case MethodInstanceType.SpecificFinder:

            IParameterCollection parameters = mi.GetMethod().GetParameters();

            // make sure there is only one input parameter for the method
            // and one return parameter.
            if (parameters.Count != 2 )
            {
                string message = "Method " + mi.GetMethod().Name +" must have one input and one return parameter";
                throw new InvalidMetadataObjectException(message);
            }

            // check if the input parameter is integer type
            // and the return parameter is "Movie" type
            Type param1Type = Type.GetType(parameters[0].GetRootTypeDescriptor().TypeName, false);
            Type param2Type = Type.GetType(parameters[1].GetRootTypeDescriptor().TypeName, false);

            if ( param1Type == null || param1Type != typeof(Int32))
            {
                string message = "Method " + mi.GetMethod().Name +" must contain input of type System.Int32";
                throw new InvalidMetadataObjectException(message);
            }

            if ( param2Type == null || param2Type != typeof(Movie))
            {
                string message = "Method " + mi.GetMethod().Name +" must contain input of type " + typeof(Movie).ToString();
                throw new InvalidMetadataObjectException(message);
            }

            int id = (int)args[0];
            args[1] = MovieData.GetMovie(id);

            break;

        case MethodInstanceType.Finder:
            args[args.Length-1] = MovieData.GetMovies();
            break;

        default:
            throw new NotImplementedException();
    }
}

Figure 3: ExecuteStatic implementation

The method verifies the Finder/SpecificFinder signature in metadata model. This way the connector can ensure that the metadata model is not invalid. Finally it executes the external system and sets the return value in args parameter.

I have attached complete source code at the end of this post.

Deploying Custom Connector in SharePoint

Deploying custom connector in SharePoint 2010 is pretty straight forward. All you need is to GAC the assembly in all  the SharePoint machines. This includes web-front ends as well as application servers in the farm. If you have the requirement to execute custom connectors on Office client ( take External List to Outlook or Workspaces ) you will need to GAC the assembly in client machines as well.

Writing Models for Custom Connector

Now that you have written the custom connector and have already deployed in the SharePoint, lets get a sample external list for the custom connector.

The LobSystem Type for custom connector must specify  "Custom". When custom connectors are used, the model must contain the SystemUtilityTypeName property for LobSystem.

<LobSystems>
    <LobSystem Type="Custom" Name="CustomLobSystem">
      <Properties>
        <Property Type="System.String" Name="SystemUtilityTypeName">SharePointConnector.Connector, SharePointConnector, Version=1.0.0.0, Culture=neutral, PublicKeyToken=dc97b363e814985e</Property>
      </Properties>

Figure 4: LobSystem properties for custom connector

SystemUtilityTypeName is the fully qualified name for custom connector type. The following figure shows snippet of the metadata model.

 

Figure 5: Metadata model snippet for custom connector

The sample metadata model is also attached in the source code (see end of post).

Custom connector in action

Now that we have our metadata model for custom connector, lets fire it up in the SharePoint. The following screen shots show the external list running against custom connector.

 

Figure 6: Choosing external content type

Figure 6 shows the external content type displayed for custom connector. At this point the custom connector is not being executed.

Figure 7: Finder stereotype executed in custom connector (via External list)

Figure 8: SpecificFinder stereotype executed in custom connector

Figure 7 and Figure 8 shows Finder and SpecificFinder stereotyped operations running in custom connector. Our custom connector executes the external system methods and returns appropriate data.

Source Code

Sample with complete source code can be downloaded from here.

As-is
The source code/software is provided "as-is". No claim of suitability, guarantee, or any warranty whatsoever is provided. Source Code and executable files can not be used in commercial applications.

SharePoint - Non root site collection caution

SharePoint allows one to create non root site collection without having a root site collection. For example, you can create a site collection as "http://sharepoint/sites/" without having any site collection at "http://sharepoint".

Caution : Even though SharePoint does not prevent you creating a non-root site collection without a root site collection, not having a root site collection is not a supported configuration. Some SharePoint features break without a root site collection.

Client OM (Microsoft.SharePoint.Client) Samples for SharePoint 2010

SharePoint 2010 introduces a new client side object model for retrieving data from SharePoint. The client OM is included in Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll. Library reference for client OM is at http://msdn.microsoft.com/en-us/library/ee536622(office.14).aspx

Client OM is counterpart to the Server OM with notable differences

• Client OM works on both on SharePoint server and client
• Client OM does not fetch data implicitly
• Client OM is supported for .NET as well as for ECMA (javascript etc)

The essence of the Client OM lies in ClientContext class ( Microsoft.SharePoint.Client.ClientContext ). This class allows you to get connected with the SharePoint server and then fetch data as required.

In this blog, I will covering few samples on how to use the Client OM.

Basics

First you will need to reference Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll and then use Microsoft.SharePoint.Client namespace.

using Microsoft.SharePoint.Client;

To connect to the SharePoint server, you will need SharePoint URL and create a ClientContext object.

ClientContext context = new ClientContext("http://sharepoint");

At this moment the client context for SharePoint is defined but no connection has been made. Now lets initialize the Web object (equivalent to SPWeb in Server OM).

Web web = context.Web;

To connect/fetch data from the SharePoint, ExecuteQuery needs to be call on ClientContext object.

context.ExecuteQuery();
Console.WriteLine("Web '{0}' [Id:{1}]",web.Title, web.Id);

If you run the above code, it will throw PropertyOrFieldNotInitializedException exception with following message

The property or field has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

The code throws exception because client OM does NOT fetch data implicitly. So, we will modify the code to fetch the data the data explicitly. Since we are only interested in Id and the Title of the Web, the code will explicitly request those data. Retreive method in the Web class to tell the client OM which data needs to be fetched. The complete code will look like

ClientContext context = new ClientContext("http://sharepoint");
Web web = context.Web;
web.Retrieve(
    WebPropertyNames.Id,
    WebPropertyNames.Title
);
context.ExecuteQuery();

Console.WriteLine("Web '{0}' [Id:{1}]",web.Title, web.Id);

The above code will print the title and the id for the Web.

ClientObject class is the base class for all Client OM object. ClientObject exposes the following important methods that code will use again and again.

IsPropertyAvailable(string propertyName) - Returns a flag that indicates whether the specified property has been retrieved or set, or has not been retrieved or set.
Retreive() - Retreives all properties associated with the object
Retreive(params string[] propertyNames) - Retreives the specified properties associated with the object

All classes deriving from ClientObject class has a property names class ( for example, Web class has WebPropertyNames class ) which tells what properties can be fetched for the object.

Sample 1: Get all lists in web

The following sample will get all the lists in a web. For the list, the code will fetch the list id, list title and the type of the list.

public void GetAllList()
{
    ClientContext context = new ClientContext("http://sharepoint");
    ClientObjectPrototype allListsPrototype = context.Web.Lists.RetrieveItems();
    allListsPrototype.Retrieve(
        ListPropertyNames.Title,
        ListPropertyNames.Id,
        ListPropertyNames.BaseType);
    context.ExecuteQuery();

    foreach (SPClient.List list in context.Web.Lists)
    {
        Console.WriteLine("List : {0}, Id: {1}, BaseType : {2}", list.Title, list.Id, list.BaseType);
    }

}

Sample 2: Get list details
In this sample, given a list the code gets the details for the list. The details include the Fields and Views of the list.

public void GetListDetails(string listName)
{
    ClientContext context = new ClientContext("http://sharepoint");
    List list = context.Web.Lists.GetByTitle(listName);            

    // get fields name and their types
    ClientObjectPrototype allFieldsPrototype = list.Fields.RetrieveItems();
    allFieldsPrototype.Retrieve( FieldPropertyNames.Id,
        FieldPropertyNames.Title, 
        FieldPropertyNames.FieldTypeKind);

    // get view title
    ClientObjectPrototype allViewsPrototype = list.Views.RetrieveItems();
    allViewsPrototype.Retrieve(
        ViewPropertyNames.Id,
        ViewPropertyNames.Title);

    context.ExecuteQuery();

    foreach (Field field in list.Fields)
    {
        Console.WriteLine("Field '{0}', Type : {1}", field.Title, field.FieldTypeKind);
    }

    ViewCollection views = list.Views;
    foreach (View view in views)
    {
        Console.WriteLine("View '{0}', Id : {1}", view.Title, view.Id);
    }

}

Sample 3: Get list items
The following code gets the list items in the given list.

public void GetListItems(string listName)
{          
    // build the CAML query to get ALL items
    CamlQuery query = new CamlQuery();
    query.ViewXml = "";

    ClientContext context = new ClientContext("http://sharepoint");
    List list = context.Web.Lists.GetByTitle(listName);
    ListItemCollection items = list.GetItems(query);
    items.RetrieveItems().Retrieve();
    context.ExecuteQuery();

    foreach (ListItem item in items)
    {
        // assumes that the list has a field with name 'Title'
        Console.WriteLine("Item : {0}, Id : {1}", item.FieldValues["Title"], item.Id);
    }
}

Sample 4: Add item to a list
The following code adds a list item in the given list.

public void AddItemToList(string listName)
{
    ClientContext context = new ClientContext("http://sharepoint");

    List list = context.Web.Lists.GetByTitle(listName);
    ListItemCreationInformation lic = new ListItemCreationInformation();
    ListItem item = list.AddItem(lic);

    //add the item information
    item["Title"] = "Adding a new Item";

    // List has a field name Checkbox which is a checkbox type field
    item["Checkbox"] = true;  

    item.Update();
    item.Retrieve(ListItemPropertyNames.Id);

    context.ExecuteQuery();

    Console.WriteLine("Id : {0}", item.Id);

}

Client OM is very useful however it takes time to understand how to use the API and get the best out of it. Enjoy !!

BDC : Beyond Web-Service and Database

As discussed in the previous post, Business Data Catalog only supports LOB of type web-service and database. What if you want to display your business data from your LOB through adapters ?

A little known fact of BDC is, its support of web-service through GACed proxy. Typically BDC generates web-service proxy at runtime from the WsdlFetchUrl property specified in the metadata. Instead of using WsdlFetchUrl property if
WebServiceProxyType property is used, BDC loads the proxy from GAC. The property WebServiceProxyType is specified at the LOBSystem level.

Sample

In this sample, an entity Employee will be displayed in the Business Data List where the entity is is fetched from local assembly ( GACed assembly ).

Figure 1: Employee Business Data List

Employee entity contains the following fields as described below

Employee
    Id - int
    LoginId - string
    FirstName - string
    LastName - string
    Title - string

AdventureWorks class (data fetched from AdventureWorks sample database) defines finder and specific finder (namely GetEmployees and GetEmployee methods).

public class AdventureWorks : HttpWebClientProtocol
{
    public IList GetEmployees(string filterName){ ... }
    public Employee GetEmployee(int id){ ... }
}

The point to note is AdventureWorks is a subclass of HttpWebClientProtocol. Once the assembly is created with AdventureWorks class and Employee class and GACed, its time to hook Employee entity with BDC.

Assuming the namespace of the AdventureWorks and Employee class to "Beyond" and FullName of the assembly is "BeyondAdvWorks, Version=1.0.0.0, Culture=neutral, PublicKeyToken=22c970e70e320837".

The following metadata defines a LobSystem of type WebService and registers Beyond.AdventureWorks class as the proxy for the webservice using WebServiceProxyType property.

Click on the following figure to see the LobSystem definition.

Figure 2 : LobSystem

Entity Employee is defined in the metadata, with identity "Id" of type int ( see following figure ).

Figure 3 : Entity

Finally the two methods are modeled in the metadata ( the figure contains Finder method, download the sample for complete metadata ).

Figure 4 : Methods

In the methods, fully qualified name is used to define the TypeDescriptor for Employee class.

After hooking the Employee entity with Business Data List, the following figure shows the individual Employee.

Figure 5 : Individual entity page

See the previous post for how to hook finder and specific finder methods to the Business Data List.

Source Code

Sample with complete source code can be downloaded from here.

As-is
The source code/software is provided "as-is". No claim of suitability, guarantee, or any warranty whatsoever is provided. Source Code and executable files can not be used in commercial applications.

Business Data Catalog

Business Data Catalog (BDC) is a feature of Microsoft Office SharePoint Server ( MOSS 2007) which provides a mechanism to bring and display business data on SharePoint. BDC is simple yet powerful to use because of its no-code solution.

Power of BDC

Consider this, there is a web-service (or database) which exposes your Line of Business (LOB) data and you want to see the data on SharePoint. This can be done by writing a custom web-part. You will need a developer to write that custom web-part. Now assume the web-service requires credentials based authentication. Hopefully the developer can still handle that. If the web-service requires unique credentials for each SharePoint user, you will require a smart developer. What about you want to search the data fetched by the web-part ?

BDC's goal is to enable you to surface business data (some of the aspects described above) in Office SharePoint Server 2007 with minimal coding effort. BDC requires one to write [n1] XML based metadata.

BDC Web Part - Adventure Works Sample

MOSS 2007 comes with five Business Data Web Parts [r3] which can display a list of entity instances, display details of an entity instance, display list of related entity instances, display a list of actions associated with an entity and create a Business Data item.

In this sample, a list of entity instances are displayed using Business Data List (BDL) with Adventure Works [r4] database as LOB.


Figure - List of Products in Adventure Works.
Steps in displaying the products list
Step 1 : Install the adventure works 2000 database [r4]
Step 2 : Install SharePoint Server 2007 SDK [r5]
Step 3 : Upload BDC metadata model for Adventure Works from SDK
- Open MOSS Central Administration
- Goto Shared Services Administration page for your shared service
- Click on "Import application definition" in the Business Data Catalog block
- Browse the adventure works model XML from SDK (C:\Program Files\2007 Office System Developer Resources\Samples\Business Data Catalog\AdventureWorks Samples\AdventureWorks2000.xml)
- Click upload



Figure - Model for Adventure Works uploaded.
At this step the BDC application definition for adventure works is uploaded.

Step 4 : Add BDL for AdventureWorks products.
- Goto your site (MySite)
- Click on Site Actions > Edit Page
- Choose to add a new Web Part



- Select Business Data List and Click OK
- A Business Data List will appear within the "Add a web part" block
- Click on "Open the tool pane"
- A Business Data List tool bar will open on the right side
- Click on the picker button and choose "Product"



- Click OK in BDL pane
- Exit the edit mode



- In the search criteria, enter your criteria where you will get all the products associated with the criteria.


Limitation of BDC

• BDC only allows read functionality on business data. That means BDC does not support CUD (Create, Update, Delete) on business entities.

• BDC metadata XML is tough to understand. If one is not using tools, model is hard to manage and will not necessarily qualify as "no code" solution.

• BDC supports only web-service and database based LOB.

References
r1 : Business Data Catalog Overview [link]
r2 : SharePoint 2007: BDC - The Business Data Catalog [link]
r3 : Business Data Web Parts [link]
r4 : Adventure Works [link]
r5 : SharePoint Server 2007 SDK [link]
Notes
n1 : There are tools (Microsoft and Third parties) that help write BDC metadata XML.