Dec 18, 2014

When we work in a SharePoint environment, we most often use data that is stored within SharePoint, but this doesn’t stop us from working with data that is stored externally to SharePoint. To start working with external data, you have to implement an external connection to the data source of your choice. Once that is in place, we can create External Content Types (ECTs). The external content types that you create will need a name, fields and operations. These operations will contain input/output parameters and can optionally contain filters. To create an external content type, follow these steps.

4. Open SharePoint Designer and click on the ‘External Content Type’ option in the navigation.
5. Hit the ‘External Content Type’ button in the ‘New’ section available on the ribbon.
6. Next, you have to specify the information for your content type in the summary pane to meet your requirements and then use the link marked by the number (2) to create a new connection to the external data source.
7. When creating a connection to an external data source, you can choose from SQL Server, a .NET type or a WCF service (I will be using a SQL Server connection). I will connect to a server called SDG-WFE1 and use a database called AdventureWorks2012. The authentication I use will be Windows authentication and a secure store ID of AdventureWorksds_bdc (this was provided by the administrator). Now we need to select the source from where we will be retrieving our information. I will be using the View called “Vemployee” and I will create all operations on it.
8. When it comes to the operations of the ECT (external content type), you can specify CRUD operations i.e. Create, Read, Update and Delete. The Read consists of two sub operations namely Read Item (specifying how to read an individual record) and Read List (specifying how to read a list of records).
9. When defining these operations, you will have to specify an identifier. This is used to uniquely identify each record when an operation needs to be applied to it. In SQL, this will commonly be a Primary Key. By using the same window, you can change how each columns acts, for example, you can change the display name to be more user friendly.
10. We can also specify filters on the Read List operation to filter out results. These include Limit (used to restrict the amount of records returned) and Pagination (similar to the top clause in SQL with pagination options).

You can also specify input and/or output parameters, and this is where you define what data is sent to the operations (input). For example, if you want to return an employee with the first name of Auret and data being retrieved from the operations (output) such as a list of employees. Finally, we can also include Associations in an ECT. These are compared to relationships in a database. This will normally be one-to-many relationships and many-to-many relationships.

Once you have the ECT in place, you can create an external list inside your site to show the data from the source specified in the ECT (see the screenshot below for the steps).