Using Filters and the Business Connectivity Services object model

By

In our last blog post we covered how to use the Business Connectivity Services object model on the server to connect to BCS services metadata store, retrieve information about the lob systems, external content types, methods and how to execute a method of an external content type.

Executing BCS External Content Type Methods in C#

In this blog post we will demonstrate how to execute an external content type’s finder method which has a filter defined

1, Using SharePoint Designer 2010 create a new External Content Type using the Employees table from AdventureWorks2000. Setup a filter for this ECT against the First Name column

image

[click image for a larger view]

Notice that we have ticked the checkbox for ‘Ignore filter if Value is:’

2, Open up Visual Studio 2010 and create a new Visual Web Part project from within the SharePoint 2010 project section.

3, With the web parts user control in design mode add some text saying ‘First Name :’ and drag n drop a textbox and button on, setting the textbox ID to be FirstNameTextbox and the button ID to be SearchButton. Finally add a GridView so that your user control looks something similar to…

image

4, We need to add a reference to Microsoft.BusinessData.dll, right click on the References folder in Solution Explorer and choose Add Reference. Browse to the dll at

c:program filescommon filesmicrosoft sharedweb server extensions14ISAPIMicrosoft.BusinessData.dll

5, Open the code view of our user control – VisualWebPart1UserControl.ascx.cs, and add the following using statements…

using Microsoft.SharePoint.BusinessData;
using Microsoft.SharePoint.BusinessData.SharedService;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.Runtime;
using Microsoft.SharePoint.Administration;
using Microsoft.BusinessData.MetadataModel.Collections;
using Microsoft.SharePoint.BusinessData.Runtime;
using System.Data;

6, Add the following method…

private void executeFilterMethod()
{

 

    // get the catalog of entities to work with…
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
    IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);

 

    // get the Employee external content type…
    IEntity entity = catalog.GetEntity(“http://localhost”, “Employees”);

 

    // get the filters for the default Finder method
    IFilterCollection filters = entity.GetDefaultFinderFilters();

 

    // if FirstNameTextbox has a value set the filter value…
    if (FirstNameTextbox.Text != string.Empty)
    {
        WildcardFilter filter = (WildcardFilter)filters[0];
        filter.Value = FirstNameTextbox.Text;
    }

 

    // return the filtered data using the default Finder method…
    IEntityInstanceEnumerator enumerator = entity.FindFiltered(filters, entity.GetLobSystem().GetLobSystemInstances()[0].Value);

 

    DataTable table = null;

 

    // loop through the data returned
    while (enumerator.MoveNext())
    {
        // first time setup the datatable, everytime there after add a row…
        if (table == null)
            table = enumerator.Current.EntityAsDataTable;
        else
            enumerator.Current.EntityAsDataRow(table);       

 

    }

 

    // bind data to our GridView…
    GridView1.DataSource = table;
    GridView1.DataBind();

 

}

 

7, Double click on the button on our user control to add a click event handler, and simply call the method above we defined

protected void SearchButton_Click(object sender, EventArgs e)
{
    executeFilterMethod();
}

8, That is all the code we have to write, we can now press F5 and Visual Studio will deploy the wsp to our site.

When SharePoint opens put the web page into edit mode and insert a web part. You’ll find in the Custom web part category VisualWebPart1. When you add this you can click the Button, and you’ll be returned all the data from the Employees External Content Type, or enter a first name such as Michael and you’ll see just those matching results coming back:

image

Check back again soon as we’ll be writing plenty more about how to use the BCS object model.

<nick/>