Find Items in External List Programmatically

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

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

CAML Query Join Lists

I was in a situation where I had a parent list with some details and then a separate list for child items. The child list had a lookup to the parent list to make the association between the two. I needed a query which allowed me to search on some fields in the parent list and some fields in the child list.

Approach

When I was thinking about this I came up with a few different ideas which are each discussed below:

1. Multiple Caml Queries. One option was I could run a Caml query against the parent list to get the items which meet the parent item criteria and then do another Caml query looking for items in the child list which are associated to the items returned in the parent Caml query but also meet the filtering on the child list. Obviously this is not idea as it would involve a decent amount of code to do but would also be fairly inefficient as well.
2. Linq to SharePoint. While this would probably have been the easiest approach as you can very quickly join two lists using Linq, once you have your entry classes generated,  but I decided against this approach. My reasoning was based around a solution design perspective. I decided that while Linq to SharePoint would prove beneficial in this situation it would require adding and maintain another element in the solution thus increasing the complexity.
3. Join Caml Query. This was something I had never done before but I was aware it was one of the new features in SharePoint. I done some research and it seemed like a fairly straightforward approach so I decided to go with this.

 

Method

There are various articles out there on how to join lists via a Caml query so I won’t go into a great deal of detail only the points I found interesting.

The first thing is the Caml query will be created as you normally would be it needs to be run against the child list not the parent list.

In order to map the lists together you use the Joins property of the SPQuery object. As I mentioned there is a lot of information out there for more complicated joins but in my situation it was simply joining parent and child lists together, see example below.

Join Lists

1. StringBuilder joinDetails = new StringBuilder();
2.         joinDetails.Append("<Join Type='INNER' ListAlias='ParentListName'><Eq><FieldRef Name='ChildListLookupColumnName' RefType='Id'/><FieldRef List='ParentListName' Name='ID'/></Eq></Join>");           

 

With the join created the next step is to set up the projected fields, these are the fields from the parent list which you want to display or filter against in the where clause, see below. The Name element is any name you want to give it and this will be used in the view fields and query sections of the SPQuery object. As far as I can see the type is always Lookup. The ShowField element is the name of the field in the parent list you want to map to this new field.

Projected Fields

1. StringBuilder projectedFields = new StringBuilder();
2.         projectedFields.Append("<Field Name='ParentListField1' Type='Lookup' List='ParentListName' ShowField='Field1'/>");
3.         projectedFields.Append("<Field Name='ParentListField2' Type='Lookup' List='ParentListName' ShowField='Field2'/>");
4.         projectedFields.Append("<Field Name='ParentListField3' Type='Lookup' List='ParentListName' ShowField='Field3'/>");
5.         projectedFields.Append("<Field Name='ParentListField4' Type='Lookup' List='ParentListName' ShowField='Field4'/>");
6.         projectedFields.Append("<Field Name='ParentListField5' Type='Lookup' List='ParentListName' ShowField='Field5'/>");
7.         projectedFields.Append("<Field Name='ParentListField6' Type='Lookup' List='ParentListName' ShowField='Field6'/>");

 

You must ensure all projected fields are listed in the view fields SPQuery property. Again anyone who has done basic Caml queries will have seen this before the only consideration is when using fields from the parent list you have to use the name set in the projected fields not the name of the column in the parent list, see below.

View Fields

1. StringBuilder viewFields = new StringBuilder();
2.         viewFields.Append("<FieldRef Name='ParentListField1'/><FieldRef Name='ParentListField2'/><FieldRef Name='ParentListField3'/>");
3.         viewFields.Append("<FieldRef Name='ParentListField4'/><FieldRef Name='ParentListField5'/><FieldRef Name='ParentListField6'/>");

With the join between the lists done and the mapping for the fields in the parent list you can then write your Caml query as per normal and filter against details in the parent list, see below for a simple example Caml query.

Example Caml Query

1. sb.Append("<Where><IsNull><FieldRef Name='ParentListField1' /></IsNull></Where>");

 

The final element is to associate all these with your SPQuery object and then pass this to the list GetItems method.

Associated with SPQuery

1. var query = new SPQuery();
2.         query.Joins = joinDetails.ToString();
3.         query.ProjectedFields = projectedFields.ToString();
4.         query.ViewFields = viewFields.ToString();
5.         query.Query = sb.ToString();

 

Getting to this point did take some changing of the various Caml query properties and it was slightly frustrating but I don’t think this is particularly related to joins within Caml but more of a general issue with Caml.

Issues

There was one issue which seems particularly related to joins and this was when trying to get a DataTable of the results instead of a SPListItemCollection, see example below. This throws a NullReferenceException, see stack trace below, and it seems the only way around this is to work with the SPListItemCollection and not a DataTable

Get DataTable

1. var queryResults = list.GetItems(query).GetDataTable();  

 

Stack Trace

The error was System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.SharePoint.SPFieldMap.EnsureFieldArray()     at Microsoft.SharePoint.SPFieldMap.GetFieldObject(Int32 columnNumber)     at Microsoft.SharePoint.SPListItemCollection.GetVisibleFieldIndices(Boolean isJsGrid, Int32[]& arrVisibleFieldIndices, Int32& iVisibleFieldCount)     at Microsoft.SharePoint.SPListItemCollection.GetDataTableCore(Boolean isJsGrid)

Conclusion

I think that the join functionality in SharePoint 2010 is a useful feature, however given the fiddly nature of this along with how easy it is to accomplish the same thing using Linq to SharePoint will mean a lot of people won’t use this approach. In my circumstances it works very well and with a few extra lines of code, compared to a normal Caml Query, gives me a lot of extra functionality.

Incorrect date format when using SharePoint DateTimeControl in application page launched via ModalDialog

I was working on a project where I needed to create a custom UI for a customer in order to do some complex logic and form formatting. The form was developed as an application page which was deployed into the 14 hive and it was presented to the user via a Popup using the JavaScript ModalDialog functionality. The modal dialog was being launched from a custom web part which was in a subsite below the main site

As part of the form I needed to prompt the user with a field which would capture a date so I used the SharePoint DateTimeControl under the Microsoft.SharePoint.WebControls namespace. I have used this several times before so I didn’t think anything of it until when testing I noticed the date format was US instead of UK. I have had this before so I added some code in the page load of my application page to set the controls regional settings to match the current web regional settings, see below.

Set DateTimeControl Region

1. DtDueDate.LocaleId = Convert.ToInt32(SPContext.Current.Web.RegionalSettings.LocaleId);

 

This normally works, however when I started testing it was still not setting the region to UK, 2057, instead it was still set to US, 1033. While debugging I noticed the SPContext details where indicating the current web was the top level web not the subsite from which the modal dialog was getting launched.

This explained why the DateTimeControl was displaying in the wrong format as the regional settings at the top level site where set to US. I changed the regional settings at the top level web and the control starting displaying in UK format.

While I was happy this was working I was slightly confused as to why the context was showing the current web to be the root web not the web for the subsite. After some digging around I realised that while I was on the subsite in the main browser window when I was calling the popup I was passing in a relative URL, see examples below, and this meant the context in the popup used this URL not the location from which it was getting called.

Original URL of popup window where context is the root site

“/_layouts/ApplicationPages/Page.aspx”

Adjusted URL of popup window where context is the subsite

“/subsite/_layouts/ApplicationPages/Page.aspx”

This caused me some issues but once I got my head around it it made perfect sense so hopefully this will help others or help me in the future when I forget all about it