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.

external-content-type-in-SharePoint1.png
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:

external-content-type-in-SharePoint2.png
Figure 2: Emp_Details table column names and data types

Emp_Details table contains the following rows:

external-content-type-in-SharePoint3.png
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.

    external-content-type-in-SharePoint4.png
    Figure 4: Open SharePoint Site
  3. Enter the site URL and click on Open.

    external-content-type-in-SharePoint5.png
    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.

    external-content-type-in-SharePoint6.png
    Figure 6: External Content Type in the ribbon interface
  5. Enter the details as shown in Figure 7.

    external-content-type-in-SharePoint7.png
    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".

    external-content-type-in-SharePoint8.png
    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".

    external-content-type-in-SharePoint9.png
    Figure 9: External Data Source Type Selection
  9. In the SQL Server Connection wizard, enter the details as shown in Figure 11.

    external-content-type-in-SharePoint10.png
    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.

    external-content-type-in-SharePoint11.png
    Figure 11: Data Source Explorer
  11. The "All Operations" wizard will pop up, as shown in Figure 13.

    external-content-type-in-SharePoint12.png
    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.

    external-content-type-in-SharePoint13.png
    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.

    external-content-type-in-SharePoint14.png
    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.

    external-content-type-in-SharePoint15.png
    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.