Modeling Complex Types in External Data

  • Article

When your external system contains tabular data structures, it's straightforward to build a representative Business Data Connectivity (BDC) model. SharePoint Designer 2010 will discover the data structures for you, and tabular entities map well to SharePoint lists. However, advanced scenarios require more complex handling. How do you create a BDC model that aggregates data from multiple sources? How do you create your data entities when your external system contains complex, nested data structures? This topic describes some of the options that can help you to address these issues.

Creating and Using .NET Connectivity Assemblies

When you create a new connection for a BDC model in SharePoint Designer 2010, you've probably noticed that you're presented with three options for the type of connection—SQL Server, WCF Service, or .NET type. This last option, the .NET type, is designed to allow you to use a .NET connectivity assembly (variously referred to as a *.*NET shim, a BCS shim, or a shim assembly) to drive your BDC model. Creating a .NET connectivity assembly allows you to write custom code to define the stereotyped operations for your BDC model. For example, you could create a Finder method, using the recommended method signature for that operation and include custom logic that queries and concatenates data from multiple entities.

Typical scenarios in which you should consider using a .NET connectivity assembly include the following:

  • You want to aggregate data from multiple services and expose a single data model to SharePoint.
  • You want to access data that is not accessible through a SQL Server database connection or a WCF Web service.
  • You want to convert proprietary data types returned by an external system into .NET data types that are understood by the BDC runtime.
  • You want to "flatten" complex data entities into fields that are compatible with the user interface (UI) components provided by the BCS.

Visual Studio provides tooling for modeling and building .NET connectivity assemblies. For more information on building .NET connectivity assemblies for BDC models, see Integrating Business Data into SharePoint on MSDN. Where possible, you should aim to make your .NET connectivity assembly methods conform to the recommended signatures for stereotyped operations, because this will enable you to maximize functionality without writing custom code. For more information, see Stereotyped Operations Supported by BDC and Recommended Method Signatures for Stereotyped Operations on MSDN.

For a practical example of a .NET connectivity assembly, see the External Data Models reference implementation.

Note

In the BDC model overview discussed in Business Data Connectivity Models, the .NET connectivity assembly maps to the external system level (LobSystem in the BDC schema). Because of this, the .NET connectivity assembly must provide classes and methods for all the entities in your model–you can't mix and match with other connection types.

Flattening Nested Complex Types

In an XML data structure, a complex type refers to an element that has child elements. A complex type that contains only one level of descendants can be represented as a tabular data structure. However, if a complex type contains more than one level of descendants—in other words, the parent node has grandchildren as well as children—the type can no longer be represented as a table or a list. In this situation, you must take additional action if you want to use built-in data constructs, such as external lists and Business Data Web Parts, to provide an interface for your external data. Flattening describes the process whereby a complex, nested type is converted into a flat two-dimensional structure that can be managed more easily by the out-of-the-box BCS components.

In general, there are two approaches you can use to manage nested complex types in SharePoint data models:

  • You can flatten the nested complex types into one or more simple types.
  • You can build a custom user interface that is able to represent the complex structure, such as through the use of custom fields or custom Web Parts.

If you choose to flatten the nested complex types, there are various options available to you. Consider the following example of a Customer entity, returned by a Web service, which includes a nested Address element.

<Customer>
  <Name>Contoso</Name>
  <Address>
    <Street>1 Microsoft Way</Street>
    <City>Redmond</City>
    <StateProvince>WA</StateProvince>
    <PostalCode>98052</PostalCode>
  </Address>
</Customer>

One approach would be to modify the Web service to return a flattened data structure that maps well to external lists and Business Data Web Parts:

<Customer>
  <Name>Contoso</Name>
  <AddressStreet>1 Microsoft Way</AddressStreet>
  <AddressCity>Redmond</AddressCity>
  <AddressStateProvince>WA</AddressStateProvince>
  <AddressPostalCode>98052</AddressPostalCode>
</Customer>

Although this approach certainly solves the problem, in many cases, you will not want, or will not be able, to modify the Web service. An alternative approach is to include a format string in the BDC model, so that the data entity is displayed as a flattened structure. In this case, the customer address is "flattened" and displayed as a single string.

<TypeDescriptor TypeName="CustomerAddress" IsCollection="false" Name="CustomerAddresses" >
  <TypeDescriptors>
    <Properties>
      <Property Name="ComplexFormatting" Type="System.String" />
    </Properties>
    <TypeDescriptor TypeName="CustomerAddress" Name="CustomerAddress" >
      <Properties>
        <Property Name="FormatString" Type="System.String">{0}, {1}, {2} {3}</Property>
      </Properties>
      <TypeDescriptors>
        <TypeDescriptor TypeName="System.String" Name="Street"/>
        <TypeDescriptor TypeName="System.String" Name="City" />
        <TypeDescriptor TypeName="System.String" Name="StateProvince" />
        <TypeDescriptor TypeName="System.String" Name="PostalCode" />
      </TypeDescriptors>
    </TypeDescriptor>
  </TypeDescriptors>
</TypeDescriptor>

In this example, the format string tells the BDC runtime how to render the address entity as a single string, in the order that the child elements are listed in the TypeDescriptors collection. If you apply the sample data to this BDC model, the address is formatted on a single line as 1 Microsoft Way, Redmond, WA 98052. You can programmatically retrieve the formatted data by using the EntityInstance.GetFormatted("FieldName") method. However, this approach has several limitations. First, the approach is only viable if the data entity can be represented effectively as a single string. Second, this formatting only handles the display of data. If you need to update the external data, you must add programming logic or custom forms to parse the new values and update the data source. Unfortunately, you can only use format strings with Business Data Web Parts. This approach will not work with external lists.

A third option is to use a custom renderer. A custom renderer is a .NET class containing a static method that takes in an array of objects and returns a string. The runtime calls this renderer to format the objects into a string. To use this approach, in the TypeDescriptor element, you would use the RendererDefinition attribute to identify the method, class, and assembly of the custom renderer. Using a custom renderer is an expensive operation, because the renderer must be called on a per-item basis; because of this, you should generally only use a custom renderer when no other options are available. Just like format strings, custom renderers can only be used with Business Data Web Parts and will not work with external lists.

Another option is to create a custom field type. A custom field type defines a data type for a SharePoint list column, and provides a useful way of storing complex list data in a manageable way. You can also create custom field controls that interpret the data in the custom field type and render it in a user-friendly way. For example, you could create a custom field type that stores nested address data, together with a custom field control that displays the data in a flattened, list-friendly format. Custom field controls typically define two interfaces—one that presents the data, and one that allows the user to edit the data—so that the control works in both list view forms and list edit forms. . In the edit view, you can provide a user interface that allows the user to provide the field data in its nested format, thereby preserving the integrity of the underlying data. Custom field types and field controls offer the most flexible approach to working with complex data, and you can build in sophisticated behavior such as sorting and filtering. However, creating field types and field controls involves creating several classes and definition files, which makes them somewhat complicated to implement. For examples of how to create custom field types and field controls, see Creating Custom SharePoint Server 2010 Field Types and Field Controls with Visual Studio 2010 and Silverlight 3 on MSDN.

Finally, you can use a .NET connectivity assembly to manage the conversion between complex types and flat types. This is a powerful approach, because you can specify exactly how your data is flattened and unflattened for each stereotyped operation. The .NET connectivity assembly bridges the gap between the external system and the BDC model—the external system sees nested complex types, while the BDC model sees flattened data structures.

For more information about these approaches, see Working with Complex Data Types on MSDN.