SharePoint 2010: External Content Types for DBA's

Jul 26, 2011 · 2 min read  ·
Share on:

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:

  1. Create relational tables/views in a SQL Server database.
  2. Add the database as a Data Source in SharePoint 2010.
  3. Setup Secure Store service for SSO in SharePoint if necessary.
  4. Create an External Content Type.
  5. Create Association Operation to associate FK/PK*.
  6. Add Read/Update/Delete/Create Operations to the type in SharePoint Designer.
  7. Create Lists based off of those Operations.
  8. Display pretty, functional lists within SharePoint and add, update and delete rows to your heart's content.
  9. 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.

SharePointSQL ServerNotes
External Content TypeTable, view or stored procedureOne content type, to one SQL Server object. So if you are working with 3 views, you will have 3 External Content Types
External Data SystemDatabase 
OperationQuery/StatementNot exactly, but you get the idea.
Delete operationDELETE FROMDeletes only one record at a time.
Read Item OperationSELECT TOP 1 *
Create OperationINSERT INTO
Read List OperationSELECT * FROMThis shows everything in the table unless there is a filter. You must return less than 2000 rows or it'll error out.
Update OperationUPDATE
Association OperationPrimary Key/Foreign Key
Business Data Connectivity Service
Set Permissions		GRANTAll 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!