Consuming External Data Using SharePoint Server 2010 Business Connectivity Services and an Excel 2010 Add-In
Summary: Learn how to use Microsoft Business Connectivity Services (BCS) in Microsoft SharePoint Server 2010 to access and update external data by using Microsoft Excel 2010 as a client.
Applies to: Business Connectivity Services | Excel 2010 | Open XML | SharePoint Designer 2010 | SharePoint Foundation 2010 | SharePoint Online | SharePoint Server 2010 | Visual Studio
Provided by: Tajeshwar Singh (read his blog), MCM for SharePoint, Microsoft Services
Contents
Overview of Using Business Connectivity Services and the Excel 2010 Add-in
Setting Up the Server Environment
Creating External Content Types Using SharePoint Designer
Creating the Excel Add-in
Packaging the Solution
Conclusion
Additional Resources
.jpg "Click to get code")
Download code: Business Connectivity Services Excel 2010 Add-in (SalesOrderAddIn1.7.zip)
Overview of Using Business Connectivity Services and the Excel 2010 Add-in
The Microsoft Business Connectivity Services (BCS) feature in Microsoft SharePoint Server 2010 provides many ways to consume data from an external system, and use it when offline or connected. You can create add-ins, task panes, and other Microsoft Office extensibility features that can help you to interact with external data by using the user-friendly Office client applications.
This article describes a scenario in which the AdventureWorks sample database that is hosted in Microsoft SQL Server 2008 is used as an external system. You will create an external content type by using Microsoft SharePoint Designer 2010. Then, you will create a Microsoft Excel 2010 add-in to interact with an external system to read external items, navigate associations, and update external items by using the Business Connectivity Services client object model. The Excel add-in is deployed to the client by using a ClickOnce package. The metadata model will be deployed separately to the client by using a ClickOnce package created with the BCS Solution Packaging Tool.
Setting Up the Server Environment
To run this scenario, you must install the following software in your server environment, and you must create the AdventureWorks database.
Install the Required Software
For this scenario, install the following software:
Microsoft SQL Server 2008
Microsoft SharePoint Server 2010
Microsoft SharePoint Designer 2010
Microsoft Excel 2010
Microsoft Visual Studio 2010
Create the AdventureWorks Database
The AdventureWorks sample database, available on Codeplex, is used in this scenario to represent the external system. You can download the x64 version of the AdventureWorks sample database from AdventureWorks 2008R2 RTM.
Run the Setup program to create the database. You will use the SalesOrder table and the SalesOrderDetail table to model the parent and child external content types in the relationship.
Creating External Content Types Using SharePoint Designer
SharePoint Designer 2010 includes functionality that you can use to design the external content types visually. Based on the options that are selected in the user interface (UI), SharePoint Designer generates XML metadata in the background. By using the ECT Designer in SharePoint Designer, you can discover the database and point to the table, view, or stored procedure that will perform the operations. Then, return the required data and use it to create the external content type, without writing any code or XML. Use the following procedures to create the external content type.
Define the General External Content Type Settings
Begin by defining settings for the external content type.
To define the general external content type settings
Create a site collection that you will use for connecting through SharePoint Designer and for creating the external content types.
On the client computer, start SharePoint Designer, and then open the site that you created.
To create an external content type, go to Site Objects on the left navigation pane, and then click External Content Types.
On the Server ribbon, in the New group, click External Content Type. To create a new external content type, set the properties as shown in Table 1.
Table 1. Properties for creating the external content type
Property
Value
Name
SalesOrderHeader
Display Name
Sales Order Header
Namespace
http://intranet.contoso.com
Version
1.0.0.0
Office Item Type
Generic List
Offline Sync for External List
Enabled
The Office Item Type that is selected determines the Microsoft Outlook behavior that you want to attach to the external content type. The Offline Sync for external list field determines whether lists based on this external content type can be taken offline in Outlook or Microsoft SharePoint Workspace 2010. Accept the default Enabled setting.
Click the link Click here to discover external data sources and define operations. This opens the windows in which to define the connection to the AdventureWorks database and operations for the external content type.
Click Add Connection in the External Data Source section, and for Data Source Type, select SQL Server. This opens the SQL Server connection properties dialog box. Set the properties as shown in Table 2.
Table 2. SQL Server connection properties
Property
Value
Database Server
Insert name of your server here (for example, ContosoSrv)
Database Name
AdventureWorks2008
Name
AdventureWorks
Note
The AdventureWorks database was named Adventureworks2008 in this scenario. Users can use the same name, but may have to change the connection name in the associated model.
Select Connect with User's Identity, and then click OK to create the connection. The AdventureWorks connection is displayed in the Data Explorer section.
Define Operations for the SalesOrderHeader External Content Type
After the connection is set up, you define operations on the SalesOrder table, which exposes SalesOrder header-level information.
To define operations for the SalesOrderHeader external content type
Right-click the SalesOrder table to see options for creating methods. SharePoint Designer provides options to create all common operations that are available in Business Connectivity Services, or it can create specific operations.
The following two operations are required to fetch data from the external system and show them in a list:
Read List method, which gets the list of records and works as the Finder method.
Read Item method, which gets data for the specific external items and works as the SpecificFinder method.
To create default operations, click Create All Operations. In the wizard that opens, in Filter Parameters Configuration, define a filter of type Limit with a default value 20 on column SalesOrderId. Because we want to limit the number of records returned, this gets the top 20 rows from the external system.
Go to the Read Item operation, and then rename it SalesOrderHeaderReadItem.
Go to the Read List operation, and then rename it SalesOrderHeaderReadList.
Save the external content type. Figure 1 shows SalesOrderHeader properties and operations.
Figure 1. SalesOrderHeader properties and operations
.jpg "SalesOrderHeader properties and operations")
Define Operations for the SalesOrderLine External Content Type
In this step, you define a Read Item method, Read List method, Association method, and Updater method to update the SalesOrderDetails table.
To define operations for the SalesOrderLine external content type
In the New group on the ribbon, click External Content Type. In the window that opens, create an external content type. Set the properties as shown in Table 3.
Table 3. Properties to set for the external content type
Property
Value
Name
SalesOrderLine
Display Name
Sales Order Line
Namespace
http://intranet.contoso.com
Version
1.0.0.0
Office Item Type
Generic List
Offline Sync for External List
Enabled
In the Views group on the ribbon, click Operations Designer View.
To see options for creating methods, right-click the SalesOrderDetail table.
Click Create New Read Item Operation. In the Operation Name field, use SalesOrderDetailReadItem. Click Next.
In Input Parameters Configuration, click the SalesOrderId data source element and clear the Map to Identifier property. You will be using the SalesOrderDetailId column to uniquely identify the SalesOrderLine, and so are removing this as the identifier. Click Next.
On the Return Parameters page, repeat the actions in step 5. Click Finish.
Right-click the SalesOrderDetail table, and then click the Create New Read List operation. In the Operation Name field, use SalesOrderDetailReadList. Click Next.
In Filter Parameters Configuration, define a filter of type Limit with a default value of 100 on the SalesOrderId column to limit the number of records returned. Click Next, and then click Finish to create the operation.
Right-click the SalesOrderDetail table, and then click New Update Operation. In the Operation Name field, use SalesOrderDetailUpdateItem. Click Next, and then click Finish to create the operation.
Right-click the SalesOrderDetail table, and then click New Association. In the Operation Name field, use SalesOrderDetailNavigateAssociation.
Click Browse, click the SalesOrderHeader external content type, and then click Next.
In Input Parameters configuration, click the SalesOrderId element, and then select its Map to Identifier property. Click Finish to create the operation. Figure 2 shows the SalesOrderLine properties and the operations that are defined on it.
Figure 2. SalesOrderLine properties and defined operations
.jpg "SalesOrderLine properties and defined operations")
Creating the Excel Add-in
Now you can create the Excel add-in by using the following procedures.
Create the Ribbon and Implement Event Handlers
To create the ribbon and implement event handlers
Open Visual Studio 2010, click New Project, expand the Office node, click 2010, and then click Excel 2010 Add-InTemplate. Name the template SalesOrderAddIn. Ensure that .NET 3.5 is selected as the target platform.
In Solution Explorer, right-click the SalesOrderAddin project, and then click Add New Item. Select Ribbon (Visual Designer) as the template to use to create the ribbon. Name the file SalesOrderRibbon.cs.
Set the Label property of the RibbonGroup to Sales Orders.
Drag a Box control, located in the Office Ribbon Controls category, from the Toolbox on the Ribbon group. Set the BoxStyle to Vertical.
Drag a ComboBox control from the Box control, and then set properties as shown in Table 4.
Table 4. Properties to set for the ComboBox control
Property
Value
Name
cmbSalesOrderNumbers
Label
Order Number:
Specify UpdateSalesOrderDetails as the name for the TextChanged event handler for the ComboBox control that you added earlier.
Drag an EditBox control from the Box control, and then set properties as shown in Table 5.
Table 5. Properties to set for the EditBox control
Property
Value
Name
txtOrderAmount
Label
Order Amount
ShowLabel
True
Drag a Separator control from the Box control to separate the control that you will be using in the next step.
Drag a RibbonButton control and set its properties as shown in Table 6.
Table 6. Properties to set for RibbonButton control
Property
Value
Name
UpdateLines
ControlSize
RibbonControlSizeLarge
Label
Update Discount
OfficeImageId
FileSave
Specify UpdateLineItems as the name for the Click event handler for the button that you added in the previous step.
Include the SalesOrderRibbon.cs file in the project that you created. (This file is provided in the project in Business Connectivity Services Excel 2010 Add-in).The code in this class has the following important members:
salesOrderTable and salesOrderLineTable variables: These class-level variables hold the data returned by executing Business Connectivity Services calls against the external system.
SalesOrderRibbon_Load method: This method is executed the first time the ribbon loads, and creates the table structures by reading the metadata for Finder View and Association View for the external content types SalesOrderHeader and SalesOrderLine. Finder View is the set of fields that are returned by the Finder operation on Entity. Association View is the set of fields that are returned by the associated external content type default Finder operation.
The method then gets the SalesOrderHeader Entities and populates the ComboBox control with SalesOrder numbers.
UpdateSalesOrderDetails method: This method is executed when the value in the ComboBox control changes. The logic gets the line items that correspond to the selected SalesOrderHeader external content type by using the Association method. The Sheet1 worksheet in Microsoft Excel is refreshed and SalesOrderLine External Items that are returned are shown for the selected SalesOrderHeader.
UpdateLineItems method: This method is executed when the user selects the option to save the changes in SalesOrder lines by using the Update Discount button on the ribbon. It iterates over changed line items for SalesOrder that are selected in the combo box and runs an Updater method on the SalesOrderLine external content type by using the changed values in the sheet.
BindSalesOrderLines method: This method implements the functionality of binding the Excel worksheet to the data table by using the ListObject control. ListObject is a list that exposes events and that can be bound to data. You can program against it directly without traversing the Excel object model.
Implement BCS Object Model Code
Include the SalesDataManager.cs class in the project. (This file is available in the project in Business Connectivity Services Excel 2010 Add-in). This class implements the functionality to get data from the external system or update the SalesOrderLine by using the Business Connectivity Services client object model. The following are the core functions that implement the functionality.
InitializeMetadataStore Method
InitializeMetadataStore initializes the RemoteSharedFileBackedMetadataCatalog and gets the external content type objects for SalesOrderHeader and SalesOrderLine. It is a store for metadata that is shared among Microsoft Office client applications and that provides an interface to the external content type objects that are available in the Business Connectivity Services client cache. This is shown in the following code example.
private void InitializeMetadataStore()
{
// Open the shared metadatacatalog cache on the client to
// read line-of business information. This class represents the Office
// client shared storage for BCS metadata. After it is on the client,
// this metadata can be accessed from any Office application.
catalog =
new RemoteSharedFileBackedMetadataCatalog();
if (catalog != null)
{
// Get the SalesOrderHeader External Item by using
// namespace and name.
entitySalesOrderHeader = catalog.GetEntity(
SalesOrderHeaderNamespace,
SalesOrderHeader);
// Get the SalesOrderLine External Item by using
// namespace and name.
entitySalesOrderLine = catalog.GetEntity(
SalesOrderHeaderNamespace,
SalesOrderLine);
// Get the handle to LobSystem.
lobInstance =
catalog.GetLobSystem(SalesOrderLobSystemName).
GetLobSystemInstances()[SalesOrderLobSystemName];
}
}
CreateSalesOrderHeaderTableStructure Method
CreateSalesOrderHeaderTableStructure creates the structure for the data table used to store External Items for SalesOrderHeader. GetFinderView returns the View definition for the Finder method of the SalesOrderHeader external content type. Fields in the view are used to create the data columns. This is shown in the following code example.
public void CreateSalesOrderHeaderTableStructure()
{
if ((entitySalesOrderHeader != null)
&& (SalesOrderTable == null))
{
// Get the View that is associated with Finder by using name.
IView viewSalesOrderHeader = entitySalesOrderHeader.
GetFinderView(SalesOrderHeaderFinderName);
fieldsSalesOrderHeader = viewSalesOrderHeader.Fields;
// Create the table definition.
SalesOrderTable = new DataTable();
foreach (IField field in fieldsSalesOrderHeader)
{
SalesOrderTable.Columns.Add(field.Name,
field.TypeDescriptor.TypeName.GetType());
}
}
}
CreateSalesOrderLineTableStructure Method
CreateSalesOrderLineTableStructure creates the structure for the data table that is used to store External Items for SalesOrderLine. GetAssociationView returns the view for the parameters that are returned by the Association method that is used for navigating associations from SalesOrderHeader to SalesOrderLine external content types. This is shown in the following code example.
public void CreateSalesOrderLineTableStructure()
{
if ((entitySalesOrderHeader != null)
&& (SalesOrderLineTable == null))
{
// Get associations where SalesOrderHeader is the source.
INamedAssociationDictionary dict =
entitySalesOrderHeader.GetSourceAssociations();
IAssociation associationSalesOrderLine =
dict[SalesOrderAssociationName];
// Get the SalesOrderLine Entity.
IEntity destinationEntity =
associationSalesOrderLine.GetDestination();
// Get the fields that are returned by the Associated View.
IView viewSalesOrderLine =
destinationEntity.GetAssociationView(associationSalesOrderLine);
fieldsSalesOrderLine = viewSalesOrderLine.Fields;
// Create table structure.
SalesOrderLineTable = new DataTable();
foreach (IField field in fieldsSalesOrderLine)
{
SalesOrderLineTable.Columns.Add(field.Name,
field.Name.GetType());
}
}
}
GetSalesOrderHeaderItems Method
GetSalesOrderHeaderItems gets the SalesOrderHeader External Items that are based on the default finder and populates the data table. IEntity.FindFiltered is used to get these records. OperationMode.Online determines whether records are fetched online or offline from the local cache. In this scenario, you are always getting the records from the external system and not from the local cache, as shown in the following code example.
public DataTable GetSalesOrderHeaderItems()
{
if ((entitySalesOrderHeader != null)
&& (SalesOrderTable != null)
&& (lobInstance != null))
{
// Get the default filters.
IFilterCollection filters =
entitySalesOrderHeader.GetDefaultFinderFilters();
// Execute the FindFiltered method online.
IEntityInstanceEnumerator enumerator =
entitySalesOrderHeader.FindFiltered(
filters,
SalesOrderHeaderFinderName,
lobInstance,
OperationMode.Online);
SalesOrderTable =null;
SalesOrderTable = catalog.Helper.CreateDataTable(enumerator);
return SalesOrderTable;
}
GetSalesOrderLineItems Method
GetSalesOrderLineItems gets the SalesOrderLine External Items corresponding to a specific SalesOrderId. GetSourceAssociations finds the associations where SalesOrderHeader is the source Entity. The association is used in the GetAssociatedInstances method to get the SalesOrderLine External Items that are the target of the association relationship. This operation is also performed by using OperationMode as online, as shown in the following code example.
public DataTable GetSalesOrderLineItems(int SalesOrderId)
{
Identity salesOrderIdentity = new Identity(SalesOrderId);
// Get the specific SalesOrderHeader by using FindSpecific.
IEntityInstance salesOrderinstance =
entitySalesOrderHeader.FindSpecific(
salesOrderIdentity,
SalesOrderSpecificFinderName,
lobInstance,
OperationMode.Online);
// Get the association.
IAssociation associationSalesOrderDetail =
entitySalesOrderHeader.GetSourceAssociations()
[SalesOrderAssociationName];
// Get the associated SalesOrderLine instances.
IEntityInstanceEnumerator enumerator =
salesOrderinstance.GetAssociatedInstances(
associationSalesOrderDetail,
OperationMode.Online);
// Populate the SalesOrderLine table.
SalesOrderLineTable = null;
// Clear the dirty rows, if any.
changedSalesLineRows.Clear();
SalesOrderLineTable = catalog.Helper.CreateDataTable(enumerator,true);
if (SalesOrderLineTable != null)
{
SalesOrderLineTable.RowChanged +=
new DataRowChangeEventHandler(SalesOrderLineTable_RowChanged);
}
return SalesOrderLineTable;
}
return SalesOrderLineTable;
}
UpdateSalesOrderLineItems Method
UpdateSalesOrderLineItems updates the SalesOrderLine Entities in the external system; updaterMethod.GetMethod().GetParameters() returns the parameter definitions to pass to the Updater method. For each SalesOrderLine, an Execute method is called to update the external content type in the external system. The method call is passed the parameters that are required for updating. This is shown in the following code example.
public void UpdateSalesOrderLineItems(DataRowCollection salesLineRows)
{
// Get the updater method.
IMethodInstance updaterMethod =
entitySalesOrderLine.GetMethodInstance("Update", MethodInstanceType.Updater);
// Get the parameters to be passed to the Updater.
IParameterCollection paramsDef = updaterMethod.GetMethod().GetParameters();
// Parameter collection.
object[] paramValues = new object[paramsDef.Count];
int counter = 0;
string colName = null;
string colType = null;
DataRow row = null;
// Loop through and call Updater on each SalesOrderLine.
IEnumerator enumerator = salesLineRows.GetEnumerator();
while (enumerator.MoveNext())
{
row = (DataRow)enumerator.Current;
while (counter < paramsDef.Count)
{
colName = paramsDef[counter].GetRootTypeDescriptor().Name;
colType = paramsDef[counter].GetRootTypeDescriptor().TypeName;
switch (paramsDef[counter].GetRootTypeDescriptor().TypeName)
{
// Converting in format of Guid.
case "System.Guid":
paramValues[counter] =
new System.Guid(row[colName].ToString());
break;
default:
paramValues[counter] =
row[colName];
break;
}
counter++;
}
// Call Updater.
entitySalesOrderLine.Execute(
updaterMethod,
lobInstance,
ref paramValues);
counter = 0;
}
return;
}
Packaging the Solution
SharePoint Server 2010 provides two mechanisms that you can use to install the metadata and data to the client computer, as follows:
Create an external list on the SharePoint Server computer based on the external content type, and synchronize the list to the client computer by clicking the Sync to SharePoint Workspace button on the ribbon of the external list. This gets the metadata from the server running SharePoint and data from the external system and makes it available in the Business Connectivity Services client cache and Microsoft SharePoint Workspace 2010.
Create a DataSolution for the Excel add-in. By using the BCS Solution PackagingTool, you can create a Microsoft Visual Studio ClickOnce package that contains the BDC model and subscription files for the external content type. When deployed to the client, it reads the client cache subscription rules and imports metadata into the client cache. After it is on the client, this can be used to execute Business Connectivity Services client object model calls to fetch and update data in the external system.
The Excel add-in is packaged as a separate SharePoint development tools in Visual Studio 2010 package by using the support provided in Visual Studio.
Create the DataSolutionPackage
The BCS Solution Packaging Tool is used to create ClickOnce packages for deploying the metadata and subscription files. In this scenario, you use the tool to package the metadata and populate the cache. The package is referred to as a data solution because the data is not shown in an Office application. The Excel add-in provides the functionality to show this data in the UI.
To create the solution package
Create a folder, and then name it DataSolutionPackage. (Or, give the folder a name that you choose.)
Create a subscription file in the DataSolutionPackage folder, and then name it SalesOrderHeaderSubscription.xml. (Or, give the file a name that you choose, but the name must end with the string subscription.) For more information about cache subscription, see What Is a Cache Subscription?
This cache subscription file is used to create the subscription for the SalesOrderHeader external content type with caching enabled. The IsCached property determines whether external content type data is cached on the client, as shown in the following example. You can use the BCS Artifact Generator Tool to create the subscription file based on the BDC model.
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <Subscription LobSystemInstanceName="AdventureWorks" EntityNamespace="http://intranet.contoso.com" EntityName="SalesOrderHeader" Name="Sales Order Header" View="SalesOrderHeaderReadItem" RefreshIntervalInMinutes="10" xmlns= "https://schemas.microsoft.com/office/2006/03/BusinessDataCatalog" > <Queries> <Query Name="QuerySalesOrderHeader" IsCached="true" RefreshIntervalInMinutes="10" MethodInstanceName="SalesOrderHeaderReadList" Enabled="true"></Query> </Queries> </Subscription>In the DataSolutionPackage folder, create a subscription file and name it SalesOrderLineSubscription.xml. (Or, give the file a name that you choose, but the name must end with the string subscription.) This cache subscription file is used to create a subscription for the SalesOrderLine external content type with caching enabled, as shown in the following example.
You can use the BCS Artifact Generator Tool to create the subscription file based on the BDC model.
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <Subscription LobSystemInstanceName="AdventureWorks" EntityNamespace="http://intranet.contoso.com" EntityName="SalesOrderLine" Name="Sales Order Line" View="SalesOrderDetailReadItem" RefreshIntervalInMinutes="10" xmlns= "https://schemas.microsoft.com/office/2006/03/BusinessDataCatalog"> <Queries> <Query Name="QuerySalesOrderLine" IsCached="true" RefreshIntervalInMinutes="10" MethodInstanceName="SalesOrderDetailReadList" Enabled="false"></Query> </Queries> </Subscription>Copy the metadata.xml file that is created by using Microsoft SharePoint Designer, or the file that is available in the source code in Business Connectivity Services Add-in, to the DataSolutionPackage folder. If you use the metadata.xml file in the Business Connectivity Services Add-in, make changes to the database server name and account names to which permissions must be granted.
Start the BCS Solution Packaging Tool, and then specify property names as shown in Table 7.
Table 7. Property names to specify after starting BCS Solution Packaging Tool
Field
Value
Solution Name
SalesOrderSolution
Source Folder Path
Path to DataSolutionPackage folder
Output Folder Path
Path to folder where package should be generated
Solution Version
1.0.0.0
Solution Type
Data Solution for Office Add-In
In this scenario, you use the autogenerated certificate for signing the package. For production environments, a commercial certificate is recommended. Use the Package button to generate the package.
Note
For information about certificates and how to fix the issue of the security prompt, see How Can I Get Rid of the "Publisher cannot be verified" Message When Taking External Lists Offline?
To deploy the solution, click the Deploy button. If the target client is a different computer, the generated files in the Output folder can be copied to the target computer. The data solution will be deployed by double-clicking the *.vsto file on the client.
Create the Excel Add-in ClickOnce Package
Use the Publish Wizard on the Visual Studio Project context menu to generate the ClickOnce package for the Excel add-in.
To create the Excel add-in ClickOnce package
Start Excel. The Sales Orders section should be displayed in the ribbon on the Add-Ins tab, as shown in Figure 3. This confirms that the solution has installed successfully on the client.
Figure 3. Sales Orders section on Add-Ins tab
.jpg "Sales Orders section on Add-Ins tab")
Populate the ComboBox control to show the SalesOrder numbers by executing the Finder method on the SalesOrderHeader external content type. The text box shows the SubTotal amount for the order that is selcted in the combo box. Sheet1 shows the SalesOrderLine items for the selected SalesOrder in the combo box. You can update the line item UnitPriceDiscount column with any value between 0.00 and 1.00, with 1 indicating 100%.
After making changes, click the Update UnitPriceDiscount button on the ribbon to save the new values. This executes the updater and updates SalesOrderLine in the external system. Internally, SQL Server triggers are fired in the external system, which updates the line item UnitPrice and the SalesOrderHeaderSubTotal column. After updating, the add-in gets the new values and refreshes the Excel sheet and value in the text box with the new order Subtotal.
Conclusion
This article describes the Microsoft Business Connectivity Services (BCS) feature in Microsoft SharePoint Server 2010. This feature provides many ways to consume data from an external system and use it when offline or connected. You can create add-ins, task panes, and other Microsoft Office extensibility features that can help users to interact with the external data through the user-friendly Microsoft Office client applications.
Additional Resources
For more information, see the following resources: