How to Implement CRUD Operations on SQL Table Using External Content Type in SharePoint?

Businesses face complications when they want to display their organizational needs in some external content with the help of SharePoint sites. Fortunately, the SharePoint server is a savior that allows businesses to connect to the external data seamlessly. There are many ways to connect the external data from a SharePoint site and one of them is to create an external list in a SharePoint site to communicate with the external data. The look and feel of an external list is the same as the normal SharePoint list. In this blog, we will understand how users can create an external data column to use external data from another document library/list. Understand how to elaborate external data and display it using images and Screenshots for a better understanding of SharePoint developers.

Enlisted below are the tools and services that should be installed or configured prior to proceeding with this blog:
SharePoint Designer 2013.
Business Data Connectivity Service. Refer here to configure.
Secure Store Service Application. Refer here to configure.
Now we will start exploring how businesses can create and connect with external data using SharePoint services. Let us start with how to create an external content type.

1. How do I Create an External Content Type in SharePoint?

Let us understand step by step how we can create an external content type in SharePoint.

1. Open SharePoint Designer 2013 🡪 Open Site 🡪 enter the URL of the site and credentials.

Open SharePoint Site

2. The next step is to click on External Content Type from the left pane 🡪 click on external content type from the ribbon, it will create a new external content-type:

External Content Type SharePoint

3. Give content type a name and then specify Display Name. If the Display Name is not specified, the SharePoint designer will take Display Name the same as Name.

Display Name SharePoint

4. Now the next step is to press on the “Click here to discover external data source and define operations” and it will add a data source.

Discover External Data SharePoint

5. Click on Add connection button 🡪 select SQL Server from the drop-down. We can select .NET Type or WCF Service as a data source, but for this article, we will go with the SQL Server.

WCF Service SharePoint

6. Enter Database Server, Database Name, check radio button Connect with Impersonated Windows Identity, and enter Security Store Application ID that is created during Secure Store Service Application configuration. Click on the OK button.

Security Store Application ID SharePoint

7. Expand the connected database and tables’ tabs 🡪 select a table that needs to be created as an external list. Right-click on that table and click on Create All Operations.

Create All Operations SharePoint

8. It will open a wizard 🡪 click Next 🡪 Select the column which is an identifier for the table and check the checkbox Map to Identifier.

Map to Identifier SharePoint

9. Now, select a column that the user wants to show in the picker and tick the checkbox Show in Picker.

Show in the Picker SharePoint

10. Users can select any column that they require and check the checkbox option ‘Required’ and ‘Read-Only’ accordingly.

11. Click on next, this screen will be used to set filters on the external content type.

12. It is recommended to add a limit filter to avoid larger result sets. To add a limit filter, click on Add Filter Parameter button.

Add Filter Parameter SharePoint

13. From the right pane, click on Click to Add, provide a name for the filter, and select the Limit option from the Filter Type drop-down list. Click on the OK button.

Filter Type SharePoint

14. Enter a value in Default Value, now you can insert a limit as per the requirement. Click on the Finish button.

Default Value SharePoint

The next step is to understand the necessary constraints and add rules and permissions to specific content types.

2. Permissions: BCS External Content Type

1. To grant permission on external content type, open Central Administration 🡪 Application Management 🡪 Manage service applications.

Manage Service Applications SharePoint
Business Data Connectivity Service Application SharePoint

2. Click on the Business Data Connectivity Service Application.

Set Permissions SharePoint

3. Select the External Content Type and select Set Permissions from the drop-down.

4. Enter Users/Security Groups that require permission and click on Add.

Users/Security Groups SharePoint

5. Check the available option Execute, and check ‘Propagate permissions to all methods of this external content type. If you do so, you will overwrite existing permissions’ if permission is required to set in all methods of the content type only. Then click OK.

External Content Type

3. Permissions: Members to ‘Secure Store Application ID’

1. To enable CRUD operations on an external list, the users can give necessary permissions to the Secure Store Service Application.

2. Users who want to consume external lists must be members of the created Secure Store Application ID.
3. To set a member of the Secure Store Application ID, open Central Administration 🡪 Application Management 🡪 Manage service applications.

Manage Service Applications SharePoint
Secure Store Service Application SharePoint

4. Click on Secure Store Service Application.

5. Select the Secure Store Application ID and click Edit. To create a Secure Store Target Application follow this article.

Secure Store Target Application

6. Click Next.

Edit Secure Store

7. Click Next.

Edit Secure Target SharePoint

8. Add Users/Security Groups who are going to use this external list for the members and then click on OK button.

Edit Secure Application SharePoint

4. How to Create an External List in SharePoint?

1. Open SharePoint site in SharePoint designer, click on External Content Types from the left panel and select the newly created external content type.

External Content Types

2. From the ribbon, now you can click on the Operations Design View.

Operations Design View SharePoint

3. To create an external list, expand the connected database and tables’ tabs 🡪 select a table that needs to be created as an external list 🡪 click Create Lists & Forms option from the ribbon and it will open “Create List and Form” pop-up to create an external list.

4. Check the radio button Create New External List and provide List Name.
5. Provide Read Item Operation and System Instance. They will take you to the default value if the user does not want to modify the value to some other.

Read Item Operation

6. Click on OK and a new list is created.
7. To check the newly created list, go to the Site contents of the site.

Site contents SharePoint
Site Contents SharePoint

8. As shown in the image below, now you can Click and see the newly created list and take a complete look and feel of how it should appear.

DemoGraphicSale

9. Records in SQL Server table.

SQL Server Table

New item list SharePoint

10. Now if you want to add a  new item to the list, you can do it by clicking on the new item button.

New Item Button

11. You can easily add a new item to the list after this process and the below image shows how the new list will look like.

DemoGraphic SharePoint

12. You can effortlessly insert all the newly created items into the SQL server table.

New item SharePoint

13. All you need to do is- select an item that the user wants to edit and click on Edit Item option from the ribbon.

Edit Item Option SharePoint

14. This will enable you to edit the item and then click on Save to store new changes.

Save to Store New Changes SharePoint
Demo

15. After clicking on save, the changes made in the list will also be reflected in the SQL Server table.

Reflected in the SQL Server

16. In case of deleting an item, select the item that the user wants to delete. Press Delete key or click Delete Item option from the Items menu in the ribbon, a confirmation message will be displayed. Click OK if the item is required to delete.

Delete Item
New Item


17. The record will also get deleted from the SQL Server table.

Deleted from the SQL Server

5. Conclusion

In this blog, finally using Sharepoint you will be able to process the display of external content. To maintain the data of a huge organization at different data sources and to be able to share using various sites developed in a cross-platform environment. If the organization has one SharePoint site and a site that uses SQL Server in the back-end then use the SharePoint OOTB functionalities i.e. external list. A SharePoint site is able to communicate to any table of the SQL Database and also perform the CRUD operations without using a single line of code.

profile-image
Shital Patel

Shital Patel is VP at sysgenpro with a high-level of proficiency and technical precision in SharePoint Development. His experience of the last two decades has helped businesses to solve complex challenges resulting in growth and performance of Startups to Fortune 500 companies.

Related Service

Know More About Our SharePoint Development Services

Learn More

Want to Hire Skilled Developers?


    Comments

    • Leave a message...