Find Items in External List Programmatically

May 15, 2012

While working on a recent project I had to connect to an external DB to pull in some data and then use this in other lists in a SharePoint site. The obvious solution for this was to use BCS and set up an external content type to get the data from my DB. I had never setup BCS before so decided to use SharePoint Designer to set it up as this seemed the easiest way. It took a little trial and error, especially around permissions, but eventually I managed to get my list up and running displaying data. In addition I added a filter to my external content type to allow users to search on the data. I won’t go over how to add a filter as there are numerous articles on the web about how to do this, see examples below.

http://msdn.microsoft.com/en-us/library/ff798274.aspx
http://www.lightningtools.com/blog/archive/2010/01/14/creating-comparison-and-wildcard-filters-for-bcs-in-sharepoint-designer.aspx

With the external list working I then created another list and within this I created a column which was an lookup to my external list. I think SharePoint must realise this is a special case as even though you add a column as a lookup to another list it changes the type to an External Data column. I was then able to add new items to my custom SharePoint list and associate them with data from my external DB.

This was all fine but I then need to migrate some of the customers existing data from an Excel SpreadSheet into SharePoint. Adding items into SharePoint is a fairly easy task and something I have done numerous times over the years but the one new section to this was how I would query the external list and then set the value of the external data column in my custom list.

After some research I found a lot of people suggesting that you could simply use CAML in the normal manner, however during my testing the external list always had 0 items. I then found an MSDN article on Using the BDC Object Model so I decided to try this.

I copied the code, see full code below, from the Using Filters section of this article and added a reference for the BCS code, Microsoft.BusinessData.dll located in the ISAPI folder in the SharePoint root.

MSDN Example Code
  1. const string entityName = "Machines";
  2.         const string systemName = "PartsManagement";
  3.         const string nameSpace = "DataModels.ExternalData.PartsManagement";
  4.         BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
  5.         IMetadataCatalog catalog =
  6.           bdcService.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
  7.         ILobSystemInstance lobSystemInstance =
  8.           catalog.GetLobSystem(systemName).GetLobSystemInstances()[systemName];
  9.         IEntity entity = catalog.GetEntity(nameSpace, entityName);
  10.         IFilterCollection filters = entity.GetDefaultFinderFilters();
  11.  
  12.         if (!string.IsNullOrEmpty(modelNumber))
  13.         {
  14.             WildcardFilter filter = (WildcardFilter)filters[0];
  15.             filter.Value = modelNumber;
  16.         }
  17.  
  18.         IEntityInstanceEnumerator enumerator =
  19.           entity.FindFiltered(filters, lobSystemInstance);
  20.  
  21.         entity.Catalog.Helper.CreateDataTable(enumerator);

 

My first issue was I had no idea what the string variables at the top of the code should be set as. I tried debugging the code and I was able to find out the system name but it took some time. It turns out all the required details are in SharePoint Designer, see below.

  • Entity name should be the name at the top of the external content type information
  • System name should be the external system at the bottom of the external content type information
  • nameSpace should be the Namespace in the middle of the external content type information

SharePointDesignerExternalContentTypeDetails

Similarly to the example MSDN code I had setup one filter and this was a wildcard filter so I didn’t have to change that section of the code. All I changed was I set the filter value to be a value I was interested. When I ran the code everything seemed to be working as expected but I noticed if I was searching for ‘Test123’ and there was an item in the external list which matched exactly then it would return my result, however if I changed the code to search for ‘Test’ it didn’t find anything. I tried adding ‘*’ in various places to act as a wildcard but it made no difference to the results.

I expected since the filter was a wildcard filter I could search on only a part of the phrase and it would return what I was interested in but it wasn’t working. I checked the filtering functionality by adding a new item in my custom list and using the searching functionality for BCS and it worked in that I could search for only part of a phrase such as ‘Test’ and it would find partial matches like ‘Test123’. After confirming the out of the box functionality was working I rechecked my code but it seemed to match the example provided above. I then check and rechecked my settings in SharePoint Designer. but everything was as I would expect.

I then turned to a colleague Ross MacKenzie and we both went through the code together but even then we were unable to establish what was going wrong. He then suggested if I have tried ‘*’ around my search criteria why not try ‘%’ as this is the wildcard in SQL. After spending a good couple of hours looking about it finally started working with the code below.

Correct Wildcard Syntax
  1. WildcardFilter companyReferenceFilter = (WildcardFilter)filters[0];
  2.         companyReferenceFilter.Value = String.Format("%{0}%", siteRefCode.ToString());

 

Conclusion

I hope this helps others and stops them encountering the same issues as me as while the change to the example code was small it made a massive impact and from what I could see it wasn’t documented very well.

Happy SharePointing Smile

Advertisements

%d bloggers like this: