Consuming External Data Using SharePoint Server 2010 Business Connectivity Services and an Excel 2010 Add-In

SharePoint 2010

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.

Last modified: March 09, 2015

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

Click to get code   Download code: Business Connectivity Services Excel 2010 Add-in (SalesOrderAddIn1.7.zip)

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.

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.

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

  1. Create a site collection that you will use for connecting through SharePoint Designer and for creating the external content types.

  2. On the client computer, start SharePoint Designer, and then open the site that you created.

  3. To create an external content type, go to Site Objects on the left navigation pane, and then click External Content Types.

  4. 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

  5. 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.

  6. 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.

  7. 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 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.

  8. 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

  1. 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.

  2. 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.

  3. Go to the Read Item operation, and then rename it SalesOrderHeaderReadItem.

  4. Go to the Read List operation, and then rename it SalesOrderHeaderReadList.

  5. Save the external content type. Figure 1 shows SalesOrderHeader properties and operations.

    Figure 1. SalesOrderHeader properties and operations

    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

  1. 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

  2. In the Views group on the ribbon, click Operations Designer View.

  3. To see options for creating methods, right-click the SalesOrderDetail table.

  4. Click Create New Read Item Operation. In the Operation Name field, use SalesOrderDetailReadItem. Click Next.

  5. 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.

  6. On the Return Parameters page, repeat the actions in step 5. Click Finish.

  7. Right-click the SalesOrderDetail table, and then click the Create New Read List operation. In the Operation Name field, use SalesOrderDetailReadList. Click Next.

  8. 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.

  9. 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.

  10. Right-click the SalesOrderDetail table, and then click New Association. In the Operation Name field, use SalesOrderDetailNavigateAssociation.

  11. Click Browse, click the SalesOrderHeader external content type, and then click Next.

  12. 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

    SalesOrderLine properties and defined operations

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

  1. 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.

  2. 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.

  3. Set the Label property of the RibbonGroup to Sales Orders.

  4. Drag a Box control, located in the Office Ribbon Controls category, from the Toolbox on the Ribbon group. Set the BoxStyle to Vertical.

  5. 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:

  6. Specify UpdateSalesOrderDetails as the name for the TextChanged event handler for the ComboBox control that you added earlier.

  7. 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

  8. Drag a Separator control from the Box control to separate the control that you will be using in the next step.

  9. 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

  10. Specify UpdateLineItems as the name for the Click event handler for the button that you added in the previous step.

  11. 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;
        }

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

  1. Create a folder, and then name it DataSolutionPackage. (Or, give the folder a name that you choose.)

  2. 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=
                  "http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog" >
      <Queries>
        <Query Name="QuerySalesOrderHeader" IsCached="true" RefreshIntervalInMinutes="10"
               MethodInstanceName="SalesOrderHeaderReadList" Enabled="true"></Query>
      </Queries>
    </Subscription>
    
  3. 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=
                  "http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
      <Queries>
        <Query Name="QuerySalesOrderLine" IsCached="true" RefreshIntervalInMinutes="10"
               MethodInstanceName="SalesOrderDetailReadList" Enabled="false"></Query>
      </Queries>
    </Subscription>
    
  4. 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.

  5. 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

  6. 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 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?

  7. 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

  1. 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

    Sales Orders section on Add-Ins tab
  2. 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%.

  3. 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.

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.

Show: