Implement CRUD Operations on SQL Table 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.

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:

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

2. Click on the Business Data Connectivity Service Application.

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.

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.

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.

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.

6. Click Next.

7. Click Next.

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

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.

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

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.

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.

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.

9. Records in 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.

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.

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

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.

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

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

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.

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

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.