Business Connectivity Services Using Designer In SharePoint 2016

SharePoint provides connectivity to external lines of business in many ways. Business Connectivity Services (BCS) is one such offering from SharePoint. With BCS, we can connect to any line of business through Visual Studio Template. However, we can also implement BCS using SharePoint designer by which we can connect directly to SQL Server table. In this article, we will see how to perform CRUD operations between SharePoint list and SQL Server table using Business Connectivity Services.

As the first step, create a table in SQL Server table with a schema of your choice.



Ensure that you create a primary key in the table as it will act as the connection link between SharePoint List and SQL Server table. Here, it will be ‘Name’. If Primary key is not set, we will get the below error while creating BCS connection.



Finally, we will be trying to create an external SharePoint list like the one below that will be connected to SQL Server using BCS.



So, let’s create a BCS connection using SharePoint designer. There is no specific designer available for SharePoint 2016, so spin up SharePoint Designer 2013 and connect to the SharePoint Site where we will be creating the external content type list.



From the left pane select ‘External Content Types’ and select ‘External Content Type’ from the top bar.



This will open up the page that can be used to manage the settings for the external content type. Click on “Click here to discover external data sources and define the operations”.



Click on Add Connection to create the BCS connection.



It will open up the External Data Source Type selection window. Select ‘SQL Server’.



Specify the database server name and database name.



This will create a connection to the SQL Server and will list out the tables in the database.



Right click on the table and specify the kind of operations that can be done using the BCS Connection. In this case I am going with ‘Create All Operations’ to define complete CRUD.



Click on Next.



Specify the primary key parameter in the ‘Identifier’ field.



You can either click on ‘Finish’ or move to the next window by clicking on ‘Next’ where we can specify the filter to fetch only the data that satisfies the filter condition from SQL Table to SharePoint List.



Clicking on Finish will create the BCS Connection.



Click on Save button to save the External Content Type created.



Now let’s head over to the SharePoint Service Applications page and select the ‘Business Data Connectivity’ Service Application.



The newly created external content type will be listed in the business data connectivity service page.



Right click it and select ‘Set Permissions’. Add the users to the external content type object.



Now, go to the SharePoint site and select ‘Add an app’.



Select ‘External List’ template.



Specify a name to the external list and browse the external content type for the data source field.



Select the previously created external content type.



The external list will be created based on the below configuration.



Thus a new external list of the name ‘Employee BCS’ has been created.



Going inside the list we can see the contents that were present in the SQL table.



Now let’s create a new list item in the external list.



A new record by the name ‘Raju’ has been created in the external list.



This should update the table in SQL Server as well.



Similarly let’s try deleting a record in the SQL server table.



Click on Yes.



The record has been removed from SharePoint List as well.



Summary - Thus we have created and tested out the two way CRUD between SharePoint List and SQL Server table using Business connectivity service.