Create External Content Type Using SharePoint Designer 2013 For SQL Server

Introduction

External Content Types are used to define the data that is stored in the external system. External Lists and external data columns are created using BCS external content types which will behave as a bridge between SharePoint and an external data source to expose the external data in SharePoint 2013.


Figure 1: External content type

SQL Server Database

A SQL Server database is used as an external data source to create external content type. A database named "Employees" is created in SQL Server.

Emp_Details table contains the following columns:


Figure 2: Emp_Details table column names and data types

Emp_Details table contains the following rows:


Figure 3: Emp_Details table rows

Create External content type for SQL Server

SharePoint Designer 2013 is the most powerful tool to create External Content types in SharePoint 2013. External content types must be associated with an external data source which contains the data from the external system (An application which manages the data in the external data source) through any of the three connectors.

1. SQL Server
2. WCF Service
3. Net Assembly

In this section you will see how to create an external content type using SharePoint Designer 2013 for SQL Server.

Steps Involved

1. Open SharePoint Designer 2013.
2. Click on Sites and then Open Site.
   
   
   Figure 4: Open SharePoint Site
3. Enter the site URL and click on Open.
   
   
   Figure 5: External Content Types in the Navigation pane and in the ribbon interface
4. In the Navigation, click on External Content Types and then click on External Content Type which is available in the New tab.
   
   
   Figure 6: External Content Type in the ribbon interface
5. Enter the details as shown in Figure 7.
   
   
   Figure 7: External Content Type iNformation in SharePoint Designer 2013
6. Click on the link "Click here to discover external data sources and define operations" as shown in Figure 8.
7. Click on "Add Connection".
   
   
   Figure 8: Add Connection to the SQL Database server
8. In the External Data Source Type Selection wizard, select "SQL Server" and then click on "Ok".
   
   
   Figure 9: External Data Source Type Selection
9. In the SQL Server Connection wizard, enter the details as shown in Figure 11.
   
   
   Figure 10: SQL Database Connection properties
10. Right-click on the Emp_Details table and click on "Create all operations" as shown in Figure 12.
    
    
    Figure 11: Data Source Explorer
11. The "All Operations" wizard will pop up, as shown in Figure 13.
    
    
    Figure 12: All Operations wizard
12. Click on Finish.
13. "Create All Operations" will create methods for the external content type which is used to perform the tasks such as creating, reading, updating and deleting.
14. External Content Type Operations will be created successfully as shown in Figure 14.
    
    
    Figure 13: External Content Type Operations
15. Save (Ctrl+S) the External Content Type.
16. Thus a new external content type is created using SharePoint Designer 2013.
17. In the Central Administration, click on Manage Service Application which is available in Application Management section.
    
    
    Figure 14: Application management Section in Central Administration
18. Click on BCS Service application.
19. You could be able to see the external content type that we have created as shown in Figure 16.
    
    
    Figure 15: Application management Section in Central Administration

Summary

Thus in this article you have seen how to create an external content type using SharePoint Designer 2013 for SQL Server.