Consuming External data using SharePoint 2010 Business Connectivity Services(BCS) and Office Excel 2010 Add-In
In this walkthrough you will learn how to use Sharepoint Server 2010 Business Connectivity Services(BCS) feature to access external business data using Microsoft Excel 2010 as a client. This simple step-by-step will help you understand how to setup a BCS External list and then interact with it using browser or new OM available via Office clients. The code is not production ready and some changes may be required. I wrote this as a proof of concept while learning the client OM and have started liking this feature. I was talking to Arpan sometime back and he was very excited about this feature and his enthusiasm forced me to explore it. I am sure many products inside MS and from other vendors will start using the client OM support including caching and clubbed with Secure credentials storage on the client to make compelling applications.
Introduction
BCS is the new version of MOSS 2007 Business Data Catalog functionality. New features have been added which helps to interact with external data using rich interface provided by Microsoft Office 2010 client applications like OutLook, Access, SharePoint Workspace and Excel. This article demonstrates the client object model of BCS to consume External Data using Excel 2010 Add-In. After reading this, readers will understand how to set up metadata on the server to consume external data and show in Excel 2010 using an add-in.
Applies to: Microsoft SharePoint Server 2010 Beta 1, Microsoft Office Excel 2010 Beta 1
Contents
Create Model using SharePoint Designer
Define Operations on External System
Create External List based on External Content Type
Synchronize List using SharePoint Workspace 2010
Overview
In this scenario, Adventure Works (AW) database hosted in SQL Server 2008 is used as an External System. An application definition is created using SharePoint Designer 2010(SPD) which include features to create External Content Type (ECT) metadata model. Function calls to the backend system are modeled as an xml and imported into Business Data Connectivity Service metadata store. A SharePoint external list is created based on ECT defined in application metadata. This list is synchronized to the client machine using SharePoint Workspace 2010. Workspace provides features to connect to a list and take content offline. On client side, Excel add-in is created which uses the BCS client object model to execute methods to get data from external system or read from cache available on client.
Set up Server Environment
Software Requirements
The list of software required to run this scenario is as follows:
1. Microsoft SQL Server 2008 x64
2. Microsoft SharePoint Server 2010 Beta1
3. Microsoft SharePoint Designer 2010
4. Microsoft SharePoint Workspace 2010
5. Microsoft Office Excel 2010
6. Microsoft Visual Studio 2010 Beta1
Create AdventureWorks Database
AdventureWorks sample database available on Codeplex site is used to represent the external system. x64 version can be downloaded from link below. Run the setup to create the database.
https://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407
Create Team site to host External List
1. Create a web application with name intranet.contoso.com to host the site. It can be any URL based on your environment. The steps will have to be altered based on URL you choose for the site.
2. Create a Site Collection based on Team Site template.
Create Model using SharePoint Designer
SPD includes functionality to design the application definition model visually. Based on the options selected on UI, it generates the xml metadata in the background. Using ECT Designer in SPD you can discover database, point to the table, view, or stored procedure that will perform the operations, and then return the required data and use it to create external content type without writing any code or XML. Follow the steps below to create the ECT:
Define the general ECT settings
1. On the client computer, start SharePoint Designer, and open the site you created above.
2. To create an ECT, start by clicking Entities.
3. On Ribbon, click the External Content Type button, as shown in figure 1.
Fig 1 - External Content Type List
4. On New External Content Type page, specify the Name, Display Name, and the Office Item Type, as shown in the figure 2.
Fig 2 - External Content Type Properties
The Office Item Type selected determines the Outlook behavior you want to attach to the ECT. For e.g., if the behavior selected is of type contact, the record will show up as a contact while viewed in Outlook. The Offline Sync for external list field determines if lists based on this ECT can be taken offline in Microsoft Outlook or SharePoint Workspace. Accept the default Enabled setting.
5. Click on the link Click here to discover external data sources and define operations. This will open up the windows to define the connection to AW database and operations for the ECT.
6. Click Add Connection under External Data Source section and choose Data Source Type as SQL Server. This brings up the SQL connection properties dialog. In this we are connecting using SQL Server provider to get data. Provide connection details as shown in Figure 3. Since the environment on which the sample was created had WFE and database on same machine, we used localhost as server name. Press OK to create the connection. You will see AdventureWorks2008DB connection under the Data Explorer section.
Fig 3 - SQL Server Connection Details
Define Operations on External System
Once the connection is setup, operations are defined on the view which exposes sales by fiscal year for each user. You can explore the connection created in previous step. It shows database artifacts like tables, views etc. Create operations as follows:
1. We will query sales data using view vSalesPersonSalesByFiscalYears. Right click on view name to see options for creating methods as shown in Fig 4.
Fig 4 - Available operations to create using SPD
SPD provides option to create the view for all common operations available in BCS or it can create operations for specific operation.
Following two minimum operations are required to fetch data from backend using BCS:
a. Query Item List method which gets the list of records and work as finder method
b. Read Item method which gets data for specific record and work as SpecificFinder method
2. Click Create Query List operation to open the wizard to define method. Specify Operation Parameters as specified in figure 5. Click next
Fig 5 - Operation Parameters
3. Skip the step to provide filter parameters as we will not define any operation to get filtered data. Click Next. In the Return Parameter Configuration step choose the options as shown in figure below
Fig 6 - Operation Return parameters
4. Repeat step 2 and 3 to create specific finder operation named SalesPersonSalesByFiscalYearsReadItem. After this, the configuration should like figure 7, showing an entity named SalesTrend with two operations.
Fig 7 - SalesTrend ECT Definition
Create External List based on External Content Type
You can create an external content type by using Microsoft SharePoint Designer 2010 or the browser. Follow the steps given below to create list using browser.
1. Open intranet.contoso.com in browser.
2. Go to Site Actions, View All Site Content.
3. Click the Create button. In the Custom Lists section, click External List.
4. On the New page, type Sales as the list name and description for the new external list.
5. The Data source configuration section displays a text box and an external content type picker. Use the picker to choose the external content type. Select SalesTrend and then click OK.
6. Click Create.
This creates the external list. You can now navigate to the new list in the SharePoint site and view/edit items.
Synchronize List using SharePoint Workspace 2010
Rich Client Composites for SharePoint Server and Office allows you to connect to external list in SharePoint site to SPD to surface external data in SharePoint Workspace 2010. You need to first sync the list with client machine using office client tools. After this the ECT metadata model becomes available on client. BCS client object model can be used to query the data from backend system using this metadata.
1. From client computer, navigate to the Sales external list using browser.
2. To take the list offline in SPD, on the SharePoint site ribbon, go to List tab; click Sync to Computer as shown in figure 8.
Fig 8 - Sync list to client
This step will create the site structure and list on the client in SharePoint Workspace. It will also copy the metadata associated with the entity to the client BCS metadata store.
3. Open the list in Workspace to check if all the records from the view are shown as list items. A detail about each selected record is shown below the list. Users can work on this data when they are offline and changes made to the data are synchronized back to the backend system if update operations are defined in ECT.
Create Excel Add-In
- Open Visual Studio 2010, select New Project and choose Excel 2007 Add-In template under Office Project Types
- Right click SalesFromECTAddin project in solution explorer, select Add, New Item, and select Ribbon (XML) as template for creating the ribbon. Name the file as SalesRibbon.cs
- Remove the auto-generated code and add the following xml in the SalesRibbon.xml file generated by Visual Studio. This XML adds a tab with label BCS, changes the label of the default control group to Sales and adds a button with label Show Sales Data. OnAction method of the SalesRibbon class is called when the button is clicked. The logic in which fetches the data from external system.
|
<?xml version="1.0" encoding="UTF-8"?> <customUI xmlns="https://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab idMso="TabAddIns" label="BCS"> <group id="ctlGroup" label="Sales"> <button id="btnFetch" label="Show Sales Data" visible="true" imageMso="SharePointListsWorkOffline" size="large" onAction="OnAction" /> </group> </tab> </tabs> </ribbon> </customUI> |
For step by step to create custom tab using Ribbon XML refer to the following link:https://msdn.microsoft.com/en-us/library/aa942955(VS.100).aspx
4. Remove the auto-generated code and add the following code in SalesRibbon.cs. OnAction method implements logic for creating instance of Sales class and showing data in excel sheet.
|
using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Reflection; using System.Runtime.InteropServices; using System.Text; using Office = Microsoft.Office.Core;
namespace SalesFromECTAddin { [ComVisible(true)] public class SalesRibbon : Office.IRibbonExtensibility { private Office.IRibbonUI ribbon;
public SalesRibbon() { }
#region IRibbonExtensibility Members
public string GetCustomUI(string ribbonID) { return GetResourceText("SalesFromECTAddin.SalesRibbon.xml"); }
#endregion
#region Ribbon Callbacks
public void OnAction(Office.IRibbonControl button) { Sales salesData= new Sales(); salesData.Show(); }
#endregion
#region Helpers private static string GetResourceText(string resourceName) { Assembly asm = Assembly.GetExecutingAssembly(); string[] resourceNames = asm.GetManifestResourceNames(); for (int i = 0; i < resourceNames.Length; ++i) { if (string.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0) { using (StreamReader resourceReader = new StreamReader( asm.GetManifestResourceStream(resourceNames[i])) ) { if (resourceReader != null) { return resourceReader.ReadToEnd(); } } } } return null; } #endregion } } |
5. Copy the following code in ThisAddIn.cs file. This code overrides the CreateRibbonExtensibilityObject method and returns the Ribbon XML class to the Office Excel application.
|
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; using Microsoft.Office.Tools.Excel; using Microsoft.Office.Tools.Excel.Extensions;
namespace SalesFromECTAddin { /// <summary> /// Add-In entry point class /// </summary> public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { }
private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { }
protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject() { return new SalesRibbon(); }
#region VSTO generated code
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion } } |
6. Create a new class called Sales and copy the following code in the file. Business Connectivity Services (BCS) uses a cache to store a copy of the external data that is synchronized to the client. The cache enables external data to be automatically copied on the client and managed for real-time access, with automatic data synchronization between the cache and the external application.The client cache is implemented as a per-user Microsoft SQL Server Compact database, which is encrypted with Encrypting File System (EFS). In this walkthrough, we will work online and execute method directly against the external system and will not use the cached(offline) copy. The last parameter OperationMode in api call entity.FindFiltered(in code below) determines if the call is made online or to the offline copy of data.
|
using System; using System.Collections.Generic; using System.Data; using System.Windows.Forms; using Microsoft.BusinessData.MetadataModel.Collections; using Microsoft.Office.BusinessData.MetadataModel; using Microsoft.BusinessData.MetadataModel; using Microsoft.BusinessData.Runtime; using Microsoft.Office.Interop.Excel;
namespace SalesFromECTAddin { /// <summary> /// Connects to backend using BCS OM and fetches the data /// </summary> public class Sales { public Sales() { }
/// <summary> /// Executes the method to refresh data /// </summary> public void Show() { GetDataFromExternalSystem(); }
/// <summary> /// Reads the metadata from client store and executes Finder /// method against AdventureWorks DB using BCS client OM /// </summary> private void GetDataFromExternalSystem() { // Open the shared metadatacatalog to read LOB metadata // information. This class represents the Office clients // shared store for LOB metadata. Once on client it can be // accessed from any Office client app RemoteSharedFileBackedMetadataCatalog catalog = new RemoteSharedFileBackedMetadataCatalog();
// Get all the LOB Systems in the catalog INamedLobSystemDictionary lobs = catalog.GetLobSystems("*");
// Pick up our AdventureWorks app def from LOB system ILobSystem lob = lobs["AdventureWorks2008DB"];
// Find all the instances of AdventureWorks app def. // In this case it's only going to be one. INamedLobSystemInstanceDictionary lobInstances = lob.GetLobSystemInstances(); ILobSystemInstance instance = lobInstances[0].Value;
// Find all entities defined in the LOB instance INamespacedEntityDictionaryDictionary entities = instance.GetEntities();
// Pick up the first entity definition. // It represents SalesTrend ECT IEnumerator<IEntity> enumerator = entities.GetEnumerator(); enumerator.MoveNext(); IEntity entity = enumerator.Current;
// Get the Finder method instances INamedMethodInstanceDictionary methodInstances = entity.GetMethodInstances(MethodInstanceType.Finder);
// Get the view for the Finder method IView vw = entity.GetFinderView(methodInstances[0].Key);
// Create definition for data table to hold backend data System.Data.DataTable dt = new System.Data.DataTable(); for (int count = 0; count < vw.Fields.Count; count++) { dt.Columns.Add(vw.Fields[count].TypeDescriptor.Name); }
IFilterCollection fc = entity.GetDefaultFinderFilters(); // Execute Finder method to get data from backend. // Operation Mode is online, hence data will be fetched // from backend system and not read from cache IEntityInstanceEnumerator entitiesList = entity.FindFiltered(fc, methodInstances[0].Value.Name, instance, OperationMode.Online);
IFieldCollection flds = vw.Fields; // Populate the data table while (entitiesList.MoveNext()) { DataRow row = dt.NewRow(); foreach (IField fld in flds) { row[fld.Name] = entitiesList.Current[fld.Name]; } dt.Rows.Add(row); }
// Refresh excel sheet RefreshSheet(dt,vw); }
/// <summary> /// Read the datatable and refreshes the Excel Sheet /// </summary> /// <param name="dt"></param> /// <param name="vw"></param> private void RefreshSheet(System.Data.DataTable dt, IView vw) { try { Worksheet xlSheet = ((Worksheet)Globals.ThisAddIn.Application. ActiveWorkbook.Worksheets["Sheet1"]);
xlSheet.Cells.Clear();
int numFlds = vw.Fields.Count; IFieldCollection flds = vw.Fields;
int colCounter=0; // Render headers foreach (IField fld in flds) { colCounter++; xlSheet.Cells[1, colCounter] = fld.Name; } // Render data rows for (int rowCounter = 0; rowCounter < dt.Rows.Count; rowCounter++) { colCounter=0; while(colCounter<numFlds) { xlSheet.Cells[rowCounter + 2, colCounter+1] = dt.Rows[rowCounter][colCounter]; colCounter++; } } } catch (Exception ex) { MessageBox.Show(ex.StackTrace); } } } } |
7. Compile this source and see if build is successful. Next task is to create a clickonce setup package to install the add-in. This is very easy and can be done in Visual Studio itself. Right click the project in solution explorer and choose publish option. This will bring up the dialog as shown in figure 9. Choose the location where you want to create the vsto package and select Finish. This will create the package at the specified location.
Fig 9 - Publishing Wizard
Deploy and Test the solution
- Double click the .vsto file created in the package above to install the add-in. The installer will run the appropriate customizations and install the add-in.
- Open the Excel application and you should see the tab with label BCS in the ribbon. Select the newly created tab and you will see a button Get Sales Data in the ribbon. This shows that our solution has successfully installed on the client.
- Click the button to execute Finder method on the ECT. The client object model executes the call against the external system since we choose the operationmode as online in the code. Code will fetch the sales information and update the sheet1 of excel file with data as shown in Fig 10. This successfully demonstrates the BCS features on the client.
Fig 10 - External data in Excel file
Conclusion
The new BCS feature in SharePoint 2010 provides numerous features and possibilities to consume data from external system and use in offline and connected fashion. Add-ins, task panes and other Office extensibility features can be created which can help interact with the external data using the user friendly Office Client applications.
Additional Resources
For more information, see the following resources: