Send your request Join Sii

Nowadays companies very rarely keep their data in one centralized source. Data is usually dispersed into many smaller data sources, served by different engines, based on data type and purpose, company organization, complexity and relations. It was always a typical case in this type of enterprises to write custom applications that gather and analyse data from different sources and present them as they were in the same place.

Introduction

Since SharePoint 2010 we have Business Connectivity Services, which are very helpful in retrieving and presenting data from external sources, making it look like the data is actually kept in SharePoint database (through external list).

When dealing with external data sources (especially large ones), we often experience performance issues. This is because SharePoint (by default) gets the whole amount of data from the data source every time user interacts with the external list. This means that we can filter the external list through UI, but filter operation occurs after the entire set of data is retrieved from the source. So what if we tell data source to pre-filter the result set before it is returned to SharePoint?

In this article we reveal some theory and show clever, practical way to setup filtering for external data sources in SharePoint 2013 through query string parameters to achieve significant performance improvement.

Introducing BDC

First of all let us explain what BDC actually is and what are its main goals and foundations.
Business Data Connectivity (often referred as BDC) is a service built into Microsoft SharePoint that enables to declaratively model external systems and to expose external data as a list in SharePoint sites.

The core function of BDC is to provide connectivity to external systems of different kinds with very little effort. BDC has built-in support for connecting to most common data providers:

  • Databases (in general)
  • Web services
  • .NET assemblies

There is also a possibility to develop a custom connector plugin through Connector Framework (so in theory BDC service can connect to any data source).

The following diagram presents general concept of BDC:

concept of BDC

At the foundation of Business Data Connectivity we have an object called BDC model, which is a bridge between SharePoint and external data source, describing how the connection is handled and what data is transferred between the two ends. In general, BDC model comprises of an entity and its methods:

  • BDC entity describes the structure of business entity which is mapped to the real life business object (like customer, order etc.).
  • Methods represent abstract descriptions of back-end API. A common task when working on BDC model is to create method instances that implement actual communication between external system and the model.

Method types

Full interface defining possible data operations for BDC model contains the following method types:

method types

While complete two-directional communication between SharePoint and external data source will require implementing all methods, for simple one-way communication we will concentrate on Finder method, which returns list of all entities from external data source. We will configure this method to support filtering and to return only the subset of data we are interested in.

Creating sample BDC model

Visual Studio will make lots of work for us, so we can quickly setup a project and add model based on Business Data Connectivity Model template.

Visual Studio template

We make couple of quick changes in naming – feature name, Entity1 and Entity1Service should get more meaningful names (one thing to note – Visual Studio has already created Farm-scoped feature for us that will be used to deploy BDC model).

entitynames2 300x254 1 - Query string filtering for BCS data

ExternalEntity class should be further changed to define set of metadata to be retrieved from external data source and to be presented to end users.

code

We will also provide a code to return a list of dummy external entities in ReadList method instance.

code

Testing BDC model

Now we can deploy our solution to SharePoint and test our BDC model.
To see the results we need to put Business Data List web part on the page and browse for our BDC model in web part settings.

Business Data List

If you don’t see your model in External Content Type Picker, most probably you’ve made a mistake in model configuration (ULS will help you to track down the problem). If everything is configured correctly, we should see data pulled from external data source with possibility to filter and sort as in normal SharePoint list view (this kind of filtering however happens on SharePoint side, so no matter what filter we choose in a list view, a whole list of entities is retrieved from external data source).

BDC Model Test

Making BDC model filterable

Now we will enable BDC model to be dynamically filtered on Category column.
First step is to define filter description for Category column (we can use Visual Studio designer to do that).

define filter description for Category column

We give the filter descriptor a name (in our case it will be called categoryFilter). In Filter Field property we should say which property from ExternalEntity will be filtered (in our case it is called Category). We have different type of filters, but since we have a text property, we will use Wildcard filter.

The second step is to add input parameter to our ReadList method.

add input parameter

It is crucial here to bind type descriptor for parameter to filter descriptor created in previous step through Associated Filter property.

One last step is to adjust ReadList method to actually filter the results (note that category parameter has been added automatically to reflect changes made to the model).

category parameter

Setting up filtering on the page

Once we have the latest version of our model deployed to the server, we can make additional setup on our page to actually enable filtering with the use of value provided in query string. To achieve that, we should put Query String (URL) Filter web part on the page and edit its properties.

Query String (URL) Filter

Filter Name is the name of our choice, it is used only for identification. Query String Parameter Name is more important, because it says what key we will use in URL query string to provide value for filtering (for example http://pageUrl?category=Cat1).

Now we will connect Query String (URL) Filter web part to our Business Data List web part following these steps:

Connections - Send Filter Values To
Connection Type - Get Query Values From
Consumer Field Name - categoryFilter

Our web parts are now connected, so we can append the query string to the page URL and see if filtering works as expected.

Category column

Summary and considerations

In this article I have shown how to configure DBC model to support filtering and how to setup the filtering on the SharePoint page. In your scenarios you should take into account the following considerations:

  • There can be multiple filters applied at the same time (in this case ReadList method will have additional parameters and multiple Query String filter web parts will be connected to external list web part)
  • This scenario supports different type of filters (so instead of Query String filter web part we can put on the page Text filter, Date filter or some other filter web parts)
  • In our example ReadList method creates objects on the spot, but in real life scenario, in this method we should place the code that connects to external database or makes parametrized call to web service in order to get the results
  • Many steps required for proper BDC model configuration (for example defining identifiers or creating type descriptors for entities) are intentionally omitted. This article concentrates on steps important from filtering point of view

Good luck with your external content types!

5/5 ( votes: 2)
Rating:
5/5 ( votes: 2)
Author
Avatar
Tomasz Kornacki

Od 10+ lat zajmuje się platformą SharePoint. Doświadczenie zbierał w polskich i zagranicznych firmach, pracując z wieloma wersjami i w różnych rolach projektowych. Zajmuje się głównie designem i developmentem, zarówno po stronie back-end jak i front-end. Poza pracą interesuje się kulinariami i sportem.

Leave a comment

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

You might also like

More articles

Don't miss out

Subscribe to our blog and receive information about the latest posts.

Get an offer

If you have any questions or would like to learn more about our offer, feel free to contact us.

Send your request Send your request

Natalia Competency Center Director

Get an offer

Join Sii

Find the job that's right for you. Check out open positions and apply.

Apply Apply

Paweł Process Owner

Join Sii

SUBMIT

Ta treść jest dostępna tylko w jednej wersji językowej.
Nastąpi przekierowanie do strony głównej.

Czy chcesz opuścić tę stronę?