Reference Implementation: External Data Models

  • Article

Microsoft® SharePoint® 2010 includes many new and enhanced areas of functionality for modeling and accessing external data, including external lists, external content types, business data Web Parts, and tooling in SharePoint Designer 2010 and Visual Studio® 2010. The External Data Models Reference Implementation uses a business integration scenario to illustrate how you can work with these areas of functionality in real-world applications.

The implementation highlights techniques and best practices in the following areas:

  • Creating Business Data Connectivity (BDC) models for Microsoft SQL Server® databases and external services.
  • Customizing BDC models to support many-to-many relationships and non-integer primary keys.
  • Using stored procedures and other custom logic to navigate foreign-keyless relationships in a BDC model.
  • Creating .NET connectivity assemblies to flatten complex data from an external service.
  • Using external lists and built-in business data Web Parts to interact with BDC models.
  • Developing custom code to interact with external data by using the BDC object model.

This solution uses the same scenario as the SharePoint List Data Models reference implementation. Together, these reference implementations are designed to help you compare and contrast the use of SharePoint lists and external databases as the data platform for moderately complex, data-driven SharePoint applications.

Solution Scenario

In this example, suppose you are providing consultancy services to a pharmaceutical company named Contoso Inc. Contoso has production plants in several locations that operate an extensive range of manufacturing machinery. Every machine requires parts to be replaced on a regular basis due to wear and tear incurred in the course of everyday operations. Different types of machines consume a wide variety of parts, which means the manufacturing team at Contoso must manage large amounts of information relating to machines, parts, and suppliers.

Currently, the Springfield production plant uses a desktop application built on FoxPro to track part inventories for machine maintenance and repair. This application is used by the warehouse manager to track how many parts are in inventory and where the parts are located. The data model for the parts management system is relatively complex, and includes one-to-many relationships, many-to-many relationships, and foreign key constraints that restrict the deletion of rows that have dependencies in other tables. Whenever employees need a spare part for maintenance or repair, they must contact the warehouse manager to see if the part is available, slowing down both the warehouse manager and the employees. The user interface for the current parts management system allows the warehouse manager to search for items using wildcard filters, and allows him to create, update, and delete items. The user can also browse associated data; for example, users can browse the parts associated with a specified machine.

The plant also uses a proprietary supplier management system to manage suppliers and their contact details. The manufacturing team would like to be able to view and manage key aspects of this information on the Contoso SharePoint 2010 intranet portal. In particular, they would like to be able to:

  • Search for machines and view a list of parts associated with the specified machine.
  • Search for parts and view a list of machines that require the specified part.
  • Search for suppliers by name and view all contacts at the specified supplier.

To meet these requirements, you implement a SharePoint solution that uses a wide variety of business connectivity services (BCS) functionality, together with a SQL Server database for storing and managing the parts and inventory locations data. This solution includes:

  • A business data connectivity (BDC) model that maps to the SQL Server database for the parts management system.
  • A .NET connectivity assembly to connect to the supplier management system, together with a BDC model that maps to the .NET connectivity assembly.
  • Parts management and supplier management pages that use a combination of external lists, built-in business data Web Parts, and custom-coded Web Parts to allow users to interact with the external data.

Note

The project template for creating a .NET connectivity assembly in Visual Studio 2010 is called Business Data Connectivity Model. When you create a .NET connectivity assembly, Visual Studio provides support both for developing the connectivity assembly code and creating the declarative model that is deployed to the BDC. SharePoint Designer can be used to create a declarative BDC model against a database or a Web service, where there is no .NET connectivity assembly involved. Visual Studio 2010 does not provide interactive support for creating or editing a declarative BDC model.

Deploying the External Data Models RI

The External Data Models RI includes an automated installation script that creates various site collections, deploys the RI components, and adds sample data. After running the installation script, browse to the new PartsManagement site collection at http://<Hostname>/sites/PartsManagement. You can open and run the project in Visual Studio, but this does not create a site collection or add sample data. To see the system fully functioning, you must run the installation script. The following table summarizes the steps to get started with the External Data Models RI.

Question

Answer

Where can I find the External Data Models RI?

<install location>\Source\DataModels\DataModels.ExternalData

What is the name of the solution file?

DataModels.ExternalData.sln

What are the system requirements?

SharePoint Server 2010 Enterprise Edition (required for Business Data Web Parts)

What preconditions are required for installation?
  • You must be a member of SharePoint Farm Admin.
  • You must be a member of the Windows admin group.
  • SharePoint must be installed at http://<Hostname:80>. If you want to install to a different location, you can edit these settings in the Settings.xml file located in the Setup directory for the solution.
  • SharePoint 2010 Administration service must be running. By default, this service is set to a manual start. To start the service, click Start on the taskbar, point to Administrative Tools, click Services, double-click SharePoint 2010 Administration service, and then click Start.

How Do I Install the External Data Models RI?

Follow the instructions in the readme file located in the project folder.

What is the default installation location?

http://<Hostname>/sites/ PartsManagement

(This location can be altered by changing the Settings.xml file in the Setup directory.)

How do I download the External Data Models RI?

The External Data Models RI is included in the download Developing Applications for SharePoint 2010.

Note

The reference implementation uses a SQL Server database. The installation script assumes that there is a SQL Server instance named SharePoint. If you do not have a SQL Server Data instance named SharePoint, or you do not want to install the test data in that database, you need to update the installation files. Edit the database instance name in PartsManagement_SqlInstall.bat and PartsManagement_Sqluninstall.bat, replacing SharePoint with your database instance name. Open the PartsManagement.bdcm file (located in the PartsManagement subdirectory) in Visual Studio, and replace the database name in the RdbConnection Data Source property, as shown by the following code.
<Property Name="RdbConnection Data Source" Type="System.String">.\SHAREPOINT</Property>