This article is the conclusion of a three-part series about SharePoint 2010 Business Connectivity Services (BCS). In "Business Connectivity Services, Part 1," I discussed BCS models, external content types (ECTs), external lists, and Business Data Catalog (BDC) Web Parts and demonstrated how to use them to implement functionality common to line of business (LOB) applications. In "Business Connectivity Services, Part 2," I discussed advanced data access techniques with BCS. In this article, I’ll show you how to build custom UI components that work with BCS data, including how to work around limitations in sandboxed solutions.

 

Understanding Sandbox Limitations


As you work more with BCS, you might find that SharePoint 2010’s out-of-the-box BCS components don’t always meet your needs from a UI perspective. In such scenarios, you can develop custom UI components to interact with BCS data. Before you begin developing such components, though, it’s important to understand the limitations that the sandbox imposes on BCS because those limitations can affect how you build your custom UI component:

  • You can deploy BCS models two ways: by using a farm solution, which is also known as a full trust solution, or by using SharePoint Designer. If you can deploy only sandboxed solutions in your SharePoint server farm, you must use SharePoint Designer to create and deploy your BCS models. You can’t use Visual Studio 2010.
  • There are several different APIs that you can use to interact with BCS data, including the BCS object model, the SharePoint server object model, and the SharePoint client object models (ECMA and Silverlight implementations). However, you can’t use the BCS object model in sandboxed solutions. You can only use it in full trust solutions, as Table 1 shows.
  • Sandboxed solutions require that you use an external list to interact with BCS data. For example, if you’re using the SharePoint ECMA client object model, you need to create an external list based on the ECT that corresponds to the BCS data with which you want to interact. Table 1 identifies which APIs require an external list.
  • When using the SharePoint server object model in a sandboxed solution, you can encounter a runtime error if you use a certain solution pattern. I’ll discuss this limitation in detail and how to work around it shortly.

Table 1: API Comparison
Table 1: API Comparison

Note that the limitations the sandbox imposes on BCS are by design; they enhance the security and stability of the SharePoint server farm.

 

Using Sandbox-Compatible APIs


Let’s look at three examples of how to access BCS data through custom UI components that can be deployed in sandboxed solutions.

SharePoint server object model. Listing 1 demonstrates how to use the SharePoint server object model to return BCS data and display it in a sandbox-compatible Visual Web Part. This solution pattern will look familiar to developers who have worked with previous versions of SharePoint. In this pattern, the SPQuery class is used to return items from an external list based on a Collaborative Application Markup Language (CAML) query.

When you deploy a Visual Web Part that uses this pattern in a sandboxed solution, an error occurs at runtime, as Figure 1 shows. This error occurs because the identity of the currently logged-on user is stripped from the thread when the SharePoint User Code Service executes the code. BDC denies access to the external list data because there’s no user associated with the request.

 

Figure 1: Runtime error message
Figure 1: Runtime error message

 

To prevent this error, you can set up a target application in the Secure Store Service, as shown in Figure 2. The target application maps the account that the User Code Service executes to an account that has access to the data in the external list. After the target application is set up, you need to edit the BCS model. As Listing 2 shows, the BCS model needs to use the Secure Store Service to authenticate to the external data source.

 

Figure 2: Target application in the Secure Store Service
Figure 2: Target application in the Secure Store Service

 

Note that the Secure Store Service is available in SharePoint Server but not SharePoint Foundation. Thus, you can’t use the pattern shown in Listing 1 for sandboxed solutions in a SharePoint Foundation environment.

You can find a complete walkthrough of this type of solution in the “Reference Implementation: External Lists” web page in the SharePoint 2010 Patterns & Practices documentation.

SharePoint ECMA client object model. Listings 3 and 4 demonstrate how to use the SharePoint ECMA client object model to return BCS data and display it in a sandbox-compatible Visual Web Part. In Listing 3, the GetCustomers function queries the external list and returns all the items in the list. The code also registers the OnCustomersQuerySuccess delegate function that’s called when the query completes successfully.

The OnCustomersQuerySuccess function in Listing 4 iterates though the items returned from the external list and formats how they’re displayed on the page. Cascading Style Sheets (CSS) classes are used to style the results, and JQuery is used to append the output to an existing section in the Web Part. Figure 3 shows an example of what the Visual Web Part looks like.

 

Figure 3: Visual Web Part displaying BCS data returned by the SharePoint ECMA client object model
Figure 3: Visual Web Part displaying BCS data returned by the SharePoint ECMA client object model

 

SharePoint Silverlight client object model. Listings 5 through 7 demonstrate how to use the SharePoint Silverlight client object model to return BCS data and display it in a Silverlight application. Silverlight applications can be deployed in a sandboxed solution, and the out-of-the-box Silverlight Web Part can be used to display Silverlight applications on SharePoint web pages.

As seen in Listing 5, the loadCustomers method in the Silverlight application queries the Customers external list and returns all the items in the list. The code also registers the OnQuerySucceeded delegate method that’s called when the query completes successfully.

To process the query results, OnQuerySucceeded uses the Dispatcher.BeginInvoke method to invoke the DisplayCustomerInfo delegate method on the UI thread (Listing 6). DisplayCustomerInfo iterates though the items returned from the external list and adds them to a list of customers. Finally, DisplayCustomerInfo binds the list of customers to a list box in the Silverlight application to display them (Listing 7).

Figure 4 shows an example of what the Silverlight application looks like. When a user selects one of the items in the list box, the labels in the Silverlight control are populated with the corresponding data.

 

Figure 4: Silverlight application displaying BCS data from an external list
Figure 4: Silverlight application displaying BCS data from an external list

Using Full Trust Compatible APIs

You can use all the object models listed in Table 1 to build custom UI components that can be deployed in full trust solutions. However, I’ll just concentrate on how to use the BCS object model since you’ve already seen how to use the other APIs.

The code in Listing 8 demonstrates how to create a Visual Web Part that uses the BCS object model to retrieve BCS data. The code doesn’t access an external list. Instead, the code accesses the BDC metadata catalog to locate an ECT and executes the queries directly against the external data source. This solution pattern must be deployed in a full trust solution.

Figure 5 shows a Visual Web Part that uses the BCS object model to retrieve data from the Customer content type. In this example, the BCS object model returns a DataTable that’s bound to a SPGridView control. The SPGridView control formats the data to look like the rest of the SharePoint site, including the custom drop-down menu items added to each record in the list. You can use other third-party controls to present the data returned from the BCS object model, including charts, graphs, dials, and gauges.

 

Figure 5: Visual Web Part displaying BCS data returned by the BCS object model
Figure 5: Visual Web Part displaying BCS data returned by the BCS object model

 

The BCS object model supports executing any of the stereotyped methods defined in a BCS model. For example, the code in Listing 9 uses the SpecificFinder method to return the information for a specific customer.

Figure 6 shows a Visual Web Part that uses the BCS object model to retrieve data for a single customer. In this example, the Visual Web Part creates the HTML and applies CSS classes to format the output.

 

Figure 6: Visual Web Part displaying BCS data for a specific customer
Figure 6: Visual Web Part displaying BCS data for a specific customer

Additional Options

There are many different options available to build custom UI components that work with BCS data. In addition to the options I demonstrated here, you can also use the SharePoint managed client object model to work with BCS data in WinForms applications, Windows Presentation Foundation (WPF) applications, console applications, Windows Azure roles, and Azure web roles. Although the examples I presented illustrate read operations, you can also perform insert, update, and delete operations on BCS data using the SharePoint ECMA and SharePoint Silverlight client object models. The BCS object model provides the ultimate level of flexibility by letting you call any stereotyped method defined in the BCS model.

The SharePoint 2010 Patterns & Practices documentation includes excellent reference implementations, how-to articles, and other types of documentation related to BCS. I encourage you to explore this documentation to supplement your knowledge of BCS.

 

Listing 1: Code That Uses the SharePoint Server Client Object Model to Return BCS Data

private SPListItemCollection results;

protected void Page_Load(object sender, EventArgs e)

\\{

  SPQuery query = new SPQuery();

  query.Query = "<OrderBy>" +

    "<FieldRef Name='City' Ascending='True' />" +

    "</OrderBy>";

  results = SPContext.Current.Web.Lists\\["Customers"\\].GetItems(query);

\\}

  public override void RenderControl(HtmlTextWriter writer)

\\{

  foreach (SPListItem spListItem in results)

  \\{

    writer.Write(spListItem\\[0\\] + "<BR><BR>");

  \\}

  base.RenderControl(writer);

\\}

 

Listing 2: Code That Configures the BCS Model to use the Secure Store Service
 

<Property Type="System.String">WindowsCredentials</Property>

<Property Type="System.String">SqlServer</Property>

<Property Type="System.String">DEMO2010A</Property>

<Property Type="System.String">AdventureWorks</Property>

<Property Type="System.String">SSPI</Property>

<Property Type="System.String">True</Property>

<Property Type="System.String">CustApp</Property>

<Property

  Type="System.String">Microsoft.Office.SecureStoreService.Server.SecureStoreProvider,

  Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral,

  PublicKeyToken=71e9bce111e9429c</Property>

 

Listing 3: Code That Uses the SharePoint ECMA Client Object Model to Return BCS Data
 

var customersListName = "Customers";

var customersList = "Customers";

var output = "";

var customerListItem;

function GetCustomers() \\{

  $("#MainContainer").empty();

  this.ClientContext = SP.ClientContext.get_current();

  var CamlQuery = new SP.CamlQuery();

  CamlQuery.set_viewXml = "<View>" +

    "<Query><OrderBy>" +

    "<FieldRef Name='City' Ascending='True' />" +

    "</OrderBy></Query>"

    "</View>";

  this.web = this.ClientContext.get_web();

  this.site = this.ClientContext.get_site();

  this.ClientContext.load(this.web);

  this.ClientContext.load(this.site);

  this.customersList = this.web.get_lists().getByTitle(customersListName);

  this.customersListItems = this.customersList.getItems(CamlQuery);

  this.ClientContext.load(this.customersListItems, "Include(Identifier1, " +

    "FirstName, LastName, Address, City, State, PostalCode)");

  this.ClientContext.executeQueryAsync(

    Function.createDelegate(this, this.OnCustomersQuerySuccess),

    Function.createDelegate(this, this.OnCustomersQueryFailed));

\\}


 

Listing 4: Code That Formats the Items Returned from an External List in a Visual Web Part
 

function OnCustomersQuerySuccess(sender, args) \\{

  if (this.customersListItems.get_count() > 0) \\{

    var customersEnumerator = this.customersListItems.getEnumerator();

    output = "";

    while (customersEnumerator.moveNext()) \\{

      customerListItem = customersEnumerator.get_current();

      output += "<div class=\"customer\">";

      output += "<div class=\"customerID\">Customer " +

        customerListItem.get_item('Identifier1') +

        "</div>";

      output += "<div class=\"customerName\">" +

        customerListItem.get_item('FirstName') + " " +

        customerListItem.get_item('LastName') +

        "</div>";

      output += "<div class=\"customerAddress\">" +

        customerListItem.get_item('Address') +

        "</div>";

      output += "<div class=\"customerAddress\">" +

        customerListItem.get_item('City') + ", " +

        customerListItem.get_item('State') + ", " +

        customerListItem.get_item('PostalCode') +

        "</div>";

      output += "</div>";

    \\}

    $('.MainContainer').append(output);

  \\}

  else \\{

    $('.MainContainer').append(output);

    output += "<div class=\"result\">No customers found.</div>";

  \\}

\\}

 

Listing 5: Code That Uses the SharePoint Silverlight Client Object Model to Return BCS Data
 

private void loadCustomers()

\\{

  List customersList = Common.ClientContext.Web.Lists.GetByTitle("Customers");

  CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();

  query.ViewXml = @"<View>" +

    @"<Query><OrderBy>" +

    @"<FieldRef Name='City' Ascending='True' />" +

    @"</OrderBy></Query>" +

    @"<ViewFields>" +

    @"<FieldRef Name='Identifier1' />" +

    @"<FieldRef Name='FirstName' />" +

    @"<FieldRef Name='LastName' />" +

    @"<FieldRef Name='Address' />" +

    @"<FieldRef Name='City' />" +

    @"<FieldRef Name='State' />" +

    @"<FieldRef Name='PostalCode' />" +

    @"<FieldRef Name='Country' />" +

    @"<FieldRef Name='EmailAddress' />" +

    @"<FieldRef Name='Phone' />" +

    @"<FieldRef Name='ShippingCompany' />" +

    @"<FieldRef Name='ShippingAccountNumber' />" +

    @"</ViewFields>" +

    @"</View>";

  Common.Customers = customersList.GetItems(query);

  Common.ClientContext.Load(Common.Customers);

  Common.ClientContext.ExecuteQueryAsync(onQuerySucceeded, onQueryFailed);

  statusTextBlock.Text += "Loading Customers . . .\n";

\\}

Listing 6: Code That Invokes a Delegate Method on the UI Thread to Process the Query Results
 

private void onQuerySucceeded(object sender, ClientRequestSucceededEventArgs args)

\\{

  UpdateUIMethod updateUI = DisplayCustomerInfo;

  this.Dispatcher.BeginInvoke(updateUI);

\\}

 

Listing 7: Code That Formats the Items Returned from an External List in a Silverlight Application
 

private void DisplayCustomerInfo()

\\{

  customersListBox.ItemsSource = null;

  // Create a generic list of type Customer to hold the customers.

  List<Customer> customersList = new List<Customer>();

  // Populate the generic list of type Customer with customers.

  foreach (ListItem item in Common.Customers)

  \\{

    Customer customer = new Customer();

    customer.ID = item\\["Identifier1"\\].ToString();

    customer.FirstName = item\\["FirstName"\\].ToString();

    customer.LastName = item\\["LastName"\\].ToString();

    customer.Address = item\\["Address"\\].ToString();

    customer.City = item\\["City"\\].ToString();

    customer.State = item\\["State"\\].ToString();

    customer.PostalCode = item\\["PostalCode"\\].ToString();

    customer.Country = item\\["Country"\\].ToString();

    customer.EmailAddress = item\\["EmailAddress"\\].ToString();

    customer.Phone = item\\["Phone"\\].ToString();

    customer.ShippingCompany = item\\["ShippingCompany"\\].ToString();

    customer.ShippingAccountNumber = item\\["ShippingAccountNumber"\\].ToString();

    customersList.Add(customer);

  \\}

  // Bind the list of customers to the list box.

  customersListBox.ItemsSource = customersList;

  statusTextBlock.Text += "Customers Loaded\n";

\\}


 

 

Listing 8: Code That Uses the BCS Object Model to Return BCS Data
 

public DataTable GetCustomers()

\\{

  // Get the Customer entity from the metadata catalog.

  IEntity entity = catalog.GetEntity(BdcEntityNameSpace, "Entity1");

  // Get the filters defined on the default Finder method for the entity.

  IFilterCollection filters = entity.GetDefaultFinderFilters();

  // Return the filtered list of items from the external data source.

  IEntityInstanceEnumerator enumerator = entity.FindFiltered(filters, lobSystemInstance);

  // Convert the filtered list of items to a DataTable and return it.

  return entity.Catalog.Helper.CreateDataTable(enumerator);

\\}

Listing 9: Code That Executes the SpecificFinder Method with the BCS Object Model and Formats the Results
 

private string GetCustomer(string customerID)

\\{

  Identity identity = new Identity(customerID);

  IEntity entity = catalog.GetEntity(BdcEntityNameSpace, "Entity1");

  IEntityInstance instance = entity.FindSpecific(identity, lobSystemInstance);

  string output = "<div class=\"customer\">";

  output += "<div class=\"customerID\">Customer " +

    instance\\["Identifier1"\\].ToString() +

    "</div>";

  output += "<div class=\"customerName\">" +

    instance\\["FirstName"\\].ToString() + " " +

    instance\\["LastName"\\].ToString() +

    "</div>";

output += "<div class=\"customerAddress\">" +

    instance\\["Address"\\].ToString() +

    "</div>";

output += "<div class=\"customerAddress\">" +

    instance\\["City"\\].ToString() + ", " +

    instance\\["State"\\].ToString() + ", " +

    instance\\["PostalCode"\\].ToString() +

    "</div>";

  output += "</div>";    

  return output;

\\}