Configuring an SPDataSource to access data from External Lists tied to External Content Types

The SPDataSource, used in conjunction with External Lists and External Content Types, is a powerful way to access traditional list data in SharePoint.  The SPDataSource is very useful for populating dropdowns, checkbox lists, and other data driven page layout elements.  Using the SPDataSource tied to an External List is very similar to one tied to a traditional SharePoint list, but there are a few gotchas and things you can do to make your life easier.

So, let's walk through a scenario of how we might take data from a database, in our case SQL Server 2008, create an External Content Type (ECT), create an External List (EL) from the External Content Type, and expose the data in that list to and end user in a dropdown that is part of a page layout.

Time for nerd stuff - spinner hats ON!!!

(Disclaimer:  this is not me, but is an epic picture nonetheless.)

First, go to your database, and add a table named "CITIES".  Add a nvarchar(25) field to it called "CITY" that is the primary key.  Then, create a view on that table - something like vwCities, etc.  This view is going to form the foundation for our ECT, and thus our External List (EL).

I advocate tying the ECT to a sql server view for all read operations rather than to the table directly. This is primarily because it is easy to change a view, and even flatten a highly normalized table structure, thus making your external list's implementation much easier to support, tweak, and use in development of page layouts and search. You can change a table out from underneath a view, even rewrite or optimize the view, without having to rebuild your entire BCS model, External Content Types, and External Lists.

Second you need to create your External Content Type (ECT).  We're going to stick with SharePoint Designer for this demo.  Open up your SharePoint site collection's root site, click External Content Types in the left hand panel, then click the new External Content Type button in the ribbon at the top right of the screen.  Configure your external system, make sure you type a name like "City" as the Name of the ECT, then create your operations.  In our scenario, just create a Read List and Read Item method - leave the others out.

I realize I'm glossing over the finer details here, but I am assuming some level of comfort with creating ECTs in this demo.  Naturally using SPD is not a requirement here, and a Visual Studio is often an easier way to create robust BCS models.  We've used SPD here the sake of simplicity and brevity in deployment and configuration.

Once you've created your External Content Type, create the External List. This is easily done in SharePoint Designer 2010 by clicking the "Create List and Forms" button in the ribbon. Unless InfoPath (IP) is a part of your solutions' architecture, don't bother with checking the checkbox to create the IP form. For purposes of data or list structure the IP form is not needed.

Next, you're going to create the SPDataSource object in your page layout. First, you probably put your External List at the top site in the site collection. This is a good practice. You don't want to have to build SPDataSources smart enough to figure out a dynamic path to your list if you can avoid it. You're going to want to add the WebUrl and ListName parameters to your list. Don't query by ListID. If you get the error "List does not exist" after editing a DFWP or SPDataSource in SharePoint Designer, check to see that it did not add a ListID for you in the SPDataSource tag. Delete that and go by name.

Here's the name parameter:

<asp:parameter defaultvalue="Cities" name="ListName">
</asp:parameter>

Here's the WebUrl parameter.

<asp:Parameter Name="WebUrl" DefaultValue="{sitecollectionroot}"/>

** Note:  You can also use the older "/" instead of "{sitecollectionroot}", but I like the sitecollectionroot param as it is more accurate when read and the newest syntax.

The above parameters tell the SPDataSource to query the Cities list in the site collection root.

In all, here's the syntax for an SPDataSource accessing an External List named "Cities":

<sharepointwebcontrols:spdatasource datasourcemode="List" id="CitiesDataSource" runat="server" selectcommand="&lt;View&gt;&lt;ViewFields&gt;&lt;FieldRef Name='CITY'/&gt;&lt;/ViewFields&gt;&lt;/View&gt;" useinternalname="true" useserverdataformat="true"><selectparameters><br /><asp:parameter defaultvalue="Location Cities" name="ListName"><br /><asp:parameter defaultvalue="{sitecollectionroot}" name="WebUrl"><br /></asp:parameter></asp:parameter></selectparameters></sharepointwebcontrols:spdatasource>

 A few peculiarities as you craft your CAML query of an External List:

• SPDataSources tied to External Lists really likes ViewFields specified in the CAML query.  You may get some weird execution errors until you add a few fields.  I've had CAML queries work beautifully without the ViewFields attribute of the CAML query set, only to come in the next day to a weird Correlation ID error.  When I looked up the error it said something along the lines of the query exceeding the size allowed, etc.  I think this may be when you have very wide tables, or attributes of the ECT and thus the list column tied to really long fields.  In my case, I had several nvarchar(4000) fields.
• The RowLimit attribute of the CAML query for external lists does not seem to limit the result set coming back. I've had to resort to filtering the number of rows returned in the XSLT at times.
• HTML Encode your CAML.  Yes, you might get away with a typed in CAML query, but it won't be as bulletproof at run time and is required for many syntax circumstances.

The next thing to do is tie a dropdown or some other data driven component to your SPDataSource, just as you would any other SPDataSource.

In this scenario, I want a dropdown tied to my CitiesDataSource SPDataSource that has a default value.  I'd also like a custom javascript method to fire when I change it.

<asp:dropdownlist appenddatabounditems="true" cssclass="ddl_city" datasourceid="CitiesDataSource" datatextfield="CITY" datavaluefield="CITY" id="ddlCity" onchange="javascript:ChangeCity(this);" runat="server"></asp:dropdownlist>

   

<asp:listitem selected="True" text="City" value="City"></asp:listitem>

So, there you have it - a dropdown tied to an external list, which is tied to an external content type that is tied to a view in a database!

What is it you say you do here? Mmmm yyeeaaahhh.

SharePoint 2010 BCS and Search over anonymous access

Recently a colleague and I labored through an entire day working with the Business Data Connectivity Service (BCS) and getting it to search properly. Our mission? Take a line of business (LOB) data source in SQL Server 2008, encapsulate it as an External Content Type (ECT) in SharePoint 2010, and create a list from that ECT that is searchable via anonymous access over the internet - a combination of scenarios rarely encountered. That list would then have a custom profile page built to view the data. The list (and ECT) should be searchable from the typical search components of SharePoint 2010 such that, when an appropriate result was found, they could click and get our custom view page - over anonymous and authenticated acces.

Sounds easy, right? Been done a thousand times with classic content types in SharePoint, right? Easy to configure the search meta data you say, right?

Brrrrrrrrnnnnggggt.

The BDSC creates an interesting search paradigm. You aren't working with a normal content type. It's simple enough to take a traditional content type and map it's site columns to managed properties in SharePoint search. However, with an ECT you don't get the benefit of a Site Column.

Furthermore, you have to index the BDCS, not a SharePoint list by url filter. So, the content source becomes an issue.

At first, we thought applying a content source for all BCS entities would work. Seemed logical. Just have one and then search by property value from there. However we soon found that this was not practically possible. We found that any time we modified the ECT, we needed to completely delete and recreate the ECT, not just update it and propagate those changes to the list via SharePoint Designer (SPD).

When we needed to add a field, we needed to delete the ECT and completely recreate the External List fielding the ECT. Otherwise the search wouldn't work.

Furthermore, we found that a Content Source in SharePoint search only worked with one ECT assigned to it. The first ECT added worked. Any subsequent ones did not. So, we ended up with a one to one ratio of ECTs to Content Sources in SharePoint search.

In short, to make LOB data searchable via BDCS, we had to do the following:

1. Create data structure (table or view worked fine) using Sql Server management tools
2. Create External Content Type using SharePoint Designer 2010 and save it.
   
   

   Make sure your data access is set appropriately if you need to access it anonymously. The BCS data source has to be configured as Revert To Self to be available anonymously, which is different than the way you must first set it up to initially configure the ECT.

   $apps = Get-SPServiceApplication$bcs = #Do something appropriate here to get the app that is#BCS. If you’re doing this by hand, just type $apps and look#for the Business Data one, then index into it like $apps[i].#If you’re doing it for automation, filter by
   #$_.GetType.FullName (not $_.TypeName, which is localized).$bcs.RevertToSelfAllowed = $true

   Then, alter your data source to select "BCS Identity" - this will cause SharePoint to revert to the application pool's identity for accessing the BCS data source.
   
   
   
   Make sure the app pool account has access to read your BCS entity in the service manager for the BCS. This is configured in Central Administration, the BCS service application. Make sure your app pool account has access to the Sql Server.

3. Click "Create Lists & Forms" button in SPD to propagate ECT to a SharePoint list.
4. Make sure the new External List is showing data. Do not proceed until you've validated it has access anonymously and authenticated.
5. Log into Central Administration and go to Search Administration. Under Content Sources, create a new Content Source. St it to "Line of Business Data", but DO NOT select the "All BCS" entities option. Rather, select ONLY the ECT you just created.
6. Save the Content Source, and run a full crawl of the Content Source. The number of successes should be close to the number of records in your data source (table or view).
7. Search for an item in your data source, and you should be presented with a result that links you to a profile page.

The BCS is much improved over SharePoint 2007's version. There are still some things to work around in difficult scenarios but it can be done.