Smart filtering in BCS

Joel Plaut

May 15, 2012

Smart Filtering in Business Connectivity Services

Business Connectivity Services allows the rapid creation of reference connections to live legacy data such as tables or views in SQL Server. The wildcard filtering is great, but what if you want to customize it?

Smart MyFilterParm Filtering in BCS

Using a stored procedure requires deviating from the easy out-of-box dynamic SQL, and defining the input parameter(s).

Here’s the pseudocode T-SQL for the smart MyFilterParm filtering. Note that first up to 10 MyFilterParms are listed, based on matching on a specific field, then 50 more generic wildcard matches are matched. This has the advantage of a high speed response, even if a user enters the letter “a” for search, making long distance (inter-farm) lookups more responsive. For the Union, note the fields from each Select need to match precisely in sequence, name and type. Best is if the exact same fields and names are returned that we are using today.

CREATE procedure dbo.sp_MySmartSearch
@MyFilterParmSmart nvarchar(255) = null
AS
SELECT 10 FROM [MyDataBase].[dbo].[CompanyView]
WHERE MyFilterParm LIKE @MyFilterParmSmart + '%'
UNION
SELECT 50 FROM [MyDataBase].[dbo].[CompanyView]
WHERE CompanyNM LIKE '%' + @MyFilterParmSmart + '%'

Once this Stored Procedure is written, export the BDCM (using SPD) and edit the XML to provide hard-coded reference to the above Stored procedure, MyFilterParm filter parameter, and fields returned. The BDCM import is not done in SPD, but is instead done in Central Admin in the BCS service app config. Here’s the XML Pseudocode to replace within the Methods XML group in the BDCM (important parts highlighted in larger font):

    &lt;Property Name="BackEndObject" Type="System.String"&gt;sp_MySmartSearch
    &lt;Property Name="BackEndObjectType" Type="System.String"&gt;SqlServerRoutine
    &lt;Property Name="RdbCommandText" Type="System.String"&gt;[dbo].[sp_MySmartSearch]
    &lt;Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"&gt;StoredProcedure
    &lt;Property Name="Schema" Type="System.String"&gt;dbo
    &lt;Parameter Direction="In" Name="@MyFilterParmSmart"&gt;
      &lt;TypeDescriptor TypeName="System.String" AssociatedFilter="Wildcard" Name="@MyFilterParmSmart"&gt;

You’ll find the XML much easier to edit in Visual Studio (any version) as the nesting is a bit much to handle in Notepad.

MSDN offers a similar example of a stored procedure, in this case, designed to return precisely one row:

https://msdn.microsoft.com/en-us/library/ee558376.aspx

Scot Hillier’s BCS book is also an excellent reference:

http://www.wrox.com/WileyCDA/WroxTitle/Professional-Business-Connectivity-Services-in-SharePoint-2010.productCd-047061790X,descCd-DOWNLOAD.html