One of the biggest annoyances of SharePoint 2007 was the lack of a native ability to work directly with SQL Server tables. SharePoint 2010 has addressed that issue with External Content Types. ECT’s combine the best of both worlds — you can modify and manage tables as usual in SQL Server, and display them (somewhat) easily and beautifully in SharePoint.
It’s a bit of a process to understand how it works, but once you do, you’re set. The process can be broken down like this:
- Create relational tables/views in a SQL Server database.
- Add the database as a Data Source in SharePoint 2010.
- Setup Secure Store service for SSO in SharePoint if necessary.
- Create an External Content Type.
- Create Association Operation to associate FK/PK*.
- Add Read/Update/Delete/Create Operations to the type in SharePoint Designer.
- Create Lists based off of those Operations.
- Display pretty, functional lists within SharePoint and add, update and delete rows to your heart’s content.
- Create SharePoint lists using External Data if you want.
Below is a table that outlines the SQL Server equivalents (or as close as it comes) of ECT’s.
|External Content Type||Table, view or stored procedure||One content type, to one SQL Server object. So if you are working with 3 views, you will have 3 External Content Types|
|External Data System||Database|
|Operation||Query/Statement||Not exactly, but you get the idea.|
|Delete operation||DELETE FROM||Deletes only one record at a time.|
|Read Item Operation||SELECT TOP 1 *|
|Create Operation||INSERT INTO|
|Read List Operation||SELECT * FROM||This shows everything in the table unless there is a filter. You must return less than 2000 rows or it’ll error out.|
|Association Operation||Primary Key/Foreign Key|
|Business Data Connectivity Service
|GRANT||All access is denied by default. BDC permissions are set in Central Administration.|
I developed these skills while working on a project that uses PowerShell to gather information about my servers and enter them into SQL Server database. From there, I setup a SharePoint site that not only displays that information in a useful way, but also manages the data from the web front-end. Hooray for SP2010!