This is a quick step guide to building a External Content Type with SharePoint Designer 2013. The External Content Type describes the data that you are connecting to using strongly typed properties as well as providing the methods for Reading, Inserting, Deleting and Updating the external data via SharePoint.
To create a connection to a Microsoft SQL server database, follow these steps:
1. Start Microsoft SharePoint Designer 2013.
2. Open the site that you wish to connect to by choosing File, Sites, Open Site.
3. Enter the URL of the site within the Site name field of the Open Site dialog box and press Open.
4. Click External Content Types on the Navigation Window.
5. Click External Content Type from the New category.
Creating a new External Content Type within SharePoint Designer 2013
6. Provide a name for the External Content Type by clicking on the ‘New External Content Type’ link.
7. Provide a namespace name. The default namespace name is the URL of the site that the ECT was created in. A good example of a namespace would be the database or solution name such as ‘Adventure Works’ or ‘CRM System’. This is more meaningful to the end user.
8. The version will increment automatically each time a change is made to the ECT.
9. Set the office type. The types include Contact, Post, Task, Appointment, or Generic List. The type controls how the data is displayed within Microsoft Outlook. For example, setting the Office Type to Contact will display the data as business cards within the contact folder within Microsoft Outlook.
10. Enable or Disable the Offline synchronization. Setting the offline synchronization to Disabled will stop users from synchronizing data within the office client in applications such as Microsoft Shared Workspace.
11. Click External System to create a connection to an external data source.
Configuring the properties of an external content type within SharePoint Designer 2013.
You will be able to see any previous connections that you have made, and have the ability to add a new connection. The connections you can create include SQL Server, WCF Web Service, and .NET Assembly. The .NET Assembly is a compiled DLL which contains the BCS Methods providing the CRUD functionality to your external data source. The .NET Assembly therefore needs to be supplied by a SharePoint Developer. Note that the .NET Assembly can be deployed as part of a solution and does not need to be used through SharePoint Designer. The WCF Web Service would need to be created specifically for BCS and cannot just be any WCF web service that returns data. This can be created within Visual Studio and then called via SharePoint Designer. Finally, you can connect directly to Microsoft SQL. This provides a non-code method to create a connection to Microsoft SQL.
12. Click Add Connection.
13. Choose SQL Server from the Data Source Type Dialog Box.
14. Enter the Database Server name. (localhost or ‘.’ can be used for the local server).
15. Type the Database name that you wish to connect to.
16. Choose the type of authentication that you would like to use.
There are three different types of authentication that you can use to authenticate with the external data source.
· Connect with users identity
· Connect with Impersonated Windows Identity
· Connect with Impersonated Custom Identity
Creating the connection to Microsoft SQL using SharePoint Designer 2013
Connect with Users Identity
‘Connect with users identity’ is windows authentication meaning that the users credentials provided at the browsers are passed to SharePoint for authentication and then also used to authenticate the logged on user against the external data source. This works well in some cases but will fail if NTLM (NT LAN Manager) is used as the authentication protocol, and the external data source resides on another physical server. Kerberos can be used within this scenario without an issue.
Connect with Impersonated Windows Identity
‘Connect with Impersonated Windows Identity’ is used when Secure Store Services (SSS) is selected to authenticate users against the external data source and Windows accounts are being used. SSS contains Windows users or groups that are mapped to other Windows accounts which in turn are the accounts passed to the external data source for authentication. SSS enables you to be aware of the credentials that will be used to access the database and will also overcome the double hop issue.
Connect with Impersonated Custom Identity
‘Connect with Impersonated Custom Identity’ is used when Secure Store Services (SSS) is selected to authenticate users against the external data source and custom accounts are used such as Forms Based Authentication (FBA).
Note: If you choose to use the Secure Store Service, you will need to ensure that the Secure Store is configured correctly and that you have the required application id. The SSS accounts will also need to be granted permissions to the external data source.
17. Expand the database that you wish to connect to.
18. Expand the Tables node from the treeview.
19. Right click the Table that you wish to connect to so that you can create the operations required for the ECT. The operations are explained below.
Read Item
The Read Item operation is created so that a specific row of data can be identified and used. Each table, view or stored procedure will require a column whereby each of its values is unique such as a Primary Key. This column called an identifier uniquely identifies the row so that a specific row can be selected and used within SharePoint Web Parts, Search, and the Business Data Column. This method contains a ‘Where’ clause that accepts a parameter passing the unique identifier value.
Read List Operation
The Read List operation is like a Select statement without a Where clause. It returns by default all columns and all rows from the selected table. Filters can be created allowing the results to be trimmed. The Read List operation is used whenever multiple rows are displayed such as within an External List or a Business Data List Web Part.
Create Operation
The Create Operation is used by the External List when data is being created. The Create Operation contains the statement allowing data to be inserted into the table.
Update Operation
The Update operation provides the ability to make and submit changes back to the external data source via the External List and some Office applications such as Microsoft Excel.
Delete Operation
The Delete Operation provides the ability to select a specific row and delete it via the external list.
Creating operations within SharePoint Designer 2013.
Often you may only be creating a Read and Read Item operation as you do not want the external data to be changed via SharePoint. However, instead of creating each of the methods independently, if you do require that all operations are created including the Update, Delete and Create, you can select the ‘Create All Operations’ method.
20. Click ‘Create All Operations’ to create each of the methods including Create, Update and Delete.
The Create Operations wizard within SharePoint Designer 2013.
21. Click Next within the All operations dialog box to configure the properties.
Within the Operations wizard, after clicking Next, you will be able to configure the properties for each of the columns from the Table that you are connecting to.
The default column selection is to select all of the columns from the Table. Using the check boxes you can uncheck the columns that you do not wish to use within SharePoint. The remaining checked columns can be selected and then have properties configured against them. The Data Source Element and .NET Type are read only and have been determined by SharePoint Designer. The .NET type describes the type of data that is stored within that column.
The Identifier property enables you to set which column will be used as the unique identifier. SharePoint Designer will set a unique column such as a primary key as the identifier. You can however change it should you have perhaps more than one column that contains unique values.
Using the Display Name column, you can set a friendly name for a column. For example, ‘Birthdate’ could have a display value of ‘Date of Birth’.
Foreign Identifiers are used when you wish to use an association between two external content types. An association is similar to a join or a relationship so that you can show related data such as Customers -> Orders. Using the Foreign Identifier column, you will specify the name of the ECT and Foreign Key column.
The Required property allows you to control the validation for the column. This is detected by default but can be overridden. Any column marked as ‘Required’ will be presented with an asterisk indicating that it is a required column within the External List.
‘Read Only’ is similar to the ‘Required’ property in the way that it provides validation within the external list when data is being created or modified. An error will be returned to the browser if the user attempts to leave the column blank.
The Office Property works in conjunction with the Office Type which controls the behavior of the external data within Microsoft Outlook. If you take the Contact Office Type as an example, your data from the ECT will be displayed as business cards within Microsoft Outlook. You will therefore be able to map columns together such as ‘Address’ and ‘Business Address’.
The Show In Picker property allows you to choose whether the column is displayed or not within the Item Picker dialog box which is displayed when an item is selected from within Office applications or when setting a value for a business data column. Although the property shows unchecked by default, unless you check ‘Show in Picker’ for at least one column, all of the columns will be shown within the picker.
The Timestamp Field property can be set for a Date/Time column such as ModifiedDate within the figure 21-21. The Timestamp field property is used by the Search Index so that only modified data needs to be re-indexed. Data that has not changed since the last index can be left alone. If your table contains a ModifiedDate column, you can configure it as the Timestamp column that the search index will query before re-indexing that row of data.
Configuring properties within the operations wizard.
22. Set any desired properties and then click Next to create filters for your External Content Type.
Filters can help make business connectivity services perform better since you are sending less data over the network between the external data source and SharePoint as well as SharePoint and the Office client. There are different types of filters that you can create:
· Comparison
· Wildcard
· Limit
· Timestamp
· Page Number
Comparison
A comparison filter can be used to set a default value for the filter. For example, you may want to show all customers where the City column is equal to ‘New York’.
Wildcard
A wildcard filter can be used when you would like to use a begins with filter. This is useful for the External Data Columns item picker. The picker can only display 200 items. Using a wildcard filter, you will be able to reduce the number of rows by setting a filter such as CompanyName begins with A.
Limit
The Limit filter can be configured to only return a particular number of rows. The External list in SharePoint 2010 had a limit of 2000 rows. The limit is much higher in SharePoint 2013, but it still makes good sense to ensure that a limit filter is in place to avoid error messages when the data grows beyond the limits.
Timestamp
The timestamp filter enables you to filter data based upon the last modified date. For example you could bring back all employees who have had their profile changes within the last three months.
Page Number
There are two places where you can configure paging; in the external list properties or within the external content type. Configuring paging in the external list is client side and therefore doesn’t help reduce the amount of data being returned to SharePoint. Setting the Page number filter reduced the data sent to SharePoint in one go, allowing pagination to control the display of 100 items at a time or a value that suits your requirements.
Creating a comparison filter within the all operations wizard.
23. Create any required filters, and then click Finish.
24. To create the External Content Type, Click the save icon within SharePoint Designer 2013.
You should now be able to create an External List within SharePoint 2013 which will be covered in a future blog post soon.
<Brett/>
