Export (0) Print
Expand All
Expand Minimize

Connecting to a .NET Framework Source Using Business Connectivity Services in Office 2010

Office 2010

Office Visual How To

Summary:  Learn how to create an Office 2010Business Connectivity Services External Content Type based on a .NET Framework source. (29 printed pages)

Last modified: September 12, 2012

Applies to: Excel 2010 | Office 2010 | Open XML | PowerPoint 2010 | VBA | Word 2010

Published:   February 2010

Provided by:   Joel Krist, iSoftStone

Overview

Microsoft Business Connectivity Services (BCS) provided with Office 2010 and SharePoint 2010 builds on the functionality that is provided by the Business Data Catalog included with Microsoft Office SharePoint Server 2007 to introduce new features such as write-back support and rich client integration. Visual Studio 2010 provides a Business Data Connectivity Model project template and several new tools that simplify how to create BCS models and the deployment of Office solutions that uses external data and services. This Visual How To illustrates using Visual Studio 2010 to create a BCS external content type. The external content type connects to a .NET Framework assembly that uses Linq to XML to provide read-write access to data that is stored in a simple XML data file. The external content type is shown being used from a SharePoint external list.

Code It

This Visual How To shows you how to create and use a .NET Framework-based BCS external content type:

  1. Creating an XML data file to simulate a back-end data store.

  2. Creating a SharePoint 2010 Business Data Connectivity Model project in Visual Studio 2010.

  3. Deleting the default Visual Studio generated entity from the model.

  4. Adding the Customer Class to the project.

  5. Adding the Customer entity to the model.

  6. Adding a reference to Microsoft.BusinessData to the project.

  7. Adding code to the Customer external content type to implement the Finder, Specific Finder, Creator, Updater, and Deleter stereotypes.

  8. Deploying the solution to SharePoint and testing the Customer external content type by creating a SharePoint external list.

Creating the Simulated Back-End Data Store

This Visual How To creates an external content type based on a Customer entity. The Customer entity is very simple and has two fields: CustomerID and CustomerName. To reduce complexity this Visual How To uses a simple XML data file to simulate a back-end data store for customer data. The following steps describe how to create the customer data file.

To create the simulated back-end data store

  1. Start your favorite XML editor and create a new file.

  2. Copy and paste the following XML into the new file.

    <?xml version="1.0" encoding="utf-8"?>
    <Customers NextCustomerId="2">
      <Customer ID="0">
        <CustomerName>Customer 0</CustomerName>
      </Customer>
      <Customer ID="1">
        <CustomerName>Customer 1</CustomerName>
      </Customer>
    </Customers>
    
  3. Save the new file as C:\Dev\Temp\CustomerData.xml

Creating a SharePoint 2010 Business Data Connectivity Model Project in Visual Studio 2010

To create a SharePoint 2010 Business Data Connectivity Model in Visual Studio 2010

  1. Start Microsoft Visual Studio 2010 as an administrator.

  2. On the Visual Studio File menu, select New then Project.

  3. In the New Project dialog box select the Visual C# SharePoint 2010 template type in the Installed Templates pane.

  4. In the templates list, select the Business Data Connectivity Model template.

  5. Name the project and solution CustomerModel.

    Figure 1. Creating the Solution

    Creating the Solution
  6. Click OK. Visual Studio displays the SharePoint Customization Wizard.

  7. Enter the URL of the local site to use for deployment and debugging.

    Figure 2. SharePoint Customization Wizard

    SharePoint Customization Wizard
  8. Click Finish to complete the wizard and create the solution. Visual Studio creates the solution and displays the model in the External Content Type Designer.

    Figure 3. New Model in the External Content Type Designer

    New Model

Deleting the Default Visual Studio Generated Entity from the Model

Visual Studio creates a default entity when a new Business Data Connectivity Shared Service model is created. This Visual How To takes the approach of deleting the default entity in order to simplify the the steps that are required to create a new entity named Customer.

To delete the default Visual Studio-generated entity

  1. Right-click on the Entity1 entity that is displayed in the External Content Type Designer, and then select Delete from the context menu.

    Figure 4. Deleting the Default Entity

    Deleting the Default Entity
  2. In Solution Explorer, expand the BdcModel1 node, right-click Entity1.cs and then click Delete.

  3. Click OK when you are prompted to confirm the deletion. Do the same for the Entity1Service.cs file. The result is an empty BDC model.

    Figure 5. The Empty BDC Model

    Empty BDC Model

Adding the Customer Class to the Project

The Customer class encapsulates the data for a customer and is used in the implementation of the Customer entity described later.

To add the customer class to the project

  1. In Visual Studio Solution Explorer, expand the CustomerModel project node and right-click the BdcModel1 node.

  2. Select Add and then click Class. The Add New Item dialog box appears with the Class template selected.

  3. Type Customer.cs for the name of the class file and then click Add to create the class. Visual Studio opens the Customer.cs file in the Code Editor.

  4. Replace the contents of the Customer.cs file with the following code.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace CustomerModel.BdcModel1
    {
        public class Customer
        {
            public Int32 CustomerID { get; set; }
            public string CustomerName { get; set; }
        }
    }
    
    
  5. Save the changes to the Customer.cs file.

Adding the Customer Entity to the Model

The following steps describe how to add the Customer entity to the model and add the CustomerID identifier to the entity.

To create the customer entity

  1. In Visual Studio, select the BdcModel1.bdcm file to display the External Content Type Designer.

  2. On the Visual Studio View menu, click Toolbox.

  3. From the BusinessDataConnectivity tab of the Toolbox, drag an Entity onto the External Content Type Designer. The new entity appears on the designer and Visual Studio adds a file to the project named EntityService.cs.

  4. On the View menu, click Properties Window.

  5. In the Properties window, set the Name property of the new entity to Customer. Visual Studio renames the EntityService.cs file to CustomerService.cs.

To add a CustomerID identifier to the customer entity

  1. On the External Content Type Designer, right-click the Customer entity, click Add, and then click Identifier. A new identifier appears on the entity.

  2. In the Properties window, change the name of the new identifier to CustomerID.

  3. In the Type Name drop-down list, select System.Int32.

  4. The following figure shows the Customer entity with CustomerID variable added.

    Figure 6. Customer Entity with CustomerID Variable

    Customer Entity with CustomerID Variable

Adding a Reference to Microsoft.BusinessData to the Project

The sample code provided with this Visual How To uses exceptions defined in the Microsoft.BusinessData.Runtime namespace provided by the Microsoft.BusinessData.dll assembly.

To add a reference to Microsoft.BusinessData

  1. Select the CustomerModel project in the Visual Studio Solution Explorer.

  2. From the Project menu, select Add Reference in Visual Studio. The Add Reference dialog box appears.

  3. Select the Browse tab and navigate to the following folder: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPIScroll until the Microsoft.BusinessData.dll assembly is visible, Select the assembly and then click OK.

    Figure 6a. Add Reference to Microsoft.BusinessData

    Add Reference to Microsoft.BusinessData

Adding Code to the External Content Type

The following steps describe how to implement the Finder, Specific Finder, Creator, Updater, and Deleter stereotypes or methods on the Customer external content type.

The Finder method is called by the Business Data Connectivity (BDC) Shared Service to request a list of Customer entities.

Adding a Finder Method

To add a finder method

  1. On the Visual Studio External Content Type Designer, select the Customer entity.

  2. On the Visual Studio View menu, click Other Windows, and then click BDC Method Details.

  3. In the BDC Method Details window, from the Add a Method drop-down list, select Create Finder Method. Visual Studio adds a method named ReadList, a return parameter named customerList, and a type descriptor named CustomerList.

  4. In the BDC Method Details window, click the drop-down list that appears for the CustomerList type descriptor, and then click Edit. The BDC Explorer opens. The BDC Explorer provides a hierarchical view of the model.

  5. In the Properties window set the Type Name property of the CustomerList type descriptor to System.Collections.Generic.IEnumerable`1[CustomerModel.BdcModel1.Customer, BdcModel1].

  6. In the BDC Explorer, expand the CustomerList type descriptor node and select the child Customer type descriptor node.

  7. In the Properties window, set the Type Name property of the Customer type descriptor to CustomerModel.BdcModel1.Customer, BdcModel1.

  8. In the BDC Explorer, right-click the Customer type descriptor node and then click Add Type Descriptor. A new type descriptor named TypeDescriptor appears in the BDC Explorer under the Customer node.

  9. In the Properties window, set the Name property of the new type descriptor to CustomerID.

  10. Set the Type Name property of the CustomerID type descriptor to System.Int32.

  11. Use the drop-down list to set the Identifier property of the CustomerID type descriptor to CustomerID.

  12. In the BDC Explorer, right-click the Customer type descriptor node and then click Add Type Descriptor. A new type descriptor named TypeDescriptor appears in the BDC Explorer under the Customer node.

  13. In the Properties window, set the Name property of the new type descriptor to CustomerName.

  14. The following figure shows what the model looks like after you add the Finder method.

    Figure 7. Model with Finder Method

    Model with Finder Method
  15. In the External Content Type Designer, on the Customer entity, double-click the ReadList method. The CustomerService.cs file opens in the Code Editor.

  16. The .NET Framework assembly used by the Customer external content type uses Linq to XML to provide read-write access to data that is stored in the XML data file. In addition, as mentioned earlier, it uses exceptions defined in the Microsoft.BusinessData.Runtime namespace. Add the following using statements to the top of the CustomerService.cs file, under the using statements that you added in Visual Studio.

    using System.Xml.Linq;using Microsoft.BusinessData.Runtime;
    
  17. Add the following variable declaration to the CustomerService class defined in the CustomerService.cs file. Use the dataFilePath variable to specify the location of the XML data file that contains customer data. If the data file was created in a different folder than that shown in this section, then change the value of the dataFilePath variable to match.

    private const string dataFilePath = @"C:\Dev\Temp\CustomerData.xml";
    
  18. Replace the definition of the ReadList method in the CustomerService.cs file with the following code.

    public static IEnumerable<Customer> ReadList(){  try  {
        var customers =
          XElement.Load(dataFilePath).Elements("Customer");
        var customersList =
          from cust in customers
          orderby (string)cust.Attribute("ID")
          select new Customer
          {
            CustomerID = (Int32)cust.Attribute("ID"),
            CustomerName = (string)cust.Element("CustomerName")
          };
          return customersList;
      }
      catch (Exception generalException)
      {
        throw new RuntimeException(
          "There was a problem reading customer data.",
          generalException);  }}
    

Adding a Specific Finder Method

The Specific Finder method is called by the BDC Shared Service to request the data for a specific Customer entity.

To add a specific finder method

  1. On the External Content Type Designer, select the Customer entity.

  2. In the BDC Method Details window, from the Add a Method drop-down list, select Create Specific Finder Method. Visual Studio adds a method named ReadItem, an input parameter named customerID, a return parameter named customer, and type descriptors named Customer and CustomerID.

    The following figure shows what the model looks like after you add the Specific Finder method.

    Figure 8. Model with Specific Finder Method

    Model with Specific Finder Method
  3. In the External Content Type Designer, on the Customer entity, double-click the ReadItem method. The CustomerService.cs file opens in Code Editor.

  4. Replace the definition of the ReadItem method in the CustomerService.cs file with the following code.

    public static Customer ReadItem(int customerID)
    {
      try
      {
        var customers =
          XElement.Load(dataFilePath).Elements("Customer");
    
        var customerList =
          from cust in customers
          where (Int32)cust.Attribute("ID") == customerID
          select new Customer
          {
            CustomerID = (Int32)cust.Attribute("ID"),
            CustomerName = (string)cust.Element("CustomerName")
          };
    
          // The following will throw InvalidOperationException if the
          // customerNameElements collection is empty meaning a
          // customer with the specified ID does not exist.
          return customerList.First();
      }
      catch (InvalidOperationException )
      {
        throw new ObjectNotFoundException(
          "Unable to read data for the customer with ID = " +
          customerID.ToString() +
          ". The customer no longer exists.");
      }
      catch (Exception generalException)
      {
        throw new RuntimeException(
          "There was a problem reading customer data.",
          generalException);
      }
    }
    
    

Adding a Creator Method

The Creator method is called by the BDC Shared Service to create a new Customer entity.

To add a creator method

  1. On the External Content Type Designer, select the Customer entity.

  2. In the BDC Method Details window, from the Add a Method drop-down list, select Create Creator Method. Visual Studio adds a method named Create, an input parameter named newCustomer, a return parameter named returnCustomer, and type descriptors named NewCustomer and ReturnCustomer.

  3. In the BDC Method Details window, click the drop-down list that appears for the NewCustomer type descriptor of the newCustomer input parameter for the Create method, and then click Edit. This will select the NewCustomer type descriptor node.

    In the BDC Explorer, expand the NewCustomer type descriptor node and select the child CustomerID type descriptor. In the Properties window set the Creator Field property of the CustomerID type descriptor to False. This will keep BCS from rendering a user interface control for the customer ID on the new customer form for an external list. This is needed because customer ID values are managed by the back-end and should not be editable by users.

    The following figure shows what the model looks like after you add the Creator method.

    Figure 9. Model with Creator Method

    Model with Creator Method
  4. In the External Content Type Designer, on the Customer entity, double-click the Create method. The CustomerService.cs file opens in Code Editor.

  5. Replace the definition of the Create method in the CustomerService.cs file with the following code.

    public static Customer Create(Customer newCustomer)
    {
      try
      {
        XElement customers = XElement.Load(dataFilePath);
        Int32 nextCustomerId =
          (Int32)customers.Attribute("NextCustomerId");
    
        Customer returnCustomer = new Customer();
    
        returnCustomer.CustomerID = nextCustomerId;
        returnCustomer.CustomerName = newCustomer.CustomerName;
    
        XElement newCustomerElement = new XElement("Customer");
        newCustomerElement.SetAttributeValue("ID", nextCustomerId);
    
        XElement newCustomerNameElement = new XElement("CustomerName",
          returnCustomer.CustomerName);
    
        newCustomerElement.Add(newCustomerNameElement);
        customers.Add(newCustomerElement);
    
        customers.SetAttributeValue("NextCustomerId",
          nextCustomerId + 1);
        customers.Save(dataFilePath);
    
        return returnCustomer;
      }
      catch (Exception generalException)
      {
        throw new RuntimeException(
          "There was a problem creating a new customer.",
          generalException);
      }
    }
    
    

Adding an Updater Method

The Updater method is called by the BDC Shared Service to edit an existing Customer entity.

To add an updater method

  1. On the External Content Type Designer, select the Customer entity.

  2. In the BDC Method Details window, from the Add a Method drop-down list, select Create Updater Method. Visual Studio adds a method named Update, an input parameter named customer, and a type descriptor named Customer.

  3. In the BDC Method Details window, add another input parameter to the Update method. Set the Name property of the new parameter to customerID. Select the type descriptor of the new parameter. In the Properties window, set the Name property to CustomerID, set the Type Name property to System.Int32, and set the Pre-Updater property to True.

    The following figure shows what the model looks like after you add the Updater method.

    Figure 10. Model with Updater Method

    Model with Updater Method
  4. In the External Content Type Designer, on the Customer entity, double-click the Update method. The CustomerService.cs file opens in Code Editor.

  5. Replace the definition of the Update method in the CustomerService.cs file with the following code.

    public static void Update(Customer customer, Int32 customerID)
    {
      try
      {
        XElement customers = XElement.Load(dataFilePath);
    
        var customerNameElements =
          from cust in customers.Elements("Customer")
          where (Int32)cust.Attribute("ID") == customer.CustomerID
          select cust.Element("CustomerName");
    
        // The following will throw InvalidOperationException if
        // the customerNameElements collection is empty meaning a
        // customer with the specified ID does not exist.
        XElement customerNameElement = customerNameElements.First();
        customerNameElement.SetValue(customer.CustomerName);
    
        customers.Save(dataFilePath);
      }
      catch (InvalidOperationException)
      {
        throw new ObjectNotFoundException(
          "Unable to update the customer with ID = " +
          customerID.ToString() +
          ". The customer no longer exists.");
      }
      catch (Exception generalException)
      {
        throw new RuntimeException(
          "There was a problem updating the customer with ID = " +
          customerID.ToString() + ".", generalException);
      }
    }
    

Read It

This Visual How To provides steps and sample code that illustrate how to create a BCS external content type that uses a .NET Framework assembly that can perform read-write operations on an XML data file. This section uses code snippets taken from the sample code provided here to describe the approach taken by this Visual How To.

Finder Method Implementation

Finder methods are used by the BDC Shared Service to return a list of entities. In the walkthrough shown here, the Customer entity's Finder method is called when SharePoint displays the list of customer items in the external list. In the sample code provided previously the Customer entity's Finder method is implemented by the CustomerService.ReadList method.

The ReadList method first opens the XML data file and gets a collection of Customer elements.

var customers = XElement.Load(dataFilePath).Elements("Customer");

It then creates and returns a collection of Customer objects that are created from each of the Customer elements.

var customersList =  from cust in customers
  orderby (string)cust.Attribute("ID")
  select new Customer
  {    CustomerID = (Int32)cust.Attribute("ID"),
    CustomerName = (string)cust.Element("CustomerName")
  };return customersList;

Specific Finder Method Implementation

Specific Finder methods are used by the BDC Shared Service to return a specific entity instance. In the walkthrough shown here, the Customer entity's Specific Finder method is called when you choose to edit or view a customer item in the external list. In the sample code provided previously, the Customer entity's Specific Finder method is implemented by the CustomerService.ReadItem method.

The ReadItem method accepts an Int32 parameter which represents the unique ID of the requested customer instance.

public static Customer ReadItem(int customerID)

The ReadItem method first opens the XML data file and gets a collection of Customer elements.

It then creates a collection of Customer objects from each of the Customer elements whose ID attribute is equal to the customer ID passed to the method. Because customer IDs are unique the collection should only contain one customer so the method returns the first item in the collection.

var customerList =
    from cust in customers
    where (Int32)cust.Attribute("ID") == customerID
    select new Customer
    {
      CustomerID = (Int32)cust.Attribute("ID"),
      CustomerName = (string)cust.Element("CustomerName")
    };

  return customerList.First();

Creator Method Implementation

Creator methods are called by the BDC Shared Service to create new entity instances. In the walkthrough shown previously, the Customer entity's Creator method is called when you add a new customer item to the external list. In the sample code provided, the Customer entity's Creator method is implemented by the CustomerService.Create method.

The Create method accepts a Customer parameter which represents the customer data that was entered via the external list's new item form.

public static Customer Create(Customer newCustomer)

The Create method first opens the XML data file and gets the top level Customers element. It then reads the value of the ID to be used for the new customer from the NextCustomerId attribute.

XElement customers = XElement.Load(dataFilePath);
Int32 nextCustomerId =
  (Int32)customers.Attribute("NextCustomerId");

The method creates a new Customer object and sets its CustomerID property to the value that was read and sets its CustomerName property to the value of the CustomerName property of the Customer object that was passed to the method.

Customer returnCustomer = new Customer();

returnCustomer.CustomerID = nextCustomerId;
returnCustomer.CustomerName = newCustomer.CustomerName;

The method next creates new Customer and CustomerName elements, sets their Customer ID attribute and CustomerName values, and adds the elements to the top level Customers element.

XElement newCustomerElement = new XElement("Customer");
newCustomerElement.SetAttributeValue("ID", nextCustomerId);

XElement newCustomerNameElement = new XElement("CustomerName",
  returnCustomer.CustomerName);

newCustomerElement.Add(newCustomerNameElement);
customers.Add(newCustomerElement);

Finally, the value of the NextCustomerId attribute is incremented and all changes are saved back to the XML data file.

customers.SetAttributeValue("NextCustomerId", nextCustomerId + 1);
customers.Save(dataFilePath);

Updater Method Implementation

Updater methods are called by the BDC Shared Service to edit existing entity instances. In the walkthrough shown here, the Customer entity's Updater method is called when a user edits a customer item in the external list. In the sample code provided previously the Customer entity's Updater method is implemented by the CustomerService.Update method.

The Update method accepts a Customer parameter which represents the customer data that was entered by the user via the external list's edit item form and an Int32 parameter which represents the unique ID of the customer being updated.

public static void Update(Customer customer, Int32 customerID)

The Update method first opens the XML data file and gets the top level Customers element.

XElement customers = XElement.Load(dataFilePath);

It then creates a collection of Customer objects from each of the Customer elements whose ID attribute is equal to the customer ID passed to the method.

var customerNameElements =
  from cust in customers.Elements("Customer")
  where (Int32)cust.Attribute("ID") == customer.CustomerID
  select cust.Element("CustomerName");

Because customer IDs are unique the collection should only contain one customer so the method gets the first item in the collection and sets its value to the value of the CustomerName property of the Customer object that was passed to the method. Finally, all changes are saved back to the XML data file.

XElement customerNameElement = customerNameElements.First();
customerNameElement.SetValue(customer.CustomerName);

customers.Save(dataFilePath);

Deleter Method Implementation

Deleter methods are called by the BDC Shared Service to delete existing entity instances. In the walkthrough shown here, the Customer entity's Deleter method is called when a user deletes a customer item from the external list. In the sample code provided previously the Customer entity's Deleter method is implemented by the CustomerService.Delete method.

The Delete method accepts an Int32 parameter which represents the unique ID of the customer being deleted.

public static void Delete(int customerID)

The Delete method first opens the XML data file and gets the top level Customers element.

XElement customers = XElement.Load(dataFilePath);

It then creates a collection of Customer objects from each of the Customer elements whose ID attribute is equal to the customer ID passed to the method.

var customerElements =
  from cust in customers.Elements("Customer")
  where (Int32)cust.Attribute("ID") == customerID
  select cust;

Because customer IDs are unique the collection should only contain one customer so the method gets the first item in the collection and removes it. Finally, all changes are saved back to the XML data file.

XElement customer = customerElements.First();
customer.Remove();

customers.Save(dataFilePath);

See It

Watch the video

Watch video

Length: 00:25:12

Click to grab code

Grab the Code

Explore It

 

Show:
© 2014 Microsoft