SharePoint 4 Developers

Additional reference guide in .NET / SharePoint Development

Business Connectivity Services – Part II

20. August 2010 Admin SharePoint Lessons (0)
Learn about the Business Connectivity Services and how to develop solutions using the SharePoint Designer 2010 and Visual Studio 2010 by creating External Content Types via Database. (Part II)

Hi folks,

This post is the continuation of the Business Connectivity Services Series, focused on development. In this approach I demonstrate how to create ECTs via Database.

External Data Access

For all the examples, consider using the following table Contact. It needs to be created, as this will be used as our base table for exemplification:

Code Snippet
  1. CREATE TABLE [dbo].[Contact](
  2.     [ContactID] [int] IDENTITY(1,1) NOT NULL,
  3.     [CompanyName] [nvarchar](100) NULL,
  4.     [ContactName] [nvarchar](70) NOT NULL,
  5.     [ContactTitle] [nvarchar](10) NULL,
  6.     [Address] [nvarchar](100) NULL,
  7.     [Email] [nvarchar](100) NOT NULL,
  8.     [City] [nvarchar](15) NULL,
  9.     [Region] [nvarchar](15) NULL,
  10.     [PostalCode] [nvarchar](10) NULL,
  11.     [Country] [nvarchar](15) NULL,
  12.     [Phone] [nvarchar](24) NOT NULL,
  13.     [Fax] [nvarchar](24) NULL,
  14.     
  15.     CONSTRAINT [PK_Contact] PRIMARY KEY (
  16.     [ContactID] ASC
  17.     )
  18. )

Create it against the SQL Server, because the ECTs are going to be created based on this table. The task of creating a table I am not going to approach, once this is not the purpose of this article (Please check the references for links to perform that).

Creating ECTs via Database

In order to create ECTs via database only SPD2010 is required and nothing more.

Use this type of approach in environments:

  • That are in the same network, where the database access can be done directly through SPD2010;
  • There is no need of creating business rules, just because CRUD operations are sufficient;
  • That uses SQL Server database (that´s right, only SQL Server);

If you have this scenario, this demonstration is the easiest and simplest way to implement.

Working with SharePoint Designer 2010

Start the SPD2010 (through SharePoint 2010 or open this tool directly) and access External Content Types in the Navigation pane. Create a new ECT and set the properties according the Figure 1:

Figura 1 Figure 1 – Creating an ECT

Note: The properties Office Item Type and Offline Sync for External List determine the mapping type of objects in Office 2010 and if the sync option will be available for External Lists respectively. Pay attention to the selection areas in red colour that display the steps to perform the configuration, which can be enumerated to indicate the correct steps.

When clicking over the External System for mapping the ECT, the Figure 2 is displayed:

Figura 2 Figure 2 – Adding a new connection

Add a new connection (1) that maps the SQL Server database by selecting it in the External Data Source option (2). Set the connection parameters according the Figure 3:

Figura 3 Figure 3 – Connection details

Nesse When connecting with User´s Identity, the SQL Server uses the login of the current user logged in SharePoint to display data. Proceed with the operation and find the table Contact. In order to create a CRUD, select the option from the context menu (right button) Create All Operations according the Figure 4:

Figura 4Figure 4 - Creating all the operations of a CRUD

Note: In this example we have a CRUD, which contains all the operations, but if you opt for the creation of some operations only, the operations Read Item and Read List needs to be created (at least). Otherwise you cannot create an External List, which represents a SharePoint list that displays external data.

A modal screen is displayed at the moment of picking out an operation. On this screen set the parameters of the CRUD, according the Figure 5:

Figura 5Figure 5 - Parameters configuration screen

Basically we need to configure the column properties of the table Contact, setting an identifier, display names, required fields and the mapping for the Office properties (Outlook). All the definitions are available in the Tables 1 and 2.

Data Source Element

.NET Type

Map to Identifier

Identifier

Field

Display Name

Foreign Identifier

ContactID

System.Int32

TRUE

ContactID

ContactID

ID

  

CompanyName

System.String

FALSE

  

CompanyName

Company Name

  

ContactName

System.String

FALSE

  

ContactName

Contact Name

  

ContactTitle

System.String

FALSE

  

ContactTitle

Contact Title

  

Address

System.String

FALSE

  

Address

Address

  

Email

System.String

FALSE

  

Email

E-mail

  

City

System.String

FALSE

  

City

City

  

Region

System.String

FALSE

  

Region

Region

  

PostalCode

System.String

FALSE

  

PostalCode

Postal Code

  

Country

System.String

FALSE

  

Country

Country

  

Phone

System.String

FALSE

  

Phone

Phone

  

Fax

System.String

FALSE

  

Fax

Fax

  
Table 1 – CRUD Parameters

Data Source Element

Required

Read-Only

Office Property

Show In Picker

Timestamp Field

ContactID

FALSE

TRUE

Custom Property

FALSE

FALSE

CompanyName

FALSE

FALSE

Company Name (CompanyName)

FALSE

FALSE

ContactName

TRUE

FALSE

Full Name (FullName)

FALSE

FALSE

ContactTitle

FALSE

FALSE

Title (Title)

FALSE

FALSE

Address

FALSE

FALSE

Business Address (BusinessAddress)

FALSE

FALSE

Email

TRUE

FALSE

Email 1 Address (Email1Address)

FALSE

FALSE

City

FALSE

FALSE

Business Address City (BusinessAddressCity)

FALSE

FALSE

Region

FALSE

FALSE

Business Address State (BusinessAddressState)

FALSE

FALSE

PostalCode

FALSE

FALSE

Business Address Postal Code (BusinessAddressPostalCode)

FALSE

FALSE

Country

FALSE

FALSE

Business Address Country/Region (BusinessAddressCountry)

FALSE

FALSE

Phone

TRUE

FALSE

Business Telephone Number (BusinessTelephoneNumber)

FALSE

FALSE

Fax

FALSE

FALSE

Business Fax Number (BusinessFaxNumber)

FALSE

FALSE
Table 2 – CRUD Parameters (Continuation)

Note: Fields in gray colour have two meanings in Tables 1 and 2:

  • Fields cannot be edited; or
  • Ignore the field configuration because it does not apply to our scenario;

The field Data Source Element is the reference used in both tables.

Once all the column properties were set properly, save the ECT (1) and note the operations created (2), which can be edited at any time, according the Figure 6:

Figura 6Figure 6 – Saving the ECT

At this moment it is already possible to create an External List that will display the external data in SharePoint 2010. In this same screen of External Content Types, select the option External List on the context menu. Name it to Contacts, according the Figure 7:

Figura 7 Figure 7 – Creating an External List

From this point on we only need to test the External List to see its functioning.

Testing an External List

This is the final step of creating ECTs, in which the CRUD operations are going to be tested against the External Lists. In order to do that, access the SharePoint Site where the External List Contacts was created.
Open it and add a new item. Note that the configuration of parameters were applied into the columns, according the Figure 8:

Figura 8 Figure 8 - Adding a new item on the Contacts List

Immediately after adding the new item, check whether it is available at your table in the database. There it is, right? This is the expected behaviour after adding a new item, furthermore, the other CRUD operations must be active in the List Contacts according the Figure 9:

Figura 9Figure 9 - Checking the CRUD operations

Perform the remaining operations and check against the database the expected behaviour.

In this post we explored the SharePoint Designer 2010 to create ECTs, however in the next posts of this series the Visual Studio 2010 will be utilised as additional tool. In the next post check how to create ECTs via Web Service. See you then!

References:
http://msdn.microsoft.com/en-us/library/ee556826(v=office.14).aspx
http://msdn.microsoft.com/en-us/library/ms186378(v=SQL.105).aspx

Cheers,

Marcel Medina

Click here to read the same content in Portuguese.

blog comments powered by Disqus