Business Connectivity Services (BCS) with SQL Server using SharePoint Designer 2010

The most easy way to use Business Connectivity Services (BCS) is to use SharePoint designer 2010. However, SPD is limited to use only with SQL Server, .NET Type and WCF Service.This article is going to demonstrate how easy to use BCS with SharePoint designer 2010.

  • Start SharePoint Designer 2010, Open your SharePoint site, Go to External Content TypesNew External Content Type

  • Click “Click here to discover external data sources and define operations” option ,

  • Click “Add Connection” button and select “SQL Server” as a data source.

  • Provide required credentials to connect SQL Server database,

  • Once connected you should able to see the SQL Server database,Expand Tables and Right click on the table which you want for BCS and choose Create All Operations. It will create CRUD operations for this table. If you don’t want all the operations then choose based on your need. Follow the wizard and click Next in Operations Properties window.
  • Set Parameters Configuration and click Finish
  • Once done you should see something like this, Save your changes.
  • Go to Central Administration – Application Management – Manage service applications and click Business Data Connectivity Service. Select the content type and set the Permissions. In case if you missed this step you will get “Not authorized” error in your BCS library.
  • Create new External list and select External Content Type
  • Here is the result, Use SharePoint list operations to verify CRUD functionality.