Create a Business Data Connectivity Service using SharePoint Designer 2013

In this post, we will discuss how we can create a business data connectivity service using SharePoint designer 2013 in SharePoint 2013. Business Connectivity Services (BCS) provides the infrastructure that enables SharePoint to bring data from those external systems into a central system.

Business Connectivity Services (BCS) in SharePoint

Now we will discuss, Business Connectivity Services (BCS) in SharePoint 2010. BCS stands for Business Connectivity Services in SharePoint. This is nothing but an application service of Microsoft SharePoint 2010. Previously in MOSS 2007, it was popularly known as the Business Data Catalog (BDC).

Through BDC we can only read external data. But through BCS allows accessing external data by using a CRUDQ (Create, Read, Update, Delete and Query) approach. Here external data source can be anything like Line of Business applications (LOB), web services, databases etc.

The service is based on a core engine, named Business Data Connectivity (BDC) that uses a BDC Server Runtime to connect with various data providers.

Data Providers supported in BCS are:

  • Database:
    This can be any database based on Microsoft SQL Server, Oracle, OLE DB data providers, or ODBC data providers.
  • Web/WCF Services:
    Any SOAP web service or any Windows Communication Foundation (WCF) service.
  • Custom:
    . NET Assemblies You can define a custom Windows .NET assembly that will wrap any back-end data source. Custom Connectors You can use or build custom connectors for reading and writing data from external data sources.

Internally BDC Server Runtime will store configurations and shapes of data sources in a dedicated repository, which is called the BDC Metadata Store and corresponds to a dedicated database file.

Every item that you read or write from an external data source is assigned an External Content Type (ECT) and can be consumed through an External Lists.

Also, Microsoft SharePoint provides some out of box web parts for rendering, filtering, and searching data that are available through BCS.

The user can also work offline with BCS from different client applications like Microsoft Office 2010, Microsoft Outlook 2010 etc. The offline data will be saved in the local storage of the client PC, within the current user profile folder. For security reasons, the data is also encrypted.

If the user changes any of the items while offline when she goes back on-line, the BDC Client Runtime will be able to synchronize the client-side data cache with the serverside online data.

Disadvantages of external lists in Business Connectivity Services in SharePoint

This SharePoint BCS tutorial explains what are the disadvantages of external lists in BCS (Business Connectivity Services) in SharePoint. There are various limitations of the external list as compared to the normal SharePoint list. By using external lists we can access external data from our SharePoint.

Below are the options are not available in SharePoint 2010 external list.

  • Approval of items is not supported.
  • Alerts and Attachments are not supported directly.
  • Check-in and checkout of items are not supported.
  • Using standard site content types in External Lists is not supported.
  • Drafts of items are not supported.
  • Send-To operations are not supported.
  • List event handlers are not supported.
  • Datasheet View is not supported.
  • Export to Excel option is not available.
  • Querying through LINQ to SharePoint is not supported.
  • Document templates are not supported.
  • Versioning of items is not supported.
  • No Item Level Permissions
  • Starting workflows from items is not supported, but workflows can read or write to External Lists through the SPList object.
  • Validation formulas are not supported.

But few features have been added in SharePoint 2013.

  • Sorting & Filtering for External Lists.
  • Export to Excel option included etc.

Create a Business Data Connectivity Service using SharePoint Designer 2013

1. Open SQL server 2012 and create a database name BCSDB and Table and add some item. See in fig

business data connectivity service sharepoint 2013
business data connectivity service sharepoint 2013
business data connectivity service sharepoint 2010
business data connectivity service SharePoint 2010

2. Now Open SharePoint Designer and Open your site in SharePoint designer 2013.

3. Now in Left Navigation Menu, under Site Object, you select External Content Types. See in fig

business data connectivity service sharepoint 2016
business data connectivity service SharePoint 2016

4. External Content Types you create by selecting the Icon External Content Types from the ribbon. See in fig and form is appear to See in the second fig

business data connectivity service sharepoint online
business data connectivity service SharePoint online
business data connectivity service sharepoint 2013 configuration

5. Now you click on the link below to define data source. See in fig and fill the details

business connectivity services sharepoint 2016
business connectivity services SharePoint 2016

6. Now choose to Add connection. See in fig

create external content type in sharepoint online
create external content type in SharePoint online

7. A dialog box appears and select SQL server. See in fig

create external content type sharepoint 2013
create external content type SharePoint 2013

8. In the SQL Server Connection add the following details –
– Database server: the name of your server
– Database Name: the name of your database
– All to the following default. See in fig

create external content type sharepoint 2010
create external content type SharePoint 2010

9. While you wait a moment for a connection. See in fig

create external content type sharepoint 2016
create external content type SharePoint 2016

10. After successful connection, see in fig.

create external content types using sharepoint designer 2013
create external content types using SharePoint designer 2013

11. Now you right click on the Table, which is the action you choose to Create All Operations. See in fig

create external content types using designer
create external content types using designer

12. Click on Next

external content type sharepoint 2010 step by step
external content type SharePoint 2010 step by step

13. The selected columns appear to show up SharePoint List :
– ID: Map to Identifier check the Properties window and check Show In Picker Identifier. See in fig
– ColumnName: In the properties window check Show Picker. See in fig
– The Other columns you also check Show In Picker

external content type sharepoint 2013 step by step
external content type SharePoint 2013 step by step
connect sql server to sharepoint 2013
connect SQL server to SharePoint 2013
connect sql server to sharepoint 2016
connect SQL server to SharePoint 2016

14. Click on finish. See in fig

connect sql server to sharepoint online
connect SQL server to SharePoint online

15. After the task to display the column you select Icon Creates List & Form. See in fig

Create external content types for SQL Server in SharePoint
Create external content types for SQL Server in SharePoint

16. A dialog box appears: Select yes

Create external content types for SQL Server in SharePoint 2013
Create external content types for SQL Server in SharePoint 2013

17. Wait to save the configuration program.

18. A dialog box appears: Click ok. See in fig

Create external content types for SQL Server in SharePoint 2016
Create external content types for SQL Server in SharePoint 2016

19. Back to your site and Select the list. See in fig

Create external content types for SQL Server in SharePoint online
Create external content types for SQL Server in SharePoint online

20. You will see the error occurs because users cannot be added to services. See in fig

create external list sharepoint 2013
create external list SharePoint 2013

21. For adding User in BCS services, go to central administration and Select Manage service application under Application Management. See in fig

sharepoint 2013 connect to external data source
SharePoint 2013 connect to an external data source

22. Now you choose Business Data Connectivity service

sharepoint 2013 connect to sql server
SharePoint 2013 connect to SQL server

23. The external content type you choose and select Set Object Permission from the ribbon. See in fig

sharepoint to sql server
SharePoint to SQL server

24. Here add user and you choose the permissions for that user. See in fig

sharepoint connect to sql server
SharePoint connect to SQL server

25. Back to your site and Press F5 to refresh page error, then see the following results. See in fig

connect sharepoint list to external data source
connect SharePoint list to the external data source

26. You can edit Item and save it. See in fig

sharepoint online external data
SharePoint online external data

27. Now back to the SQL server and Execute a query for showing the result in Table. See the changes. See in fig

Creating Business Data Connectivity Service using SharePoint Designer 2013
Creating a Business Data Connectivity Service using SharePoint Designer 2013

You may like following SharePoint BCS tutorials:

I hope this article will be helpful to create Business data connectivity services using SharePoint designer 2013 in SharePoint 2013.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>