SharePoint 4 Developers

1 year of blog! Time flies! This deserves a post and a celebration, especially because I have persisted with my posts and articles along this time! :)

Well, I can say that was and is a challenge to keep this blog every day, because I always focus on sharing unique information and very detailed about .NET/SharePoint development. This involves a lot of work, because it is necessary time for ideas, research and writing, which is something I really enjoy doing and there is nothing better than seeing your articles being used and becoming reference to the work of others.

Another important factor that has motivated me is the participation in communities in Brazil and here in New Zealand. Along the time you get to know other people who share the same ideas and interests, and as a result your contact networking also increases.

As an outcome of that is my participation in CanalSharePoint as moderator, which I consider extremely important, because the CanalSharePoint is one of the major channels for learning SharePoint in Brazil. Here are my thanks to the leaders Bruno Velaz, Heber Lopes and Wagner Amorim. Thanks guys!

Another community that also have the opportunity to participate and collaborate is the Codificando.NET. I also have to thank Diego Nogare for supporting me with the articles and Fernanda Sallai for supporting me with the publication of articles at the Revista Codificando.NET.

My participation in the SPUG (SharePoint User Group) from Auckland is still not significant, but I intend to contribute more to this community.

The launch of the domain sharepoint4developers.net also deserves celebration, it was something I always wanted and could implement it this year, moving the blog from Windows Live Spaces.

Well, this is just the first year and many things has happened. I wish the following years to be even better!

Cheers,

Marcel Medina

Click here to read the same content in Portuguese.

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:

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:

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:

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:

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:

Figure 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:

Figure 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.

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:

Figure 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:

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:

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:

Figure 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.

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:

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:

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.