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.
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.
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
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.
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.