Article – Business Connectivity Services (BCS) Part III – Creating an External Content Type (ECT)

Here you get the long awaiting Part III of my BCS article series. Having discussed what BCS is, terminology and the solution types, it's time to create a simple solution. Let's create a simple solution which brings/ surfaces data coming from a SQL Server database within in a SharePoint site.

Scenario

Northwind has implemented an intranet portal using SharePoint Server 2010 and it is becoming very popular among the business users/ information workers. One of the business users has learnt about the powerful capabilities of SharePoint 2010 release in the area of integrating with legacy systems. He sends an official email to the IT department of Northwind inquiring about the possibility of integrating their LOB system with SharePoint so that they can manage their Contacts, Prospects, Leads, etc… efficiently using their intranet postal interface.

Solution

IT Manager has realized the importance of the request and now he needs to deliver the solution using BCS which is part of SharePoint 2010. IT Manager decided to develop a simple solution using SharePoint Designer 2010 to make it simple and fast for both business users and the developers.

I navigated to SQL Server Management Studio (Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio) and expanded the Databases node. I further expanded the Northwind database and its Tables folder to see all the tables available.

As the 1^st step, I decided to provide access to Employees data available in Northwind database using SharePoint 2010 site. I executed the following SQL statement to see all the data available in the Employee table:

Employees data in SQL Server

Let's develop our 1^st BCS solution to integrate Employees data into a SharePoint 2010 site.

Exercise 1 – Creating an External Content Type (ECT)

1. Fire-up SharePoint Designer 2010 by navigating to Start -> All Programs -> SharePoint -> Microsoft SharePoint Designer 2010.
2. Click Open Site to bring up Open Site dialog box.
   

   Opening your SharePoint site in SPD 2010

3. Enter the URL of the SharePoint site for Site name: and click Open.
4. Select the External Content Types from the Site Objects pane and wait until it retrieves all the existing External Content Types.
   

   External Content Types in SharePoint

5. Select the right-side panel to activate the Ribbon menu options and click the External Content Type from the New group in the ribbon.
   

   Creating a new ECT

6. You will get the New external content type tab/ page and that's where you will configure your External Content Type to communicate with the Northwind database.
7. Let's first fill the details for the External Content Type Information section. Place cursor for the Name and provide a meaningful name for the External Content Type.
   

   External Content Type Information

8. Press Tab to navigate to next field and you will see Display Name is populated automatically based on the Name field. Place the cursor and change the Display Name if you wish. I gave a space in-between to read as "Northwind Employees" which is easy to read.
   

   External Content Type Information

   

   External Content Type Information

9. Select an appropriate option for the Office Item Type. If you are planning to surface data within Office applications such as Outlook you need to specify the Office Item Type. If you have no plan to surface data inside Office applications you can keep the default value which is Generic List for the Office Item Type. You can use Appointment as the Office Item Type if the underlying data coming from the LOB system need be shown in the Calendar pane in Outlook. If you are brining data which can be shown in the Contacts pane in the Outlook such as Employees, Customers, Students, Dealers, etc… then you can use Contact as the Office Item Type.
   

   Select Office Item Type

10. For this demo select the Contact as the Office Item Type.
11. Next select whether you want to have the Offline Sync for external list Enabled or Disabled. For this demo, keep the default value which is Enabled.
12. After finishing the defining External Content Type Information, your screen will look something similar to the following.
    

    Creating a new ECT

Exercise 2 – Defining External Content Type Operations

1. Click the link Click here to discover external data sources and define operations link in the External Content Types Operations section to define the external system information and the operations.
   

   Defining External System Information

2. You will be taken to the Operations Designer for the Northwind Employees External Content Type. Click the Add Connection button to bring the External Data Source Type Selection dialog box.
   

   ECT Operations Designer

   

   Data Source Type Selection

3. Select SQL Server as the Data Source Type and click OK to bring up the SQL Server Connection dialog box.
4. Fill up the SQL Server Connection dialog box as shown below by providing Connection Properties and click OK:
   1. Database Server: - provide the name of the SQL Server. In my case, I'm providing (local) since the database is hosted in my local machine.
   2. Database Name: - provide the name of the database. In this demo we are connecting to Northwind database.
   3. Authentication – select what authentication mode to be used. Select Connect with User's Identity for this demo and other options for this are:
      1. Connect with User's Identity – this will take the currently logged-on user's identity for the authenticating to the external system.
      2. Connect with Impersonated Windows Identity – this allows us to have a common windows account setup for authenticating to the external system and all the requests goes through this account just like using a Service Account.
      3. Connect with Impersonated Custom Identity – this helps us to setup Secure Store Services (SSS) and configure Single-Sign-On to authenticate to the external system. With SSS, we can create and configure different Secure Store Target Applications and map the login credentials between SharePoint and the external system. If we are going to this option, make sure to create and configure the Secure Store Target Application beforehand and provide the Target Application ID for the Secure Store Application ID: field.


Defining SQL Server Connection

5. Data Source Explorer will show up Northwind data source and expand it to see all 03 possible way of communicating with the external system:  
   1. Tables – allows us to directly connect with a table object in SQL Server.
   2. View – allows us to connect with a view instead of a table.
   3. Routines – allows us to connect with a Stored Procedure in SQL Server instead of a table.
      

      Data Source Explorer

6. Expand the Tables node and then you will see all the tables from the Northwind database. Expand Employees table and then Columns node to see how SharePoint Designer 2010 has retrieved schema information from the underlying database system.
   

   Data Source Explorer Expanded

7. Right-click the Employees table and select Create All Operations to fire up the All Operations wizard. This wizard will guide us on configuring what information we need to bring to SharePoint and how they get mapped to Office properties if we have decided to do so, etc… If you don't wish to provide all the CRUD-Create, Read, Update, Delete operations to the external system through SharePoint, you need to select the appropriate options from the below screen. If you want to provide only the Read operations, generate only Read Item and Read List operations.
   

   Generating CRUD Operations

8. Click Next in the welcome page of the All Operations wizard.
9. Parameters Configuration page will show up next and as the 1^st step we need to select what are the columns we need to bring into SharePoint. Best practice is to avoid all the optional data fields and select only the mandatory fields to minimize the performance issues. As you can see below, I have selected the fields which are interest to my demo and make sure you double-check with your requirement.
   

   Selecting Fields for ECT

10. Next we need to setup properties for each column and select the column from the left-side Data Source Elements pane and set the properties from the right-side Properties pane. Select the LastName from the Data Source Elements and change the Display Name:, Office Property: & Show In Picker: properties.
    

    Setting Properties for Fields

11. Repeat the same for the rest of the fields except selecting the option Show In Picker:. Leave Show In Picker: as unchecked.
12. Click Next to bring up the Filter Parameters Configuration page. Click Add Filter Parameter to add a filter.
    

    Filter Parameters Configuration

13. As a best practice, we will add a Limit filter to limit the no of records travels from the external system to SharePoint. Select the newly added filter from the left-hand side Filter Parameters pane and click (Click to Add) from the Properties pane to configure the Filter:. This will brings up the Filter Configuration dialog box. Provide a name for the New Filter field. Select Limit for the Filter Type: and click OK.
    

    Setting up Filter Parameter

    

    Setting up Filter Parameter

14. Once you are back in the Filter Parameter Configuration screen, give a value to Default Value: to provide a default value to the Limit filter and press Tab. I gave 100 and you can provide a reasonable value.
    

    Setting up Filter Parameter

15. Next, let's add a Wildcard filter parameter to provide users with wildcard searching capability against Last Name value. Click Add Filter Parameter button again and select the LastName for the Data Source Element: from the Properties pane. Click (Click to Add) link to configure the Filter:. Configure the Filter: as shown in the following screen and click OK.
    

    Setting up Wildcard Parameter

16. Click Finish to finish the Filter Parameter Configuration wizard.
17. When you are back in Operation Designer, you can see External Content Type Operations section has 05 operations generated for us to use.
    

    External Content Type Operations

18. Click Save to save the External Content Type to the Site Collection.
    

    Save the ECT

19. Verify that the new External Content Type is created by navigating back to External Content Types form the Site Objects pane.
    

    After Creating the ECT

Now that we have created and configured our External Content Type to communicate with Employee data in Northwind database in SQL Server and we are ready to integrate it with SharePoint 2010 site.

In my next article, I will discuss the process of creating an External List and how to surface Employee data within SharePoint site coming via NorthwindEmployees External Content Type.