SharePoint 4 Developers

Additional reference guide in .NET / SharePoint Development

Business Connectivity Services – Part II

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.

Business Connectivity Services – Part I

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. (Part I)

Hi folks,

In these last months I have been working on the new SharePoint 2010 BCS for the integration of different data sources, and I have decided to talk about the concepts involved and my experiences based on examples of different lessons. This is the beginning of a series of posts related to this subject, and this is the first one. This a complete content focused on development, which BTW is the purpose of this blog. Hope you enjoy it!

Understanding what the BCS is, its architecture and components is the main purpose of this approach.

Business Connectivity Services

Have you ever thought about or had the necessity of integrating different data sources in a unified system? It is important to know that this is possible in SharePoint 2010, thanks to the Business Connectivity Services (BCS).

By default the SharePoint 2010 platform contains Out-of-the-box (OOTB) features of BCS for displaying, manipulating and data searching, and with the utilization of SharePoint Designer 2010 and/or Visual Studio 2010 tools is possible to create advanced BCS solutions for integrating data sources and services.

The previous version of BCS called Business Data Catalog in MOSS 2007 also allowed us to connect to external data sources, however the creation of this kind of solutions was difficult due to the lack of design tools. Despite the fact it was relatively simple to create applications read-only, applications that persisted data were difficult to be done. Now there is an easy workaround for these restrictions due to the new tools SPD2010 and VS2010.

By the re-utilisation of a concept in grouping metadata called Content Types, BCS creates a metadata mapping called External Content Type, due to the creation of an external metadata mapping, which I will talk about later.

Note: Many companies have already understood that SharePoint can be used like an Integration Portal, where they can extend their internal systems (ERP, CRM, etc.) with custom and OOTB solutions. That is happening with the utilisation of Business Data Catalog in MOSS 2007, now with the BCS in SharePoint 2010.

Architecture

Let’s check the blocks and components that belong to the diagram below, they bring us an understanding of how the BCS works, according the Figure 1:

BCS Architecture Figure 1 – BCS Architecture

As per the Figure 1, each block was enumerated to explain its components:

Building Block 1 – External Data Sources

This block displays the provided support to different types of external data sources, through the following connections:

Databases

By default direct connections via database are done using SQL Server.

Web / WCF Services

This support is provided to both Web Services (*.asmx) and WCF Services (*.svc).

Assemblies .NET

The connection to different databases (Oracle, MySQL, etc.) is only possible by creating custom assemblies .NET for this purpose.

Custom Data Sources

The utilisation of Custom Connectors allows the connection to different types of Data Sources. i.e.: XML and Text data.

Building Block 2 – SharePoint Site

This block is responsible for connecting to external data sources, it allows the operations Create, Read, Update, Delete, and Query (CRUDQ) to be performed. Check out its components:

BDC Metadata Store

The BDC Metadata Store is a component that is responsible for the storage of External Content Types, which contains the metadata that describes how to connect to the external data sources.

Secure Store Service (SSS)

This is a service application of SharePoint 2010 for the storage of credentials. It replaces the Single Sign On feature of MOSS 2007. The credentials of external data sources can be stored and associated to a specific identity (or a group of identities).

External Content Type (ECT)

The ECT describes the schema and data access capabilities of an external data source and its behaviour within Office and SharePoint.

BDC Server Runtime

This component knows exactly how to connect to an external data source utilising ECTs for this purpose.

Search, Workflow, Web Parts

The BCS provides and supports some features OOTB that are available in SharePoint 2010. They are listed: searching, workflows and Web Parts.

External List

This is a List created based on an ECT, which is displayed like any other List in SharePoint.

Connectors

They are responsible for interfacing the BDC Runtime with the external data sources.

Building Block 3 – Office Client

Both applications (Add-ins / Custom code) created for Office 2010 and the Microsoft products like Outlook 2010 and Microsoft SharePoint Workspace 2010 can handle external data efficiently. The following components are contained in this building block:

Cache

When the application is online, the SQL Compact stores data temporarily, which ensures that even if the application is offline, the content is displayed.

Offline Operations

The reading and writing operations performed in cache are persisted when the application is back online.

BDC Client Runtime

This component is responsible for synchronizing external data, which are carried out consistently as follows:

  • When online, it connects the client directly to the external data source. Through the Sync a copy of ETCs are sent to the client, which allows direct access.
  • When offline, data remains cached for later synchronization.

Office Add-in / Custom Code

Deployment of VSTO packages via ClickOnce allows applications to be quickly available in user machines, by the utilisation of smart-clients.

Building Block 4 – Tools

The new versions of the tools below allow the creation of several solutions due to the new set of features they offer.

SharePoint Designer 2010

It enables the creation of ETCs and External Lists without the need of creating custom code, allowing the rapid creation of solutions using its resources OOTB.

Visual Studio 2010

In cases where solutions created by the SPD 2010 do not meet the business needs, VS 2010 should be used for advanced developments via code.

External Content Types

The BCS uses External Content Types as the main object for the mapping of external data. Have you already heard about Content Types in SharePoint? By definition Content Types are collections of metadata, which groups Site Columns in SharePoint. Content Types internally store the schema of its metadata in XML, used for identifying its data types. i.e.: string, boolean, number, etc.

The ECT is an extension of this concept, the Figure 2 shows the use of ECTs:

ect_en680 Figure 2 – Utilisation of an ECT

As per Figure 2, besides storing its metadata schema, an ECT provides data access capabilities of an external data source and its behaviour within Office and SharePoint, which is available through entities and methods. This object is stored in the BDC Metadata Store that contains all ECTs (as described in the Architecture).

This was a theoretical introduction. I consider this understanding essential for the explanation of the demonstrations of the next posts. BTW, in the next post I am going to show how to create ETCs. Stay tuned!

References:
http://msdn.microsoft.com/en-us/library/ee556826(v=office.14).aspx
http://msdn.microsoft.com/en-us/library/ee557898(v=office.14).aspx
http://msdn.microsoft.com/en-us/library/ee557790(v=office.14).aspx
http://msdn.microsoft.com/en-us/library/ee554916(v=office.14).aspx

Cheers,

Marcel Medina

Click here to read the same content in Portuguese.

Course 10175A, Microsoft® SharePoint® 2010, Application Development

Recently a new course part of the upcoming Microsoft Learning SharePoint 2010 portfolio was published, target at SharePoint developers. The course is the 10175A, Microsoft® SharePoint® 2010, Application Development.

Hi folks,

Great news for SharePoint developers!

Recently a new course part of the upcoming Microsoft Learning SharePoint 2010 portfolio was published, target at SharePoint developers. The course is the 10175A, Microsoft® SharePoint® 2010, Application Development.

In this post Tjeerd Veninga explains how this course was created: http://borntolearn.mslearn.net/btl/b/weblog/archive/2010/07/30/not-all-courses-are-created-the-same-way.aspx

Find a CPLS (Certified Partner for Learning Solutions) and get a quote for this new course, which BTW I am already able to ministrate it.

This course provides existing .NET developers with practical information and labs that enables them to build solutions on the Microsoft® SharePoint® 2010 platform.

Have a quick look at its content:

  • Module 1: Introduction to the SharePoint 2010 Development Platform
  • Module 2: Using SharePoint 2010 Developer Tools
  • Module 3: Developing SharePoint 2010 Web Parts
  • Module 4: Working with SharePoint Objects on the Server
  • Module 5: Creating Event Receivers and Application Settings
  • Module 6: Developing Solutions by Using Business Connectivity Services
  • Module 7: Developing SharePoint 2010 Workflows
  • Module 8: Working with Client-Based APIs for SharePoint 2010
  • Module 9: Developing Interactive User Interfaces
  • Module 10: Developing Silverlight Applications for SharePoint
  • Module 11: Developing Sandboxed Solutions
  • Module 12: Working with SharePoint Server Profiles and Taxonomy APIs
  • Module 13: Developing Content Management Solutions

Comparing with other SharePoint MOCs, this material is very good and very well detailed. Awesome!

Cheers,

Marcel Medina

Click here to read the same content in Portuguese.