Creating Office Business Applications in Microsoft SharePoint 2010

Applications in an organization can be very diverse with information being spread across multiple source systems. Information comes in the form of structured or unstructured data. Structured data is sourced from enterprise applications such as SAP or Siebel or databases such as Microsoft SQL Server or Oracle. It can also come from a proprietary-based system developed to support a specific organization. Unstructured data can be sourced from office applications such as emails, documents, or work sheets or Web 2.0 services such as Internet applications, blogs, wikis, and social network sites. These dynamic and diverse environments present information workers with many challenges.

Organizations have attempted to resolve these issues by creating custom applications and web portals, which bring up other challenges to the IT organization. In this article, I explain how, together, Microsoft Office 2010 and SharePoint 2010 provide a great platform for building modern office business applications that not only ease the information worker challenges but also reduce the pain of IT organizations.

Challenges and Benefits

The use of diverse data sources can present information workers with various challenges, such as getting used to different source system interfaces and constantly switching between applications. For example, to create a sales proposal, a sales person needs to look up data in the Siebel or SAP system and look up inventory levels in a separate proprietary system. All this information is later manually brought into a sales template.

In an effort to reduce these problems, organizations often create custom applications and web portals. These solutions often bring up challenges for IT personnel, as well. These challenges include:

Together Office 2010 and SharePoint 2010 provide a great platform for building Office Business Applications that not only ease the information worker challenges but also reduce the pain of IT organizations. When you use the Office 2010 and SharePoint 2010 capabilities, you reap the following benefits when building Office Business Applications:

Business Connectivity Services

Business Connectivity Services (BCS) is an evolution of the Business Data Catalog (BDC) in Microsoft Office SharePoint Server (MOSS) 2007. BCS enhances the capability of SharePoint 2010 as a platform for developing composite applications. It provides features, services, and tools that streamline development of composite applications with deep integration of external data and services. It provides the capability to connect SharePoint 2010 and Office 2010 client applications to external data sources such as SQL Server, Oracle, SAP, Siebel, or custom applications. Compared with BDC, which supported read only entities, BCS supports full create, read, update, delete, and query (CRUDQ ) external content types.

External Content Type

An external content type is a core component of a BCS application model. It represents a reusable description of an object that can be used in a composite application. Examples of external content types are Customer, Order, or Contact. The external content type includes data fields; operations (e.g., create, read, update, or delete) that users can take on the object; and information that supports connecting to the external data source where the object is stored. BDC had a similar concept of Entities. External content types are more powerful than Entities as they support CRUDQ operations. It lets you perform batch and bulk operations that can significantly improve performance of BCS solutions. External content types also allow you to specify behavior within Office (e.g., they should behave like an appointment when surfaced in Office.)

External content types let you represent external data stored in source systems such as SAP, Siebel, SQL Server, SharePoint, and web services. SharePoint Designer 2010 can build external content types that pull content from databases, WCF/Web Services, or .Net Connector end points, whereas Visual Studio 2010 can be leveraged to build external content types that use .Net Connector.

BCS Client-Side Infrastructure

Figure 1 shows the components of the BCS architecture. BCS client-side components provide Office 2010 the capability to connect to external systems. It uses a client-side cache that not only provides faster data access but also provides the ability to perform offline operations. It has a built-in basic support for error handling and conflict detection when end users come online and syncs their changes. Out of the box, BCS provides integration with the following Office client applications.
SharePoint Workspace (formerly Groove). SharePoint Workspace provides support for taking external lists in SharePoint 2010 offline. For specialized scenarios in which the external content type maps directly with Outlook Item types, it can be associated with Outlook.
Outlook. An external content type can be associated with an Outlook type such as Contact, Appointment, Task, or Post. External data can be synced easily between Outlook and the external source system. BCS creates a separate external list PST folder in Outlook. It supports customizing the task pane, ribbon, forms, and views without writing any code.
Word. BCS enables connecting to external data directly in a read-only fashion using Word 2010. For example, you can create a document that has fields that are bound to external content types.
InfoPath. SharePoint Designer 2010 lets you create InfoPath forms that can replace out-of-the-box SharePoint forms for external lists.
Access. Access 2010 leverages BCS to let you connect to external data and develop rich Access 2010 applications around external systems.

For applications such as Excel or PowerPoint there is no out-of-the-box integration with BCS, but it's possible to develop custom add-ins for Excel using Visual Studio Tools for the Microsoft Office System (VSTO) that leverage BCS client infrastructure. Custom VSTO add-ins can also be created for Outlook and Word.

BCS Server-Side Infrastructure Components

The server-side infrastructure provides a symmetrical runtime to the client side to facilitate the various operations and behaviors of BCS. BCS is developed as a service application on SharePoint 2010. It has a separate store to upload application models for BCS solutions. BCS fully supports the latest claims-based security model for SharePoint 2010. It fully supports claims-enabled backend systems. BCS provides application packaging, deployment, and administration. With the use of click-once technology, BCS simplifies upgrade management of client-slide solutions. Out of the box, BCS provides integration with the following components of SharePoint 2010.
External list. This has the power and familiar look and feel of a standard SharePoint list but has the ability to pull data from an external system using an external content type.
Web parts. It supports web parts such as Business Data Item, List, Related List, Actions, and Chart web parts for rich visualization of external data.
External data column. For a SharePoint list, it allows creation of external data columns that can point to data in an external system using external content types.
Workflow. SharePoint workflows can be integrated with external data lists or external data columns and can perform interesting scenarios against external data. Both SharePoint Designer 2010 and Visual Studio 2010 development tool supports workflow integration.
Search. SharePoint 2010 Search supports crawling external data using external content types.

BCS Connectivity

BCS out of the box provides the following connectivity options: .Net Assembly Connector. This option allows great flexibility for connecting to external systems. This option is for advanced BCS scenarios where the solution demands data transformation and/or data aggregation across multiple-source (external) systems. It also opens the door for connecting BCS to any external system via a custom connector developed using .Net code.
Database. The database option allows direct integration with SQL Server. To connect to a different ADO.Net endpoint, you need to edit the model file, which is fully supported by the BCS Runtime.
Web services. Web services options allow connecting to any SOAP based .NET web services. For example SAP, Siebel, or custom Line of Business (LOB) systems expose web services for operation/data integrations. Using this method, you can develop a BCS solution against these external systems.
WCF. The WCF connectivity option allows connecting BCS solution to a WCF end point. Using this functionality, a BCS solution can be developed against cloud-based services.

Development Tools for BCS

BCS applications can be developed using SharePoint Designer and/or Visual Studio 2010. The development tools provide a seamless transition between SharePoint Designer 2010 and Visual Studio 2010 back and forth.

SharePoint Designer 2010. SharePoint Designer provides a no-code experience to a power user to rapidly develop BCS solutions. It's intended for simple backend scenarios that don’t require any custom integration logic. Out of the box, it provides the ability generate external lists and associate it with an external content types; integration of external data with SharePoint Workflows; generating InfoPath forms for an external list; and creation of profile pages for an external content type.

Visual Studio 2010. Visual Studio 2010 is intended for advanced development that requires custom code such as complex data aggregation across multiple source systems or data transformation scenarios. It also provides a consistent debugging experience like any other .Net application. Developing a BCS model is much simpler compared with the BDC application model development in the previous version as it provides a GUI for developing a model.

BCS provides a great platform for building rich composite business applications that span SharePoint and multiple Office client applications. It considerably reduces the overhead of developing and maintaining custom applications, providing information workers the flexibility and ease of use of doing their day-to-day operations using Office/SharePoint UIs without understanding the complexity of external source systems.

Using SharePoint Designer to Create and Use External Lists in SharePoint 2010

The example outlined in the following sections demonstrates the use of SharePoint Designer as a tool for creating an external content type and using it in an external list to work with data in an external system such as SQL Server. As mentioned in the introduction of BCS, an external list is a list based on data from an external system outside of SharePoint, such as a customer relationship management (CRM) system or relational database. Here are the steps for creating and using external lists.

Create an External Content Type

The first step is to create your external content type. To do so, perform these steps:

Create an External List

Next, we'll create an external list using the external content type created in the previous section. To do so, perform the following steps:

Syncing an External List to a Computer

Next you sync the external list to a computer. These steps illustrate the process:

This example demonstrates a no-code experience of creating an external content type using SharePoint Designer 2010. It also showcased the ability to create an external list using an external content type and synchronizing the data from an external system with SharePoint Workspace.

In a nutshell, BCS provides seamless integration of external data with familiar Microsoft Office and Microsoft SharePoint interfaces thereby enhancing the capability of Microsoft SharePoint 2010 and Microsoft Office 2010 as platforms for composite application.