Aim of this walk-through: Use data held in a database as a value list in SharePoint.
SharePoint 2010 can use external data as lists. In this example I’m accessing a table of Airport data held in SQL Server, since I felt that 10,000 entries is not something you want to typically maintain in a SharePoint list.
SharePoint alllows you to connect to external data sources from a .NET assembly, SQL Server directly or WCF.
I prefer to use WCF, since I’m not convinced that many scenarios would want SharePoint having direct access to the database. In addition, .NET code would be best managed by a WCF service anyhow.
Using SharePoint Designer, I define a new External Content Type called ‘Airports’:
Under External Content Type Operations, click on the hyperlink (“Click here to discover…“)
On the following screen, click the “Add a new connection” button.
In the following dialog, enter the URL of your WCF endpoints.
You will then see all the web methods listed. By right-mouse clicking on each you will be able to assign various operations to them. At the moment we are just interested in ‘Read Item Operation‘ and ‘Read List Operations‘.
In my case:
- GetAirport returns a specific airport (Read Item Operation)
- GetAirports returns all airports (Read List Operation)
GetAirport and GetAirports are simple. Go through their respective wizards and assign the parameter ‘code’.
After you have completed the operations, click ‘Save’.
On the External Content Types, you will be able to right mouse click and define an External List on Airports.
Give it a name, and click OK. We are now ready? Unfortunately not quite yet! If you try and view your new list, you will see that you get a message “Access denied by Business Data Connectivity“.
Go to: ( This would be too many screen shots, and I’m sure you can work this out)
Central Administration -> Application Management -> Manage Service Applications -> Business Data Connectivity Services
Click on your list (which should now appear here) and in the following screen click ‘Set Object Permissions’.
You can now add groups of users to use this data connection. In this quick example I’ll just assign Administrator in the first instance.
TIP: Once this is applied, it will take some time to become active in SharePoint. You can force it to apply right away by going to:
Central Administration -> Application Management -> Manage Services on Server-> Business Data Connectivity Services
Stop this service, and then Start again.
If you then browse to your new external list:
Now we have a list with.. 10,000 items! The next post will look at how we make this more usable.