Microsoft Business Connectivity Services Team Blog

BCS Team Channel: External Data in SharePoint Workspace

The third installment of the BCS Team Channel is now live! In this episode you are taken on a tour of how you can surface external data in SharePoint Workspace 2010.

The next video will show you how to associate two external content types and how to work with that data in Web parts.

- Lionel Robinson, Program Manager

External Lists in SharePoint Workspace

This is a post from the SharePoint Workspace team on taking external data into SharePoint Workspace 2010:

You've seen how SharePoint Lists are tightly integrated into SharePoint Workspace, and thanks to the Business Connectivity Services (BCS) team, SharePoint Workspace will also present you with all of your external list data, using the same familiar interface.

In this post, you will see what external lists look like in SharePoint Workspace, and you’ll see a demo of reading and adding data from an external list connected to a SQL backend.  You will see that external lists provide the same functionality as regular SharePoint lists within SharePoint Workspace, and provide the user with the same seamless, easy-to-use experience.

First, we need to sync the list.  You can do this either by synching the entire site, as discussed previously, or you can opt to sync only the external list, as I will do below.  First, go to the list you want, then under List Tools -> List, select ‘Sync to SharePoint Workspace'.  Syncing the list creates a local copy of the external list as well as all the customized views from SharePoint.

Below I have an external list open in SharePoint and SharePoint Workspace.  You can see a very similar look and feel for each program with consistent views, just as you would expect.

Additionally, using SharePoint Workspace, you also get to see a sample of the data in an InfoPath form on the lower part of the window, shown in the picture below. InfoPath integration gives you the same customized data entry experience as SharePoint, and all the same functionality.

There are many options for sorting lists in SharePoint Workspace.  The screenshot below shows some of the options available to you.  You can sort based on any criteria, you can apply a filter, and you can use any views available on the SharePoint site.

Let's see what happens when I try to add data to my external list from SharePoint Workspace.  The company wants to add a new Medical Tricorder to their product catalog, but it won't be released for a few years.  You can see that InfoPath checks the form during the data entry process and warns the user not to offer this particular product until its first offer date.  Clicking ‘Save & Continue' adds the product to the catalog.

I can use SharePoint Workspace to search for the item I just added to the database.  You can see that it doesn't have a Product Key yet.  That's because the product has not been synchronized with the server yet.  Once the list item is synchronized, the product will automatically be assigned a Product Key, and that will be reflected in SharePoint Workspace.

Now we have synchronized the list.  Let's look for the product in the SQL database directly.  You can see that it shows up right there as expected with an automatically assigned Product Key.

With BCS, SharePoint and SharePoint Workspace integrate with your external data sources, giving you a rich, familiar experience no matter where your data comes from.  Check out the BCS blog for more information about how Business Connectivity Services, SharePoint, and SharePoint Workspace can work together to make your enterprise data management tasks easier.

-Andrew Harris, Program Manager

BCS Tooling Investments in 2010: Part 2

In the first installment of this series of posts, “BCS Tooling Investments in 2010: Part 1”, we talked about in to the design experience in SharePoint Designer 2010. Now let’s take a look at what you can do in Visual Studio 2010 followed by the interoperability between SharePoint Designer 2010 and Visual Studio 2010.

Visual Studio 2010

In Visual Studio you can either create reusable BCS code components (that can be used by others to compose new solutions) or you can create complete client-server solutions that leverage BCS. In the case of reusable components, External Content Types are probably the most important, but you can also create custom class library projects that implement specific interfaces defined by BCS to provide external data access logic and user interface.

Complete solutions can involve any combination of server and client side components that are typically packed together as a WSP and/or ClickOnce solution package. These types of solutions will normally use the full power of Visual Studio to create a wide range of SharePoint and Office components ranging from sites, lists, and Web parts to custom Office add-ins. Any of these can be extended through code to access external data through BCS.

External Content Type Authoring

From a BCS perspective, the most prominent investment in Visual Studio 2010 is the new Business Data Connectivity Model project template that comes with a set of visual designers and code management that will let you author External Content Types that can access virtually any data source through your own custom connectivity logic in .Net code. A notable aspect of the tool is that it does a great job on encapsulating the SharePoint specifics so you don’t need to be a SharePoint expert to create very powerful External Content Types that can aggregate data from multiple sources, apply transformations and business logic, deal with custom security schemes, etc.

Figure 1. Screenshot of Visual Studio 2010 (Beta) showing a Business Data Connectivity Model project with four External Content Types and their associations.

Business Data Connectivity Model projects in Visual Studio 2010 always produce External Content Types of type .Net Assembly. External Content Types that connect directly to databases, WCF or Web Services can be authored and included in SharePoint solution projects, but only XML-based authoring (i.e. no visual design experience) is supported for them.

Authoring BCS-enabled Reusable Components

In Visual Studio 2010 you can author BCS-enabled reusable components for both client and server by creating standard class library projects that implement specific BCS-defined interfaces as well as making use of the BCS Object Models to access external data. Some examples of reusable components are:

- Code actions that can be invoked through the ribbon within Outlook

- External Data Parts that can be hosted in declarative taskpanes in Outlook to present external data

- Workflow activities that access external data using the BCS Object model. These can then be made available for use within the Workflow Designer in SharePoint Designer 2010.

- Field renderers can be created to provide custom UI for complex data types inside External Lists

- Custom credential providers that can be used to access systems that support custom authentication mechanisms

Authoring BCS-enabled Office Add-ins

Visual Studio 2010 allows you to integrating external data into Office applications by creating a standard Office add-in project that uses the BCS object model.

External Data in Workflows

Visual Studio 2010 allows you to access external data by authoring code SharePoint workflows that interact with External Lists or the BCS object models.

For more information on how to create an external list using Visual Studio 2010 take a look at the SharePoint team’s blog post: "Walkthrough of creating a SharePoint 2010 external list using Visual Studio 2010 Beta"

Interoperability between SharePoint Designer 2010 and Visual Studio 2010

When authoring SharePoint solutions, you can actually use a combination of Visual Studio 2010 and SharePoint Designer 2010, you just need to be aware that they have slightly different authoring and deployment models that affect interoperability. SharePoint Designer 2010 works with a “live” connection, so changes are saved directly to the SharePoint server. In the case of Visual Studio 2010, projects need to be first built and then deployed to the SharePoint server. This is based on WSP packages that can be manually deployed or be deployed automatically when the user run or debugs the project.

Projects created in Visual Studio 2010 can be further edited in SharePoint Designer 2010 after they have been deployed to the SharePoint server. However, components authored in SharePoint Designer 2010 need to be manually exported/imported into Visual Studio 2010. In the case of Business Data Connectivity Models, SharePoint Designer 2010 allows you to export one or more External Content Types into an XML file that can then be imported into a Visual Studio 2010. Note that Visual Studio does not support a visual experience for External Content Types that are not based on a .Net Assembly, so for these an XML-only authoring experience will be provided.

SharePoint SDK

The SharePoint SDK will include documentation, samples and guidance for creating SharePoint solutions that leverage BCS. There is one type of solution that will be exclusively supported through the SDK. It is known as an Outlook Declarative solution and it allows you to extend Outlook with custom taskpanes, ribbon actions, forms and views based on declarative XML files that are packaged and deployed together to be interpreted by the BCS at runtime. The SDK will include a packaging tool that will allow you to create this type of solution package from a file system folder containing your solution files. Stay tuned for future posts that will cover more details about this type of solutions.

Summary

The following table provides a side-by-side comparison of the different capabilities in Visual Studio 2010, SharePoint Designer 2010 and the SharePoint SDK.

- Rolando Jimenez Salgado, Sr. Lead Program Manager

BCS Team Channel: External Lists in Outlook

The second installment of the BCS Team Channel is now live! Juan Balmori Labra takes us on a tour of how you can surface external data in Outlook 2010 as Contacts, Appointments and Posts.

The next video will show you how external data is taken into SharePoint Workspace 2010.

- Lionel Robinson, Program Manager

BCS Tooling Investments in 2010: Part 1

One of the most popular requests around the Business Data Catalog (BDC) in Microsoft Office SharePoint Server 2007 is for additional tooling support. Business Connectivity Services (BCS) is the evolution of BDC, so for this release we have made big investments in SharePoint Designer 2010 and Visual Studio 2010 to facilitate the creation of SharePoint solutions that leverage Business Connectivity Services. With a set of new BCS capabilities in these two applications and the documentation, guidance, samples and utilities included in the SharePoint SDK, we expect to empower a broader audience ranging from power users to advanced code developers to create solutions that integrate external data.

In a previous post, Brad Stevenson introduced the different types of solutions enabled by BCS. In this post series I’d like to provide an overview of the tools you can use to create them. As I explained in Introducing the External Content Type, External Content Types (ECTs) are the basic building block for creating these solutions so you will see that the most prominent tooling investments are around External Content Type authoring. Both SharePoint Designer 2010 and Visual Studio 2010 offer External Content Type designers, however they target different scenarios, types of data sources and audiences. Let's start by taking a look at the new BCS capabilities in SharePoint Designer 2010.

SharePoint Designer 2010

SharePoint Designer provides a “no code” experience for composing (composing refers to the fact that the solution author assembles a solution out of existing reusable components as opposed to building it from scratch) simple to intermediately complex solutions based on External Content Types which you can author through a wizard-driven user interface. Once an External Content Type is created, you can easily create and customize External Lists, Web Part Pages, Workflows and Forms that are based on it. Let me describe how each of these tasks is supported in SharePoint Designer 2010.

External Content Type Authoring

When you open a site in SharePoint Designer 2010, you will now notice a new gallery and designers for authoring External Content Types. The gallery allows you to access the list of External Content Types published into the shared BCS metadata store (formerly known as the Business Data Catalog). The designers support connectivity to SQL Server databases, WCF services (including Web Services) and .Net assemblies (previously created by a code developer). The modeling approach is to discover and map the data schema and operations exposed by the external data source to the desired schema and stereotyped operations (Read Item, Read List, Create, Update and Delete) of your External Content Type. Because of this, it is necessary that the interface exposed by the external data sources is “BCS friendly” for SharePoint Designer 2010 to be able to consume it. Direct access to individual database tables is always considered “BCS friendly”. For stored procedures, WCF services, Web Services and .Net assemblies, I recommend you to read the series of posts in which Sanjay Rama describes how to design BCS friendly interfaces.

Figure 1. Screenshot of the new External Content Type gallery showing the External Content Types currently published to the shared BCS metadata store.

SharePoint Designer 2010 also allows you to define associations between External content Types based on foreign keys. This is done by configuring stereotyped association navigation operations that enable retrieval of associated External content Type instances.

In addition to data schema, stereotyped operations and associations, SharePoint Designer 2010 allows you to configure the Office integration behaviors of you External Content Type. For example, you can configure data filters, search support, data offlining into Outlook and SharePoint Workspace, as well as mappings to native item types like Contact, Task, Appointment and Post.

SharePoint Designer 2010 supports the most common external data connectivity scenarios; however there are advanced connectivity behaviors that are not supported like bulk and batch operation stereotypes, generic/polymorphic services, associations not based on foreign keys, etc. In future posts we’ll cover how to deal with these advanced scenarios.

External List Authoring

Once you have an External Content Type defined, SharePoint Designer 2010 allows you to create External Lists based on it. This can be done through an auto-generate option available in the External Content Type’s summary page or by choosing the new External List option in the Lists gallery.

InfoPath Form Generation and Authoring

SharePoint Designer 2010 allows you to “upsize” any External List to use InfoPath forms instead of the standard SharePoint forms. You can choose to auto-generate InfoPath forms when you create a new External List or you can generate forms for existing lists. The auto-generated forms are fully functional and ready to use, except for the cases where one or more fields on your External Content Type are considered “complex”. We’ll talk more about complex fields in future posts, for now let’s simply define a complex field as one which data type involves a structure that goes beyond a simple value, for example and address structure that includes street, city, state and country. In this case a form will be generated, but manual customization of the form may be required to make it functional. In any case you can always customize existing External List InfoPath forms using the full power of InfoPath Designer 2010 which has been extended to provide an integrated form customization experience when launched in the context of an External List.

External Data in Workflows

In the workflow space, SharePoint Designer 2010 allows you to access external data by authoring workflows that interact with External Lists. The read and write workflow activities for regular SharePoint lists have been extended to support External Lists. This means that the authoring experience is as simple and flexible as you know it for regular lists, with the additional benefit of being able to access an external data source.

Note that External Lists themselves do not support triggering workflows. However in SharePoint Designer 2010 you can create a site level, list level or reusable workflow that accesses one or more external lists.

Web Part and Profile Pages

SharePoint Designer 2010 offers general support for authoring Web Part Pages which allows you to create custom pages based on the External List and/or the BDC Web Parts. Specific to BCS, SharePoint Designer 2010 allows you to auto-generate Profile Pages for an existing External Content Type. A Profile Page is a special type of Web Part Page that shows a 360 degree view of an External Content Type instance by showing its details as well as its associated External Content Types. Profile Pages are the default target of external data search result links.

If you need to integrate external data into SharePoint and Office without writing code, SharePoint Designer 2010 will probably be your best bet.

In the following post we will go through the design experience in Visual Studio 2010, how it supports complex code-based data integration scenarios, and the interoperability between SharePoint Designer 2010 and Visual Studio 2010.

- Rolando Jimenez Salgado, Sr. Lead Program Manager

Introducing the External Content Type

External content type is probably the most important Business Connectivity Services (BCS) concept to understand, so I decided to create a brief post about it. In essence, an external content type is the SharePoint and Office representation of a real business entity like a Customer, Order or Employee.

Implementation-wise, external content types are the evolution of BDC entities from Microsoft Office SharePoint Server 2007. They are metadata descriptions of the connectivity settings, data structures and stereotyped operations – like create, read, update and delete - that allow access to business entities as exposed by external systems (such as databases, web services, line-of-business applications, among others). In addition to the new stereotyped operations now supporting read/write access to external data, external content types in SharePoint 2010 can also describe SharePoint and Office integration behaviors like mappings to native Office item types including Contact, Task, Appointment and Post as well as data offlining into Outlook 2010 and SharePoint Workspace 2010. These are very important new aspects as they are the basis for providing familiar experiences for information workers to interact with external data inside SharePoint and Office.

You can learn more about External content types in the SharePoint SDK topic “What Are External Content Types?”. In this post though, I’d like to emphasize their role as a building block for features and solutions that leverage BCS.

SharePoint 2010 features like External Lists, External Data Search, and External Data Columns are all based on external content types. In a similar way, SharePoint and Office solutions needing access to external data can do so through BCS by relying on one or more external content types. This means that defining external content types will be among the first steps of building a BCS-enabled solution. Once you define an external content type, you can use it to easily integrate external data on client and server thanks to the Business Connectivity Services runtime available with SharePoint 2010 and Office 2010.

In terms of authoring, both SharePoint Designer 2010 and Visual Studio 2010 provide external content type designers that allow you to create, edit and publish external content types into the Business Data Connectivity Metadata Store in SharePoint 2010. Once published, external content types can be reused across SharePoint and Office either by end-users using the out-of-the-box SharePoint features or by solutions created by power users and/or developers.

Figure 1. Summary view in SharePoint Designer 2010 (Beta) of an external content type representing business customers stored in a SQLServer database.

Figure 2. Visual Studio 2010 (Beta) showing an external content type with methods to read customer information (from two different line-of-business applications) implemented in a .Net assembly.

Now that you know about external content types, go ahead and create some in SharePoint Designer 2010 or Visual Studio 2010 to experience their power first hand!

- Rolando Jimenez Salgado, Sr. Lead Program Manager

Duet Enterprise for Microsoft SharePoint and SAP

We’re excited to announce that at the Professional Developer Conference 2009 (PDC09) Microsoft announced the intent to deliver Duet Enterprise for Microsoft SharePoint and SAP.

Built on top of Business Connectivity Services, this jointly developed solution will enable interoperability between SAP applications and SharePoint 2010. To learn more about the solution that is planned to be released in the second half of calendar year 2010 go to:

• Duet’s website
• Duet Enterprise press release
• www.Microsoft.com/Duet
• www.SAP.com/Duet

- Lionel A. Robinson, Program Manager

Permissions in Business Connectivity Services

Solution designers, administrators, and end users interact with composite solutions using external content types, which enable the presentation of and interaction with external data in SharePoint lists (known as external lists), Web Parts, and supported Microsoft Office 2010 client applications. Permissions are recorded in the metadata definitions for the various objects stored in the BCS metadata store, such as external systems, models, and external content types. By correctly setting permissions on objects in Microsoft Business Connectivity Services, you help enable solutions to securely incorporate external data.

Roles in Business Connectivity Services

Following are the roles that individuals (or processes) in an organization must fill in Business Connectivity Services scenarios. Depending on your solution goals, individuals and groups in these roles may be assigned various levels of permissions on the objects in the metadata store:

• SharePoint Server administrator: Deploys, administers, and maintains the server farm and creates the shared services that Business Connectivity Services depends on.
• Database administrator: Deploys, administers, and maintains the database server.
• Shared Service administrator: SharePoint Server administrators can delegate administration of an instance of a shared service to a shared service administrator.
• Solution designer: Develops models and external content types using SharePoint Designer 2010.
• Solution developer: Uses development tools such as Visual Studio 2008 to create external content types, Web services, and other components of a BCS solution.
• Solution user: Interacts with the external content type to modify data or enter new data.
• Solution viewer: Views the external data in Web parts or external lists.
• Application pool account: The account under which a shared service or other Web application will run.

What can permissions be assigned to?

Permissions in the Business Connectivity Services associate an individual account, group account, or claim with one or more permission levels on an object in a metadata store. Depending on the object for which the user or group is being granted permissions, the permission level specifies the actions that the user or group can take on that object. All permissions on objects in the Business Connectivity Services can be set using the following values: Edit, Execute, Selectable in clients, and SetPermissions. This section describes the types of objects in Business Connectivity Services on which permissions can be directly set and, for each object, describes how to assign permissions depending on the actions you want to permit.

Metadata store

The metadata store is the collection of XML files in the Business Data Connectivity service that contain definitions of models, external content types, and external systems.

Model

A model is XML file that contains sets of descriptions of one or more external content types, their related external systems, and information that is specific to the environment, such as authentication properties.

External system

An external system is the metadata definition of a supported source of data that can be modeled, such as a database, Web service, or .NET connectivity assembly.

External content type

An external content type is a reusable collection of metadata that defines a set of data from one or more external systems, the operations available on that data, and connectivity information related to that data.

Method

A method is an operation related to an external content type such as Read or Update.

Method instance

A method instance describes, for a particular method, how to use a method by using a specific set of default values.

Example Scenario

In this scenario, a small departmental Web server hosts both SharePoint Server 2010 and a SQL Server database containing external data that will be integrated into a composite solution. For example, a small organization could use Business Connectivity Services to interact with customer contact information that is stored in a SQL Server database by creating a composite solution that exposes the data both in a SharePoint site using external lists and Web parts and from Microsoft Outlook 2010. Some users of the solution will have authorization to add new contacts or modify existing ones; other users will have read-only privileges.

The following permissions are typical for this scenario:

For more information on setting Business Connectivity Services permissions, along with other security-related topics, see my TechNet topic Business Connectivity Services security overview (SharePoint Server 2010).

- Robert Silver, SharePoint IT Pro Content Team

This post was updated 12/10/2009

Office 2010 and SharePoint 2010 Public Beta

The BCS team is excited to announce the Office 2010 and SharePoint 2010 public betas are available for download from www.microsoft.com/2010. Now for the first time anyone can download and test drive the new capabilities Business Connectivity Services has to offer. So go ahead and download it now, try it out and tell us what you think about it.

Along with the software you can also download the SharePoint 2010 (Beta) SDK. Randall and the SDK team did a great job announcing its availability on their blog post: SharePoint Content and Resources. Being in a beta stage, the SDK documentation is continuously updated; so for the most up to date documentation check out the BCS documentation on MSDN.

- Lionel A. Robinson, Program Manager

The Notion of Associations and the External Item Picker

In real world problems there is a frequent need to find related or complimentary information for a specific business entity. For example, while looking at a customer profile, I would love to see additional information about it such as what are the latest orders placed by that customer, or the top 5 products it usually buys or any other relevant information for it, you name it. Business Connectivity Services supports the ability to associate two External Content Types (ECTs) to support such scenarios. This blog post introduces the concept of associations in BCS, how the concept is materialized in the product UI and a quick example on how you can define a simple association between two ECTs using SharePoint Designer 2010 (SPD).

The most common type of association is the one-to-many or master-detail one. Following the classic Customer-Orders example, by defining the association BCS will enable the following scenarios:

• While looking at a customer profile I am able to see to the orders submitted by that customer. (master-detail report)
• While browsing an order I can navigate to the details of the customer that placed it.
• While creating an order you need to assign a customer for it, but you want to pick from a list of available and existing customers rather than guessing the customer’s primary key (identifier) on the order creation form. Once a customer is picked, you want to see the name of the customer rather than its primary key.
• While browsing an order, you want to see more details about the customer that placed it.(reverse lookup)

It’s really easy to create an association between two ECTs. As a prerequisite we need to start with two existing ECTs, in this case Customer and Orders. Then you need to do define a navigation method on the ECT that contains the foreign key, in this case the Order:

1. Right click Orders table and select ‘New Association’

2. A wizard will pop-up in SPD prompting to select to which ECT to create the association, we need to select Customers by clicking on ‘Browse’, then we click ‘Ok’ and ‘Next’

3. Configure input parameters for the navigation method, by specifying the identifier of the associated ECT, in this case the CustomerID. Make sure to select the identifier in the data source elements panel and then hit on the ‘Map to Identifier’ check box on the right.

4. Skip the filter configuration

5. Configure output parameters for the navigation method. The navigation method is meant to return the Orders for the specified Customer so we need to map the return identifier, in this case the OrderID.

6. Click on ‘Finish’.

7. Create BCS artifact, such as external lists, profile pages etc.

Once this is complete we’ll notice the associations are visible in the UI in several parts of BCS components:

1. On the external list forms: The most visible presence of associations will be that on the order creation form, an external item picker will be placed to select a customer for the order. Users now are enabled to pick from an existing list of customers for this form. If the external list is synchronized to Outlook or to SharePoint Workspaces, a rich client version of the picker will be shown on the forms to create orders as well.

On both cases it’s possible to show a friendly display name on the external item picker control upon customer selection to show something more meaningful that the number that identifies the customer in the external system, for instance the customer name, or whatever field that is tagged as the title field in SPD.

2. On the profile pages: Notice how once an association is defined, the orders for the customer will appear on the customer’s profile page. By the same token, in the profile page for Orders, if there is a reverse association defined user will be able to navigate and see more customer details.

3. Business Data Parts: The Business Data List and Business Data related list web parts take advantage of associations and can enable interesting scenarios such as the one shown in the figure below in which the Business Data List part on top lists a set of customers and upon customer selection it shows the related orders on the Business Data related list part below.

So in this post we reviewed how to define association between ECTs on BCS and also saw them in action through external lists, profile pages and business data parts. This topic is just the tip of the iceberg of the association’s concept; on subsequent posts we’ll review more complex scenarios around associations such as self-referential associations, multiple associations, associations between ECTs of different external systems, composite identifier associations and more.

-Juan Balmori Labra, Program Manager

Making Web Services BCS Friendly – Part 1

As mentioned in the overview post, Business Connectivity Services in SharePoint 2010 allows end users to interact with external data using SharePoint and Office UX. For this, BCS provides an abstract data access layer that SharePoint and Office applications can consume. The abstraction is achieved through some stereotype methods supported in BCS. These stereotypes are based on some of the common patterns in popular external systems.

Consider an External list in SharePoint 2010, one of the primary presentation features for external data in SharePoint 2010. A very basic form of an external list depends on two methods:

1. A method for reading the details of an item given its identifier
2. A method to query a list of items given a filtering criterion

For bringing external data into SharePoint via a backend web service, the web service will have to support at least the two methods listed above.

In BCS, these two methods are represented through two stereotypes called “Specific Finder” and “Finder” respectively. Each stereotype imposes some semantic requirements that the backend web service methods have to satisfy for them to be mapped correctly.

In this post, we will review the semantic requirements for a “Specific Finder” and a “Finder” and the design recommendations for the corresponding web services methods to make them BCS friendly. These recommendations are applicable to .Net assemblies and WCF services as well.You can see the full list of stereotypes on MSDN.

Specific Finder

This stereotype is used to read an item given its identifier. For example, given a “customer” business object, this method can be used to obtain detailed customer information for a given customer Id.

Recommendations for the corresponding web service method

To qualify as a Specific Finder, the corresponding web service method should:

• Take an identifier (or combination of identifiers) as one of the input parameters
• Return a set of fields (called a “view” in BCS. See definition below)
• Always return the item identifier as one of the fields returned

View

A “View” is a collection of fields, each of which has a name, a type, and, optionally, localized display names. It is a schematic definition – unlike views in SQL databases which include both the schematic definition and the data returned as part of the query.

Example: If “Employee” is the business object, then a “view” of employee may contain the following fields:

• Id
• Name
• Address
• Designation

It is possible to have multiple views of the same business object in BCS. For example another view of “Employee” may contain the following fields:

• Id
• Name
• Address
• Designation
• AnnualIncome
• StartDate
• SeniorityLevel
• EmergencyContact

Note: If the view returned by the read operation includes fields that automatically change just by reading the item (e.g. TimeStamp for tracking when the item was “Last Read”), they need to be marked with a special attribute called “significant” in the BCS application model.  This is because as part of an update operation, BCS performs a read before updating the item to detect data conflicts. This is done by a state comparison between the cached or in-memory state of the item and the current state in the external system. If this attribute is set to false, these fields will be excluded from the hash calculation to determining data conflicts. If not, data conflicts will be produced for every update operation.

Finder

This stereotype is used for reading a list of items given a filtering criterion. For example, given a business object called “Customer”, this method can be used to obtain a list of customers whose order amount is in a certain range.

Recommendations for the corresponding web service method

To qualify as a Finder, the corresponding web service method:

• Should take the filtering criterion as the input parameter(s) to limit the number of items returned (especially when returning a large number of items)
• Should return collections of items for a given business object
• Should return the identifier of the item as part of the view returned for each item
• Should support rich filtering (See filtering support in BCS for details on what filters to support)
• The “view” for each item returned by this method must be equal to or a subset of the view for the specific finder method. This is because the create and update operations are dependent on the specific finder view. If the finder returns more fields, the extra fields will not be update-able (more details on create/update operations will be covered in the future posts). Also, if finder only returns a subset of the data, a specific finder call will be executed to bring the rest to ensure that the cached items are ‘complete’. Hence both specific finder and finder having identical views is the best case scenario. BCS will verify the views for both during run time and avoid making the extra call if they are identical.

Multiple Specific Finders:

In SharePoint 2010, a business object can have multiple specific finders with different views. Following are some scenarios where multiple specific finders are useful:

• Role based views: Given an EmployeeId, one specific finder method can be used to present a limited view to peers and the global address book while another specific finder method can be used to present the other detailed view to the employee or the employee’s manager.
• Cache optimization: BCS has rich support for taking business data offline to office clients like Outlook and SharePoint Workspace. Administrators can disable offlining for detailed views to optimize cache usage on the client side. This allows users to have a limited view offline and access the detailed view while online.

Note: If there are multiple specific finders with different views of a business object, it is good to have at least one finder method for each specific finder method.

Bulk Specific Finder

This stereotype allows querying for multiple items given a collection of identifiers.

Recommendations for the corresponding web service method

To support this stereotype, the corresponding web service method should take a collection of identifiers as input and return a detailed view for each of the items. All other requirements are exactly same as the specific finder.

In this post we discussed the two basic stereotypes required for presenting external data in a “read only” external list. In the next post we will discuss the create, update and delete stereotypes required for enabling CUD capability followed by future posts that will cover the other advanced stereotypes such as Association Navigators, Id Enumerator etc.

This post was updated 11/20/2009

- Sanjay Rama, Program Manager

BCS Team Channel is now Live!

The first of a series of episodes on the BCS Team Channel is live! Watch our very own Sanjay Rama introduce BCS and show how external data can be viewed and manipulated (Create, Read, Update and Delete data) through the browser.

Upcoming videos will include synchronizing external data into Outlook, SharePoint Workspace, and other increasingly advanced scenarios.

- Lionel Robinson, Program Manager

Types of Solutions

Solutions that leverage Business Connectivity Services (BCS) typically fall into one of three high level categories:

• Simple solution leveraging out-of-box capabilities
• Intermediate declarative solutions
• Advanced code-based solutions

The first two do not require the use of code, although they can re-use code-based components that have been published by developers. This is important for two reasons:

1. They dramatically increase the set of users who can build these types of solutions, freeing up developers and IT organizations to focus their resources on solving other problems, and
2. Central IT retains control over the types of things that these solutions can do, which creates fewer management headaches and has the side benefit of making it easier to “convert” them to a Central IT-supported app rolled out to a larger audience in the future. (Because they are created and customized using a standard set of capabilities and tools)

The Advanced category is entirely code-based, leveraging Visual Studio as the environment to compose and publish re-usable components or entire end-to-end solutions.

Figure 1: Three different types of BCS solutions, types of users who create them, the tools used to create them, and common scenarios that can be accomplished with each.

Let’s review the characteristics and benefits of each type of solution:

Simple Solution Leveraging Out-of-box Capabilities

In SharePoint Foundation 2010, you can surface external data in an external list or on a web part page via the Business Data web parts or the Chart web part. You can also add an External Data column to a standard SharePoint list or document library. That column can then be exposed as a Quick Part (aka content control) in Microsoft Word.

In Outlook 2010, you can take an external list offline from SharePoint Server 2010 with Enterprise Client Access License and allow users to interact with the external data from within Outlook. Users see the same formatting (contact, appointment, task or post) as regular Outlook items and can use the same gestures to interact with them. This type of solution runs under the native BCS Outlook Add-in, which is installed with Office 2010 and loaded at Outlook startup.

In Microsoft SharePoint Workspace 2010, you can take an external list offline from SharePoint Server 2010 with Enterprise Client Access License and allow users to interact with the external data from within SharePoint Workspace. Business Connectivity Services does not provide an extensible programming model to extend this type of a simple solution, but you can associate an InfoPath Form with the External List, which then opens up the ability to customize the form and present that customized form on both the server and on the client in SharePoint Workspace.

Users in Outlook and SharePoint Workspace can synchronize data directly with the external system(s) on an automatic basis (default is 3 hours) or by explicitly clicking an action. They can also check for updates to the external list – i.e. The structure of the external list, its forms or views.

Tool Support

You create external content types by using SharePoint Designer, Visual Studio, or an XML editor. Then, you create an external list using the browser or SharePoint Designer and click a button in the SharePoint ribbon to connect it to Outlook or SharePoint Workspace. Web part pages and external data columns can be created using the browser or SharePoint Designer.

Intermediate Declarative Solution

The most common types of Intermediate solutions are Simple solutions that have been further customized to add capabilities. These additional capabilities include InfoPath forms, SharePoint workflow, and SharePoint web part pages. You can customize InfoPath forms that present External Data by changing the look and feel, adding declarative rules/business logic, or adding code-behind. The latter requires the form to be published as an admin-deployed form to the server – consult the InfoPath 2010 documentation on MSDN for more information about these capabilities, walkthroughs and samples. You can also create or add capabilities to SharePoint workflows through SharePoint Designer by either a) configuring the out-of-box SharePoint List activity to read data from, or write data to an external list, or b) re-using a custom workflow activity built in Visual Studio (and published to SharePoint) that interacts with external lists or the BDC runtime object model. Finally, you can create web part pages that leverage out-of-box web parts (Business Data Item, List, Related List, Actions, Item Builder, and Filter as well as the Chart web part) and optionally configure part-to-part connections to send data between them. The look and feel of parts can be customized by editing the XSLT of each part in the web part toolpane.

Another type of Intermediate customization involves external data in Outlook 2010. An example of a common scenario that can be enabled in this space would be showing related Order information in a custom taskpane when a user has a Business Contact inspector open.

Starting with basic Outlook elements, you can customize the view that is shown for a folder of external data. This is done by opening the folder in Outlook and using the standard commands to build and save a new view. BCS provides a command to save the customized view to SharePoint and place it a sub-folder of the External List, making it available to future users who connect the list to Outlook.

Additional Outlook customizations are slightly more complex than other Intermediate customizations and require the creation of XML files (for example, BDC Model, Solution Manifest (OIR.Config), Subscription, Ribbon, and Layouts) and then a ClickOnce package by using BCS SDK tools. Users can then deploy the solution in Outlook by installing the ClickOnce package. In such a solution, you can define custom taskpanes and present external data to users via External Data Parts (either out-of-box or code-based custom parts) hosted in a taskpane. You can also define ribbon files and custom actions (exposed in the ribbon or in an external data part) that either trigger code or launch a browser pointing to a URL. Finally, you can customize Outlook forms by starting with the auto-generated forms that BCS provides, tweaking them and saving/exporting the file(s). Customizations here leverage the Business Connectivity Services rich client runtime (including the BCS Outlook add-in) which presents the elements defined in the XML files at runtime.

Tool Support

A variety of tools including InfoPath Designer (for forms), SharePoint Designer (for workflows), a browser (for SharePoint web part pages) and Outlook (for customized forms and views) can be used to build intermediate solutions.

To create the XML files needed for your Outlook declarative solution, you can use any XML editor. Visual Studio can provide IntelliSense, which can be helpful when creating these XML files. Samples to get you started will be available as part of the BCS Developer documentation and SDK resources on MSDN. Business Connectivity Services provides an SDK tool that can be used to create a ClickOnce package for Outlook declarative solutions.

Advanced Code-Based Solutions

These solutions can involve the creation of reusable components (.net assembly connector to aggregate or transform data from external systems, custom web parts, custom workflow activities, code-behind for InfoPath forms, and code-based actions or external data parts for use in Outlook declarative solutions) or an entire end to end solution that leverages the public Business Connectivity Services object model.

A code-based Microsoft .NET Framework solution created in a tool such as Visual Studio can use any element of the public Business Connectivity Services object model and can enable users to interact with external data. It can register with the Business Data Connectivity (BDC) service by using the BDC object model to present data in SharePoint, an Office 2010 application such as Microsoft Excel, or a custom application. This object model is installed with SharePoint Foundation 2010 and Office 2010. External data can be retrieved directly from the external system while connected, or it can be retrieved locally from the BCS rich client cache provided that it’s already available (typically achieved by taking external lists offline to SharePoint Workspace or Outlook). This type of solution can be used to extend BCS to Office applications that are not supported out of the box, such as Excel or PowerPoint.

In a code-based end to end solution, the developer controls all of the user interface, packaging, and deployment. This type of solution cannot make use of the Business Connectivity Services rich client runtime, which is used by Simple and Intermediate solutions to integrate data into Office applications.

Tool Support

Code-based solutions are developed in a tool such as Visual Studio 2010. A BDC project type is available to facilitate the creation of .net assembly connectors.

To summarize, there’s a broad spectrum of solutions that can be built using BCS. These range from simple solutions that rely on out-of-box capabilities with little or no customization, to intermediate solutions that involve customizing a wide range of features in SharePoint and Office 2010. Advanced solutions involve the creation of code via Visual Studio, and can either be complete end to end solutions, or provide re-usable code-based components that can be included in an intermediate solution.

This post was updated 11/20/2009

- Brad Stevenson, Sr. Lead Program Manager

BCS Overview Demo Video

In this three-part video Brad Stevenson goes through a high-level overview out-of-the-box functionality provided by Business Connectivity Services. You’ll be able to observe:

• Full read/write capability of BCS
• Create an external content type which connects to a SQL backend
• Bring external data into SharePoint through an external list
• See how the external list provides the same functionality of a normal SharePoint list
• Take the external data into Outlook and SharePoint Workspace
• Auto-generated forms and customized InfoPath forms for external lists
• External Data Columns and using Word templates to fill in information using external data

We hope you enjoy the video and stay tuned for more demos from BCS like Rolando Jimenez’s tooling demo and a series of short 5 minute tours of BCS put together by the team.

Video Part 1 of 3

Video Part 2 of 3

Video Part 3 of 3

- Lionel A. Robinson, Program Manager

Announcing the Business Connectivity Services Resource Center

The SharePoint IT Pro content team is proud to announce the launching of our new Business Connectivity Resource Center. We will keep this resource center up to date with timely information to help you design, build, and deploy SharePoint sites and solutions that integrate external data and services.

Because the Business Connectivity Services Resource Center is going live as Microsoft prepares to release SharePoint Server 2010 Beta, we have made sure that the site links to all the available Business Connectivity Services Beta content, including content for IT professionals, solution designers, and developers. To keep you up to date, we will continue to add links during Beta and beyond. Bookmark this page to keep learning about the latest Business Connectivity Services content.

Along with linking to our own content about Business Connectivity Services, we will use this resource center to highlight great community content. So please use the ratings and feedback control at the top of the page, the Site Feedback control at the bottom, or reply directly to this blog entry to suggest content that you’d like us to link to. You can also use these mechanisms to request additional content that you want us to write or to give us feedback on the resource center.

- Emily Schroeder and Rob Silver, SharePoint IT Pro content team