Saturday, May 14, 2016

How to Create External Lists from SQL Server using BCS in SharePoint 2016

Business Data Connectivity Services enables SharePoint 2013/2016 and Office 2013/2016 clients to interact with data that doesn't live in SharePoint.  BCS allows you to integrate external data into SharePoint, External list provides familiar list like user experience. Lets see how to create a External List in SharePoint that grabs the data stored in SQL Server Database and display it in SharePoint 2016 without writing a single line of code.

Prerequisites: Before creating an external content type-External List, You need to have Business Data Connectivity Service (BCS) application and/or secure store service application configured in your SharePoint farm! Here is how to Create and configure BDC service application in SharePoint 2016

Step 1: Create External Content Type in SharePoint Designer 2013:
Follow these steps to create an External Content Type using SharePoint Designer 2013.
  • Open your SharePoint 2016 site in SharePoint Designer 2013
  • Select External Content Types in the left Navigation. Click on External Content Type under New Group.
    sharepoint 2013 using bcs with sql server database
  • In the External Content Type creation page, Enter the Name and Display Name for the external content type. Click on "Click here to discover external data sources and define operations" link next to "External System".
    sharepoint 2010 bcs sql server example
  • This takes you to the Operation Designer page. Now, Click on "Add a Connection" button to connect to the database. Select Data source type as SQL Server.
    sharepoint 2016 bcs sql server tutorial
  • Setup SQL Server connection properties. Enter the Database server name, Database name and choose the "Connect with impersonated windows identity" and enter the Application ID you created to connect with SQL server in Secure store service.I've created a target application ID in SharePoint 2016 secure store service. Refer this article on How to Create Target Application ID in Secure Store Service in SharePoint 2016
    sharepoint 2013 bcs connect to sql server
  •  Once the connection is made with the provided database, Data Source Explorer will be filled with the database objects we specified. Now select the relevant table to which we are going to connect from SharePoint. Right click the table and select the option "Read List Items". Repeat this step for "Read List" operation as well. 
    sharepoint 2013 using bcs with sql server database
  • You'll get Operation Properties wizard. Run through this wizard, specify appropriate settings such as filters, necessary fields and click on Finish button once done. 
    sharepoint 2016 using bcs with sql server database
  • You'll be presented with a list of operations that your External Content Type can do, as shown below.
    sharepoint bcs sql server
  • Hit Save button in SharePoint designer to complete creation of external content type.

Step 2: Create External List in SharePoint 2016:
The Next step is to expose that SQL table through an External List.
  • Open your SharePoint site in Browser, Add an App >> Choose External List
  • You will be presented with an External Content Type Picker and you will be able to view the Content Type you created a while ago. Choose that content type, Provide a name to your External list and hit OK
  • Now you can see your SharePoint list populated from the database table!
You may have to explicitly provide permission to External Content type through BCS service application if you have not set meta data permission to all users already! Last but not least: We can't compare External Lists with standard SharePoint lists. See my another post on some of the significant limitations when using external lists: 25 Limitations of External Lists in SharePoint



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...