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:

• Integration challenges among various source systems
Yet another unfamiliar UI, which introduces additional training costs
• The need to design, develop, and maintain the core infrastructure for such applications
• Creation of a seamless transition between client, web, and offline scenarios
• Non-Office based interfaces, which add one more layer of usability challenges
• Upgrading the applications across a large number of client deployments
• Responsibility for building SOA-type infrastructures to expose the data, UI, and infrastructure for each application. This is a very serialized and inefficient development process.

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:

• Seamless integration of external data with more familiar end-user applications such as SharePoint and Office
• Great off-line support for external data
• Rich and improved development support using Visual Studio 2010 and SharePoint Designer 2010
• Support for collaborative development. For example, the IT organization can focus on data connectivity whereas Power Users and developers can focus on developing applications.
• Ease of integration with a variety of external systems using four different connectivity options
• Support for one-click deployment for installation and upgrade of 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:

1. Open a SharePoint 2010 site collection using SharePoint Designer. You can do this in either of two ways: You can browse to a SharePoint 2010 site, click Site Action, and choose Edit Site in Designer, or open SharePoint Designer and open the site you want to work with.
2. To create an external content type, click the Entities button on the left navigation pane for the site in the Designer. Click external content type. Change the Display Name and Programmatic Name to your external content type name (e.g., Product). Click the Operations Designer View as Figure 2 shows.
3. Add an external system connection by clicking on the Add Connection button. This pops up the four data source types that you can use to connect to the external system. Select SQL Server as the data source type.
4. Provide the details of the SQL Server (external system) connection. In this example, we're connecting to an AdventureWorks database, as Figure 3 shows.
5. SharePoint Designer allows you to create an external content type on a database table. Right-click a table (e.g., Product). A menu opens that will allow you to create an external content type associated with the table and add various operations to it. Choose Create All Basic Operations, as Web Figure 1 (www.windowsitpro.com, InstantDoc ID 103416) shows. A wizard opens that will walk you through identifying the parameters for the operations based on the fields of the table selected.
6. Enter the basic settings such as Operation Name Prefix and Operation Display Name Prefix and click Next.
7. Here you configure the parameters for the external content type. By default, all columns are selected, as Figure 4 shows. Designer will give an error message if you clear a check box for a column that is a required field in the database. Also, at least one field must be selected to show in the Picker control or you'll get a warning.
8. For the ProductID field, check the Show in Picker option as shown in Figure 4.
9. The wizard then generates the operations for the Product external content type, as Figure 5 shows.

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:

1. Click the Create List and Forms button.
2. Select Create New External List and provide a name for the external list (e.g., AdventureWorks Product). As shown in Figure 6, an external list needs a Read Item Operation associated with it. This operation is used for reading an external content type.

Syncing an External List to a Computer

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

1. Browse to the SharePoint site where the external list was created.
2. Go to Site Actions and select View all Site Content. This will bring up the All Site Content page, which will show the external list created in the previous section.
3. Click the AdventureWorks Product External List as highlighted in Web Figure 2.
4. Figure 7 shows the data from the Product table in the AdventureWorks SQL Server database in an external list. As you can see, the interface is similar to a SharePoint list, but the data is being surfaced from an external system.
5. To sync this list to your local computer, click the Sync to Computer button. Click OK. The underlying BCS infrastructure packages the solution and makes it available as a click-once deployment
6. Click Install in the Microsoft Office Customization Installer dialog box. This will install the BCS solution generated for AdventureWorks Product external list on the client machine.
7. Web Figure 3 shows the AdventureWorks Product external list in SharePoint Workspace. The end user can perform edit or delete operations in an online as well as offline mode. Once the user is back online, BCS will sync the data with the external system.

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.