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="<View><ViewFields><FieldRef Name='CITY'/></ViewFields></View>" 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!
