BCS solutions always begin by defining External Content Types for the schema and operations. These definitions are nearly always created using SPD. SPD provides all the basic tooling necessary to create ECTs and External Lists. In addition, ECTs can be exported from SPD so that they can be migrated from a development environment to a quality assurance (QA) environment and then to a production environment. To begin creating an ECT, you open a SharePoint site in SPD and click the External Content Types object under the list of Site Objects, as shown in Figure 13-3. This produces a list of all the existing ECTs in the farm.

Figure 13-3. Displaying the available ECTs

Once you have a view of the available ECTs, you may define a new one by clicking the New External Content Type button on the Ribbon. The basic ECT information consists of a Name, Display Name, Namespace, and Version. You may also select from a list of various Office Types, which determines what form will be used to render the information when it is displayed in Outlook. Figure 13-4 shows the basic ECT information for the walkthrough with the Contact Office Type selected.

Figure 13-4. Basic ECT information

Once the basic ECT information is defined, you will define connection information for the External System. Clicking the Operations Design View button on the Ribbon presents a form for defining the connection information, as shown in Figure 13-5. From this form, clicking Add Connection allows you to select from three types of connections: WCF, SQL, and .NET Type. Selecting WCF allows you to connect to a Web service, SQL allows you to connect to a database, and .NET Type allows you to use a custom .NET Assembly Connector (which is covered in the section entitled Creating .NET Assembly Connectors later in this chapter).

When connecting to the External System, BCS supports a number of authentication mechanisms. You can connect as the current user, the SHAREPOINTSYSTEM account, transform credentials to another account, or even use claims-based access. In this walkthrough, we connect as the current user, which will work fine if the database is on the same server as SharePoint. In more realistic environments, other authentication schemes must be used, and they are covered later in this chapter.

Figure 13-5. Specifying connection information

Once the data source connection is made, SPD can create operations for the ECT. When using a SQL connection, SPD can infer a significant amount of information about the data source and the operations, so it is easier to create the entire set of create, read, update, and delete (CRUD) operations. In fact, all you have to do is right-click the table in the connection and select Create All Operations from the context menu, which will start the Operation Wizard to collect the small amount of information required to complete the operation definitions. Figure 13-6 shows the context menu in SPD.

Figure 13-6. Creating ECT operations

The Operation Wizard starts whenever SPD needs additional information to complete the operation definition. The information required typically includes a mapping of fields between the ECT and Outlook, identification of the primary key for the ECT, and the definition of filters to throttle the size of returned result sets. SPD displays errors and warnings throughout the wizard to guide you in correctly defining the operations.

After the operations are defined, the ECT should be saved. Saving the ECT writes the definition to the metadata catalog, where it becomes available to the entire farm. After the ECT is saved, it will appear in the list of External Content Types in SPD. From the list of External Content Types, you may also export the ECT definition as an Extensible Markup Language (XML) file. This XML file can subsequently be imported through the Business Data Connectivity service interface in Central Administration.

Once the ECT is created, it can be used as the basis for an External List. External Lists can be created directly in SPD or in the browser using the Create menu in SharePoint. For this walkthrough, a new External List was created directly from the summary page in SPD. Figure 13-7 shows the dialog for defining the list name and associating operations.

Figure 13-7. Creating an External List from SPD

Once the new External List is created, it may be viewed in the browser. Because all the CRUD operations were created, the resulting list supports editing, adding, and deleting items. Figure 13-8 shows the new list in SharePoint Server 2010. Note how the appearance of the External List closely resembles a standard SharePoint list. The Ribbon is functional, as well as the edit-control block (ECB) associated with individual items. Any changes to items in the list will be reflected immediately in the External System.

Figure 13-8. The External List

Just like “regular” lists, External Lists may be taken offline through both the SFW and Outlook. For this walkthrough, the ECT was defined as a contact item in Outlook. This means that Outlook will use the standard contact list to display the data when the Connect To Outlook button on the List tab of the Ribbon is clicked. When an External List is synchronized with Outlook, BCS delivers a Visual Studio Tools for Office (VSTO) package to the client for accessing the External System. Figure 13-9 shows the External System data in Outlook.

Figure 13-9. External data in Outlook

Clicking the Sync To SharePoint Workspace button on the List tab on the Ribbon in SharePoint will take the list offline as well. In a fashion similar to Outlook, a VSTO package will install, and then the list will be available in the SFW. Figure 13-10 shows the list in the SFW.

Figure 13-10. External data in SFW

BCS communicates with External Systems using connectors. Connectors contain the functionality necessary to communicate with databases, Web services, and other systems. The walkthrough presented earlier used the SQL connector to access a Microsoft SQL Server database, but BCS also supports a WCF connector for accessing Web services. The SQL and WCF connectors provide a lot of the functionality you will need for basic BCS solutions, but in more advanced cases, you may need to create a connector. When you create your own connector, you can create either a Custom connector or a .NET Assembly Connector.

You can build a Custom connector for connecting to systems other than databases and Web services. These connectors are built specifically for a certain type of system. For example, you could build a Custom connector for Microsoft Exchange Server. While Custom connectors can be created in Visual Studio 2010, there is no tooling support. This means that Custom connectors must be built up from a standard class library project. In most cases, Custom connectors will be created by third parties to target a specific system; the details of this process are beyond the scope of this chapter.

A .NET Assembly Connector is a project that you create in Visual Studio 2010 that contains the ECT definition and associated business logic for accessing a specific External System. The .NET Assembly Connector differs from the Custom connector because it targets a specific instance of a system, as opposed to all instances of a specific system type. In other words, you can use a .NET Assembly Connector to access a specific folder in Exchange Server while a custom connector could be used to access any folder in Exchange Server.

Developers are much more likely to create .NET Assembly Connectors than Custom connectors because they have tooling support in Visual Studio 2010. The .NET Assembly Connector is useful for implementing operations that are not supported by the SPD tooling, such as accessing document streams. .NET Assembly Connectors also support aggregating data from multiple sources into a single ECT and applying business rules to data before it is made available in SharePoint.

The Business Data Connectivity layer provides the plumbing and run-time components of BCS. These components are essentially the components that made up the Business Data Catalog in the Microsoft Office SharePoint Server (MOSS) 2007. In SharePoint 2010, Microsoft kept the “BDC” acronym for these components, but changed its definition from Business Data Catalog to Business Data Connectivity.

In SharePoint 2010, both the server and the client have BDC components to support the symmetry of operations on the client and the server. You can use a similar approach to creating BCS solutions whether you are focused on the server, client, or both. On the server, the BDC components consist of the ECT catalog and the BDC Server Runtime. On the client, the BDC components consist of a metadata cache and the BDC Client Runtime. The metadata cache can be thought of as the client-side metadata catalog, while the run-time components have symmetrical functionality to support operations against the External Systems.

When you create ECTs in SPD and save them, they are stored in the metadata catalog, which is a database accessed through the BDC service application. The BDC service application wraps the BDC and makes it available as a farm service so that ECTs can be used throughout the farm. Figure 13-12 shows the basic architecture of the BDC service application.

Figure 13-12. The BDC service application architecture

The BDC Service application wraps the BDC plumbing and makes BDC functionality available as a service. When External Systems are accessed through connectors, the returned data is made available by the BDC service application to support External Lists, searching, Web Parts, and so on. The metadata cache is maintained in the BDC service so that ECT data is accessed easily without having to read it from the database. This metadata cache is updated every minute by a timer job so that the latest changes are available. Note that External Data itself is never cached by the server—only the ECT metadata.

Along with caching metadata to improve performance, BCS implements limits on the number of connections that can be made to an External System. In addition, the BDC service application also implements five different throttle settings to limit the connections made and data returned from External Systems. Table 13-1 lists the throttle settings for the BDC service application.

Throttle values can be viewed and changed using Windows PowerShell commands. Before you can change them, however, you must get a reference to the BDC service application. The following Windows PowerShell code shows how to return a reference to the BDC service application named Business Data Connectivity.

$bdc = Get-SPServiceApplicationProxy | Where {$_ -match "Business Data Connectivity"}

Once you have a reference to the BDC service application, you can use the Get-SPBusinessDataCatalogThrottleConfig cmdlet and the Set-SPBusinessDataCatalogThrottleConfig cmdlet to view and change throttle settings. Each of these cmdlets requires you to specify the throttle that you are viewing or changing. The following code shows how to view the current throttle settings using Windows PowerShell.

Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Connections -Scope Global
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Items -Scope Database
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Timeout -Scope Database
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Size -Scope Wcf
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Timeout -Scope Wcf
                                        -ServiceApplicationProxy $bdc

When changing throttle settings, you must specify the new value in the Set-SPBusinessDataCatalogThrottleConfig cmdlet. New throttle settings take effect immediately. As a sample, the following code shows how to change the number of items that can be returned from a database.

$bdc = Get-SPServiceApplicationProxy | Where {$_ -match "Business Data Connectivity"}
$throttle = Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Items -Scope Database
                                                    -ServiceApplicationProxy $bdc
Set-SPBusinessDataCatalogThrottleConfig -Maximum 3000 -Default 1000 -Identity $throttle

Along with viewing or editing throttle values, you can disable them. However, disabling throttles is not something that should be done lightly. Disabling throttles can result in poor BCS performance and may affect the performance of the SharePoint farm as a whole. The following code shows how to disable the connection limit throttle.

$bdc = Get-SPServiceApplicationProxy | Where {$_ -match "Business Data Connectivity"}
$throttle = Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Connections
            -Scope Global -ServiceApplicationProxy $bdc
Set-SPBusinessDataCatalogThrottleConfig -Enforced $false -Identity $throttle

The BDC service application is part of the service application framework in SharePoint. As such, it functions like any of the other shared services in SharePoint. The management interface for the BDC service application is accessible through the Central Administration home page by selecting Application Management, Manage Service Applications. Figure 13-13 shows the BDC service application in Central Administration.

Figure 13-13. The BDC service application in Central Administration

From the Service Applications page, you can click the Properties button on the Ribbon and see the basic service properties for the BDC service application. In the Properties dialog, you will see the name of the database used for the ECT repository. This database is set up when the BDC service application is first created during farm installation and configuration. As with all services, you can also set administrative and connection permissions for the service so that it can be used by other servers in the SharePoint farm.

Clicking the Manage button on the Service Applications page will allow you to manage the ECTs in the repository. Here, you will see the ECTs that you have defined in SPD, along with information about the associated models and External Systems. Models may be imported and exported from this page, so you may export models from SPD in a development environment and import them into the BDC service application in QA or production environments.

Managing the BDC service application also allows you to set permissions for the various objects in your model. Users must have permissions to access the ECT and its operations before they will see data in SharePoint. This permission is separate from the actual permissions required to access an External System. There are four different rights available for an ECT: Edit, Execute, Selectable in In Clients, and Set Permissions. The Edit right grants the ability to edit models, data sources, and External Content Types. The Execute right grants the ability to perform CRUD operations. The Selectable in In Clients right grants the ability to create new External Lists, use the External Data Web Parts, and pick External Content Types from the various pickers that appear in SharePoint. The Set Permissions right grants the ability to set permissions in the BDC service.

The BDC Server Runtime consists of the run-time object model, the administration object model, and the security infrastructure. The run-time object model provides access to ECTs and their associated operations, while the administration object model provides objects for managing the ECTs and their associated models. The security infrastructure facilitates authentication and authorization for ECT operations and External System access.

BCS uses a client cache to store information from the ECT repository so that Office client applications can access External Systems directly. The client cache is a SQL Server Compact Edition (SQLCE) database that is installed as part of the Office 2010 installation. A synchronization process called BCSSync.exe runs on the client to synchronize the cache with model information in the BDC layer. When operations are performed on data within the Office clients, the operations are queued inside the client cache and synchronized with the External System when it is available. The synchronization process will also attempt to update data in the cache at various intervals from the External System depending on the user settings and availability of the External System. Conflicts between the cache and the External System are flagged for the user so that they may be resolved. When clients access External Systems, they always use the information in the cache to access the External System directly. There is never any case where the client application accesses the External System through the server-side components.

The BDC Client Runtime, which is also called the Office Integration Runtime (OIR), is the client-side component that compliments the BDC Server Runtime. Like the server-side component, the BDC Client Runtime is responsible for the plumbing and functionality necessary to execute operations against an External System and bind the data to clients like Outlook. The BDC Client Runtime is installed on the client as part of the Office 2010 installation process just like the cache. This means that all Office 2010 client installations will support BCS functionality.

The Secure Store Service (SSS) is a service application that provides for the storage, mapping, and retrieval of credential information. It is used in authentication scenarios where the user account is either not available or not supported by the External System. To store credential sets for an External System, a new Target Application must be created in SSS. The Target Application acts as a container for credential sets mapped to an External System. The Target Application settings page contains a name for the application and a setting to specify whether each individual user will have a separate set of mapped credentials or whether every user will map to a single common set of credentials. Figure 13-14 shows application settings mapping a single set of credentials to an Active Directory Domain Services (AD DS) group.

Figure 13-14. Creating a new Target Application in SSS

After the Target Application is defined, credential fields are defined to specify what credentials are required to access the External System. In most cases, the Target Application will save a Windows user name and password, but you could also map credentials for non-Windows authentication schemes. SSS also supports ideas such as personal identification numbers (PINs) for credentials. Figure 13-15 shows the field definition form for a Target Application.

Figure 13-15. Defining credential fields

Once the application and credential fields are defined, you must enter the actual credential information for a given user. For each user or group that will access the External System, a set of credentials is stored in SSS. If a user should attempt to access the system without proper credentials in SSS, then that person will be directed to a logon page so the credentials can be entered and stored just-in-time. Once the credentials are mapped, you may specify the name of the Target Application in SPD during ECT creation. When specified in this way, BCS will use the SSS credentials to access the External System. Figure 13-16 shows a dialog for setting the credentials used in a group mapping. In this case, all users are utilizing a single account.

Figure 13-16. Mapping group credentials to a single account

The credentials stored in SSS are accessible through a set of objects designed to support your custom solutions. Using these objects, you can create solutions, such as Web Parts, that use SSS credentials to gain access to External Systems. The Microsoft.Office.SecureStoreService assembly contains the main classes necessary to work with SSS. In addition, the Microsoft.BusinessData assembly contains supporting classes for working with BCS.

The general approach to retrieving SSS credentials in code involves getting a reference to the SSS service application through the ISecureStoreProvider class. The GetCredentials method may then be called with the name of the Target Application to return the credentials. Example 13-1 shows how a Web Part can use this approach to build a connection string for an External System.

protected override void OnPreRender(EventArgs e) {
  string username = string.Empty;
  string password = string.Empty;

  try {
    ISecureStoreProvider p = SecureStoreProviderFactory.Create();
    using (SecureStoreCredentialCollection creds =
               p.GetCredentials(ApplicationId)) {

      // enumerate through all credentials
      foreach (SecureStoreCredential c in creds) {
        switch (c.CredentialType) {
          case SecureStoreCredentialType.UserName:
            username = ConvertToString(c.Credential);
            break;

            case SecureStoreCredentialType.Password:
              password = ConvertToString(c.Credential);
              break;

            case SecureStoreCredentialType.WindowsUserName:
              username = ConvertToString(c.Credential);
              break;

            case SecureStoreCredentialType.WindowsPassword:
              password = ConvertToString(c.Credential);
              break;
        }
      }
    }

    SqlConnectionStringBuilder cBuilder = new SqlConnectionStringBuilder();
    cBuilder.DataSource = ServerName;
    cBuilder.InitialCatalog = DatabaseName;
    cBuilder.UserID = username;
    cBuilder.Password = password;

    messages.Text = cBuilder.ConnectionString;

  }
  catch (Exception x) {
    messages.Text = x.Message;
  }
}

private String ConvertToString(SecureString s) {
  IntPtr b = Marshal.SecureStringToBSTR(s);
  try { return Marshal.PtrToStringBSTR(b); }
  finally { Marshal.FreeBSTR(b); }
}

When a user elects to synchronize an External List with Outlook or the SFW, BCS creates a VSTO Click-Once deployment package that contains all the elements necessary to work with the list on the client. The package is created by BCS just-in-time and stored under the list in a folder named ClientSolution. After the package is created, the deployment is started automatically.

The package contains the BCS model defining the External System, ECTs, operations, and security information that is necessary to access and modify data. The package also contains subscription information, which tells the client cache what data to manage and how it should be refreshed. Finally, the package contains pre- and post-deployment steps that should be taken, such as creating custom forms in the client application to display the data.

Once deployed, the add-in can use Office Business Parts on the client to help render data. Office Business Parts are Windows form controls that display a single item or list of items in a task pane to simplify the rendering process so that custom task panes do not have to be created for the client.

BCS supports two authentication models: Trusted Subsystem and Impersonation and Delegation. In the Trusted Subsystem model, BCS uses a single account to access the External System regardless of the user identity. Under Impersonation and Delegation, BCS attempts to impersonate the user and access the External System. The AuthenticationMode element in the BDC Metadata Model determines how authentication is performed and has several different options.

<LobSystemInstances>
  <LobSystemInstance Name="Adventureworks Data Warehouse">
    <Properties>
      <Property Name="AuthenticationMode" Type="System.String">
        PassThrough
      </Property>
      <Property Name="DatabaseAccessProvider" Type="System.String">
        SqlServer
      </Property>
      <Property Name="RdbConnection Data Source" Type="System.String">
        AWSQL
      </Property>
      <Property Name="RdbConnection Initial Catalog"
                Type="System.String">AdventureworksDW</Property>
      <Property Name="RdbConnection Integrated Security" Type="System.String">
        SSPI
      </Property>
      <Property Name="RdbConnection Pooling" Type="System.String">true</Property>
    </Properties>
  </LobSystemInstance>
</LobSystemInstances>

Understanding RevertToSelf

RevertToSelf is used in the Trusted Subsystem model of authentication. Setting the value of the AuthenticationMode element to RevertToSelf causes BCS to use the credentials of the application pool to access the External System. The following code shows a BDC Metadata Model using RevertToSelf authentication.

<LobSystemInstances>
  <LobSystemInstance Name="Adventureworks Data Warehouse">
    <Properties>
      <Property Name="AuthenticationMode" Type="System.String">
        RevertToSelf
      </Property>
      <Property Name="DatabaseAccessProvider" Type="System.String">
        SqlServer
      </Property>
      <Property Name="RdbConnection Data Source" Type="System.String">
        AWSQL
      </Property>
      <Property Name="RdbConnection Initial Catalog"
                Type="System.String">AdventureworksDW</Property>
      <Property Name="RdbConnection Integrated Security" Type="System.String">
        SSPI
      </Property>
      <Property Name="RdbConnection Pooling" Type="System.String">true</Property>
      <Property Name="ShowInSearchUI" Type="System.String"></Property>
    </Properties>
  </LobSystemInstance>
</LobSystemInstances>

Configuring RevertToSelf is accomplished by editing the connection information for the External System after it is defined. In the SharePoint Designer, on the Summary View for the ECT, the connection information may be edited by clicking the hyperlink for the External System. Figure 13-17 shows the Connection Properties dialog. RevertToSelf is specified by selecting the BDC Identity option under Authentication Mode.

Figure 13-17. Using the BDC identity to access an External System

Using RevertToSelf authentication eliminates the double-hop issue because BCS is no longer attempting to impersonate the user all the way to the External System. The drawback to this approach, however, is that all access is accomplished using the same account. As a result, no auditing of individual activities against the External System is possible.

In addition to the limitations imposed by RevertToSelf authentication, it is important to understand that the application pool identity is a powerful one whose credentials must be protected. Along with being the account under which the Web application runs, the application pool identity is used to access the content database, as mentioned earlier. Furthermore, the application pool identity is the account under which code runs when the SPSecurity.RunWithElevatedPrivileges method is called in SharePoint, which essentially allows code to perform any action in a SharePoint farm. For this reason, RevertToSelf is disabled by default and must be enabled explicitly using the following Windows PowerShell script.

$bdc = Get-SPServiceApplication
 | where {$_ -match "Business Data Connectivity Service"}
$bdc.RevertToSelfAllowed = $true
$bdc.Update;

Claims authentication is a new form of authentication available in SharePoint 2010; it is covered in detail in Chapter 12. Claims authentication overcomes the current limitations of multiple user repositories and centralized user repositories by moving the task of authentication out of the application altogether. Under a claims authentication model, applications no longer need to worry about querying a user repository. Instead, the user arrives at the application with authentication already completed.

Because SharePoint 2010 supports claims authentication, BCS can also use claims to authenticate against External Systems. To implement claims authentication, the External System must support claims and trust the claims provider used with SharePoint. Currently, there are few systems that support claims authentication, but the number will increase over time. A likely scenario today involves a custom Windows Communication Foundation (WCF) service that implements claims authentication.

To implement claims authentication, the AuthenticationMode should be set to Passthrough. None of the other configuration really makes sense because claims authentication is based on delegating the user’s identity. For the most part, claims-based authentication happens automatically, provided that the External System accepts the token offered by BCS. In the case where SharePoint is operating in a “claims aware” mode, but the External System is not, the correct approach is to use SSS to transform credentials.

Today, many Web-based applications use a token-based authentication system. These systems typically have a logon mechanism that is separate from the applications that they support. For example, Window Live has a logon system that uses a Windows Live ID that is used for many applications including HotMail, SkyDrive, and Live Mesh. Regardless of the application, however, users always use the same logon screen to authenticate and receive a token that is trusted by the applications.

BCS can support authentication against token-based systems, but it requires the creation of a custom SSS provider designed to work with the particular token system in use. In addition, a custom handler must be created to redirect users to the appropriate logon page for the system. The SharePoint software development kit (SDK) contains more information on this approach, which is beyond the scope of this chapter.

BCS supports a wide variety of operations designed to facilitate accessing systems and performing CRUD functions. Generally, you will be concerned with basic reading and writing to External Systems using Finder (Read List), SpecificFinder (Read Item), Creator (Create), Updater (Update), and Deleter (Delete) methods. These methods are also supported in SPD through menus in the Operations Design view. Methods that are not supported by SPD offer additional functionality and control, but they must be created by manually editing the BDC Metadata Model or creating a .NET Assembly Connector. Manually editing the XML model requires that you export the model, edit it, and import the new model. Table 13-2 lists all the supported BCS operations.

Finder methods are used to return a result set from the External System and are one of two required operations for External Lists. You can create a Finder method in SPD by selecting to create a New Read List operation. Example 13-3 shows the definition of a Finder method.

<Method Name="Read List" DefaultDisplayName="Product Read List">
  <Properties>
    <Property Type="System.Data.CommandType, [assembly name for System.Data]"
              Name="RdbCommandType">Text</Property>
    <Property Name="RdbCommandText" Type="System.String">
      SELECT TOP(@ProductID) [ProductID] , [ProductName]
      FROM [dbo].[Products] ORDER BY [ProductID]
    </Property>
    <Property Name="BackEndObjectType"
              Type="System.String">SqlServerTable</Property>
    <Property Name="BackEndObject" Type="System.String">Products</Property>
    <Property Name="Schema" Type="System.String">dbo</Property>
  </Properties>
  <Parameters>
    <Parameter Direction="In" Name="@ProductID">
      <TypeDescriptor TypeName="System.Int64" AssociatedFilter="Filter"
                      Name="ProductID">
        <DefaultValues>
          <DefaultValue MethodInstanceName="Read List"
                        Type="System.Int64">100</DefaultValue>
        </DefaultValues>
      </TypeDescriptor>
    </Parameter>
    <Parameter Direction="Return" Name="Read List">
      <TypeDescriptor
         TypeName="System.Data.IDataReader, [assembly name for System.Data]"
         IsCollection="true" Name="Read List">
        <TypeDescriptors>
          <TypeDescriptor
            TypeName="System.Data.IDataRecord, [assembly name for System.Data]"
            Name="Read ListElement">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Int32" ReadOnly="true"
                              IdentifierName="ProductID" Name="ProductID" />
              <TypeDescriptor TypeName="System.String" Name="ProductName">
                <Properties>
                  <Property Name="Size" Type="System.Int32">50</Property>
                  <Property Name="RequiredInForms"
                            Type="System.Boolean">true</Property>
                  <Property Name="ShowInPicker"
                            Type="System.Boolean">true</Property>
                </Properties>
                 ...
    </Parameter>
  </Parameters>
  <MethodInstances>
    <MethodInstance Type="Finder" ReturnParameterName="Read List" Default="true"
                    Name="Read List" DefaultDisplayName="Product Read List">
      <Properties>
        <Property Name="UseClientCachingForSearch" Type="System.String"></Property>
        <Property Name="RootFinder" Type="System.String"></Property>
        <Property Name="LastModifiedTimeStampField"
                  Type="System.String">LastUpdate</Property>
      </Properties>
    </MethodInstance>
  </MethodInstances>
</Method>

In the definition for the Finder method, SPD automatically generates a SQL query to retrieve items for display in the list if the External System is a database. This is done when the methods are created in the wizard. If you want, you can use stored procedures or views instead of dynamic SQL. Also, note how the return parameters are defined so that BCS understands the data returned from the External System. In particular, note the use of the TypeDescriptor element. TypeDescriptor is used to map data types in the External System to .NET data types in BCS.

You can create multiple Finder methods, but one will always be designated as the default. The default Finder method forms the basis of the default view of an External List and provides support for indexing the External System so it can be searched. SPD automatically adds a RootFinder property to the default Finder method. This property is used when indexing the External System to specify the records in the External System that should be indexed. In addition, the method can designate a timestamp field to support incremental crawls. Designating a field as a timestamp is done in the Return Parameters section of the Operation Wizard and appears in the BDC Metadata Model as a LastModifiedTimeStamp property.

SpecificFinder methods are used to return a single item from the External System and are also required to support External Lists. Creator, Updater, and Deleter methods are optional for External Lists. All the methods have similar XML structures in the BDC Metadata Model. You can examine these structures easily by creating models and exporting them from SPD.

BCS supports the definition of relationships between entities, which allows you to display relationships and navigate between entities within SharePoint. Within the SharePoint Designer, one-to-many, self-referential, and reverse associations are supported by the tooling. The most common type of association in BCS solutions is the one-to-many association, whereby a parent entity instance is related to many child entity instances. Self-referential associations are just like one-to-many relationships, except that a self-referential relationship uses the same ECT as both the parent and the child. Reverse associations return a single parent entity instance for a child entity instance. Reverse associations are not supported for tables and views, but they are supported for stored procedures and Web services because the reverse association is not inherent in the database schema. It must be programmed explicitly through a stored procedure or Web service.

To create a relationship, you select New Association from the context menu in the Operations Design view. This will start a wizard to help you define the new association. The wizard will ask you to select another ECT with which to make the association. If the ECTs are based on related tables in a database, then SPD will infer the relationship using the foreign key. If not, then you will have to specify the relationship manually by associating fields from the parent to child ECT. Example 13-4 shows a relationship between a Product entity and a Category.

<Method IsStatic="false" Name="CategoryAssociation">
  <Properties>
    ...
  </Properties>
  <Parameters>
    <Parameter Direction="In" Name="@CategoryId">
      <TypeDescriptor ... />
    </Parameter>
    <Parameter Direction="Return" Name="CategoryAssociation">
      <TypeDescriptor ...>
        <TypeDescriptors>
          ...
        </TypeDescriptors>
      </TypeDescriptor>
    </Parameter>
  </Parameters>
  <MethodInstances>
    <Association Name="CategoryAssociation" Type="AssociationNavigator"
     ReturnParameterName="CategoryAssociation"
     DefaultDisplayName="Category Association">
      <Properties>
        <Property Name="ForeignFieldMappings" Type="System.String">
        ... ForeignFieldMapping ForeignIdentifierName="CategoryId" ...
        </Property>
      </Properties>
      <SourceEntity Namespace="http://www.wingtip.com" Name="Category" />
      <DestinationEntity Namespace="http://www.wingtip.com" Name="Product" />
    </Association>
  </MethodInstances>
</Method>

When creating Finder and SpecificFinder methods, you quite often want to limit the information that is returned from the External System. You may want to limit the returned data simply to prevent a large amount of data from being requested, support conditional queries, paging, or wildcards. The Application Model supports all these types of filters. Filters can also be thought of as input parameters to an ECT operation. Generally, their values are set by the calling client before the operation is invoked. The wizards in SPD will help you define the most common filters when you are creating ECTs. Table 13-3 lists all the filters supported in BCS.

Whenever you are creating Finder and SpecificFinder methods, you should define a Limit filter for the operation. This filter ensures that large result sets are not returned to an External List and are critical for maintaining BCS performance. While BCS does implement throttling at the system level, the ECT should implement its own tighter limits to ensure query performance is maintained.

Defining filters in SPD is done in the Operation Wizard on the Filter Parameters Configuration page. On this page, you may click Add Filter Parameter to add a new filter. After adding a new filter, you must then click the Filter hyperlink to open the Filter Configuration dialog. Figure 13-18 shows the Filter Configuration dialog within the Operation Wizard.

Figure 13-18. Defining filters

Although External Lists have several limitations, they also have many of the same capabilities as a standard list. When created from SPD, for example, you may also select to create an InfoPath form for editing items by clicking the Create Lists And Form button. Creating an InfoPath form allows you to customize the appearance of the form, as well as add validation logic.

Once you have created the InfoPath form, it may be edited by clicking the Design Forms In InfoPath button, which is available on the List Settings tab. Clicking this button will open the form in InfoPath, where you will have complete control over the appearance and functionality of the form. Figure 13-19 shows a simple item edit form that has been modified in InfoPath.

Figure 13-19. An External List form in InfoPath

In addition to creating InfoPath forms, you may also create new ASPX forms for External Lists. The default forms created for the External List use the List Form Web Part (LFWP). The LFWP executes Collaborative Application Markup Language (CAML) queries against the External List to display items. Unfortunately, the LFWP does not support modifying its presentation; therefore, a new form must be created instead.

Clicking the New button above the form list in SPD opens the Create New List Form dialog. This dialog is used to create new, edit, and display forms that are based on the Data Form Web Part (DFWP). The DFWP uses Extensible Stylesheet Language for Transformations (XSLT) to transform list data into a display. Modifying this XSLT can change the presentation of list data easily.

Along with using an ECT as the basis for a list, you can use an ECT as the source for a column in another list. This capability is known as an External Data Column. When you create an External Data Column for a list, you select the ECT to use as the basis for the column. You may then select one or more of the fields available in the ECT to display alongside the column you are creating. These additional fields are known as projected fields because they project data from the ECT into the parent list. Figure 13-20 shows an external column definition.

Figure 13-20. Defining an external column

Another way you can use ECTs is through a set of Web Parts that ship with SharePoint Server 2010 known as External Data Web Parts (also called Business Data Web Parts). External Data Web Parts are designed specifically to display ECT data and relationships. The available parts include the Business Data List, Business Data Related List, and Business Data Item. These Web Parts display a list based on an ECT, a list based on an ECT association, or a single item, respectively.

The Business Data List part allows you to select an ECT, and then it displays a list of data based on a Finder method that you specify. In many ways, this Web Part is like an External List. You can, for example, modify the view by selecting which columns to display. If you have filters defined for the Finder method, these can be used to turn simple queries against the list to change the view. Finally, you can change the appearance of the list by altering the XSLT contained within the Web Part. This XSLT is used to transform the data returned for display in the Web Part.

The Business Data Related List is meant to be used in conjunction with the Business Data List to show data based on an association between two ECTs. After selecting an ECT for both the Business Data and Business Data Related lists, you can use the Web Part menu to connect the two lists. Once connected, the Business Data List Web Part acts as a filter against the Business Data Related List Web Part. This gives users a simple way to filter the list view by clicking items in the related list.

The Business Data Item Web Part is used to display a single record based on an ECT. This Web Part is configured by first selecting the ECT and then selecting the particular record to display. This Web Part is especially useful when combined with the Business Data Item Builder Web Part, which builds a business item from query string parameters in the page Uniform Resource Locator (URL). This combination of the Business Data Item Builder Web Part and Business Data Item Web Part is used by BCS to create a profile page for an ECT. Profile pages are discussed in the next section. Figure 13-21 shows the Business Data List, Business Data Related List, and Business Data Item Builder Web Parts on a page.

Figure 13-21. Business Data Web Parts

When SharePoint surfaces ECT data in lists and Web Parts, it does not necessarily show all the available fields and associations. For example, when an ECT is used as the source for an external column, only a single field is required for display. When users see partial ECT data, however, they are quite often interested in being able to see the data behind it. This is where profile pages enter the picture. A profile page is a dedicated page that shows all the ECT data for a specific record. This way, users can jump from partial ECT data to a complete view of the record.

The Business Data Item Builder and Business Data Item Web Parts are deployed onto a dedicated profile page. The profile page is typically accessed through an action. An action is defined as a hyperlink containing query string parameters that can be used by the Business Data Item Builder Web Part to construct the profile page. Actions are often surfaced as a drop-down menu associated with the displayed ECT data. Figure 13-22 shows a profile page.

Figure 13-22. A BCS profile page

Before you can create profile pages, they must be enabled through the BDC service application. On the Edit tab for the BDC service application, you can click the Configure button in the Profile Pages group. In the Properties dialog that opens, you must specify a SharePoint site where the profile pages can be created. After that, you can simply select ECTs and click the Create/Upgrade button to make profile pages for the ECTs that you select. You can also make profile pages in SPD when you are designing your ECT.

ECTs created with SPD already support indexing by SharePoint Search with no additional work. However, External Systems will be indexed only if you explicitly set up a content source that includes the ECT. Content sources can be created within the Search service application, where you will have the option to create a content source associated with an External System.

When you select to create a new content source from an ECT, you will be presented with a drop-down list of the available BDC service applications. When you choose a BDC service application, you will then have the option to index all External Systems associated with the selected service or to pick particular systems. Figure 13-23 shows the content source creation options.

Figure 13-23. Defining a content source

After a content source is created and crawled, it may be used in the standard ways. This means that you may simply go to the Search Center, type a keyword, and return records from the External System. These results include a hyperlink to the profile page so that users can see the full details of the returned records. You may also set up search scopes and use them to search only the External System data.

The User Profile service application is used to synchronize data from AD DS to the profile database maintained by SharePoint. The profile database contains rich information about users that can be displayed in sites. The User Profile service application maps AD DS fields to fields in the user’s profile. On a scheduled basis, this information is imported from AD DS.

In much the same way that you can add search connections to External Systems through ECTs, you can add profile synchronization connections. Adding a new synchronization connection allows you to use data from External Systems to supplement the data in the profile system. This is often useful in organizations that maintain a Human Resources (HR) system but do not have rich data in their AD DS system. In such cases, ECTs are designed against the HR system and mapped to fields in the profile database. Figure 13-24 shows a new connection being created in the User Profile service application.

Figure 13-24. Connecting BCS with user profiles

SPW is an Office 2010 client designed to be the main application for managing SharePoint data offline. Using SPW, users can select to synchronize sites, lists, and libraries between their client and the SharePoint server. SPW was formerly known as Groove and still supports all the peer-to-peer capabilities of that product.

SPW has a simple interface that allows for synchronization to be initiated either through the browser or the SPW client. In the browser, users can synchronize a single list or an entire site. Clicking the Sync To SharePoint Workspace button on the List tab causes SPW to synchronize a list. Clicking the same button on the Site Actions menu causes SPW to synchronize an entire site. The SPW client contains a Launchbar that is accessible from an icon in the tray. The Launchbar lists the available sites and allows you to specify new sites to synchronize by providing a URL. Figure 13-25 shows the Launchbar.

Figure 13-25. The SPW Launchbar

SPW also contains a Workspace Window that can be opened to show all the available lists and libraries on a site. In the walkthrough earlier in the chapter, the Workspace Window was used to view an External List. You can use the Workspace Window to work with both lists and library documents, make changes, and synchronize them with the server. When documents are added to the workspace, SPW will upload them to the server if it is online. If the server is offline, then documents are queued for upload in the Upload Center. The Upload Center presents the status of document upload and is accessible through an icon in the tray. Figure 13-26 shows the Upload Center.

Figure 13-26. The Upload Center

You can manage the synchronization permissions through the SharePoint site. Site Collection administrators can use the standard permission settings to control who has access to the site. An additional option on the Site Settings page allows the administrator to set whether a particular site is available for offline use.

While SPW is a powerful client for managing SharePoint sites, many users prefer to have data available to them in Outlook as well. Lists may be synchronized with Outlook by clicking the Connect To Outlook button on the List tab. Just like SPW, Outlook allows users to work with data offline and then synchronize it with SharePoint later.

When synchronizing External Lists, ECTs can use Outlook forms by explicitly declaring that they should be displayed as an appointment, contact, task, or post when they are designed in SPD. Selecting to display an ECT as a particular type of Office item requires that External System fields be mapped to Outlook fields in the SPD wizard. Generally, the SPD wizard will prompt for the correct mapping through messages. This mapping ensures that the data is displayed correctly inside Outlook.

When you synchronize lists to Outlook, a VSTO solution is installed for working with the items. While the synchronization behavior works out of the box, you could choose to enhance the overall solution with your own VSTO solutions, which could be a full-blown custom VSTO solution created in Outlook or a special declarative solution unique to BCS. Creating these advanced custom VSTO solutions is beyond the scope of this chapter.

When you choose to create an external column for a document library, this column will show in Word in the Document Information Panel (DIP) at the top of the document. The DIP is designed to present metadata information so that it can be filled in during the document creation process, as opposed to prompting for metadata values when the document is saved.

In conjunction with viewing the metadata values in the DIP, document templates can also use Quick Parts. Quick Parts in Word allow you to insert fields into the document template that are bound to the metadata fields of the document. When a user fills in the field as part of the document creation process, the metadata values are set automatically. Adding Quick Parts to a document is done by selecting the appropriate metadata field from the Quick Parts list, which appears on the Insert tab in Word.

While Quick Parts work well with all manner of document metadata, they work especially well with ECTs. This is because the Quick Parts will surface a picker dialog for metadata that is based on an ECT. This makes it easy for users to select valid values for the metadata while improving the document creation experience. Figure 13-27 shows a document with a Quick Part based on an ECT. In the image, you can see the Quick Part field, the picker dialog, and the Quick Part list on the Insert tab.

Figure 13-27. Quick Parts in a document

The BDC Server Runtime and BDC Client Runtime are the object models used for manipulating External Data. Using the object models, you can perform full CRUD operations on External Data through custom code. This is the programming interface used by External Lists and Outlook, which means you can create custom Web Parts, pages, and add-ins for SharePoint and Office clients.

Using the object models requires you to set references in Visual Studio 2010 to the appropriate assemblies where the programming interface is defined. Selecting the correct assemblies is first a matter of deciding whether you are creating a server-side customization or a client-side customization. For server-side customizations, you will need to set references to the assemblies Microsoft.BusinessData.dll and Microsoft.SharePoint.dll. For client-side customizations, you will need to set references to Microsoft.BusinessData.dll and Microsoft.Office.BusinessApplications.Runtime.dll.

After you set references to the appropriate assemblies, the first challenge is to connect to the appropriate catalog. If you are on the server, then you will connect to the metadata catalog associated with the BDC service application. If you are on the client, then you will connect to the client cache.

Connecting to the metadata catalog on the server can be done with or without a SharePoint context, but the code will be different. In any case, you must get a reference to BdcServiceApplicationProxy, which can then be used to connect with the metadata catalog, which is represented by the DatabaseBackedMetadataCatalog object. If your code is running with a SharePoint context, then the following code will connect to the metadata catalog.

BdcServiceApplicationProxy p =
      (BdcServiceApplicationProxy)SPServiceContext.Current.GetDefaultProxy(
      typeof(BdcServiceApplicationProxy));
DatabaseBackedMetadataCatalog catalog = sap.GetDatabaseBackedMetadataCatalog;

If your code is running outside a SharePoint context, then you will need additional code to connect with BdcServiceApplicationProxy. The following code shows how to create a LINQ query to return the application proxy.

SPFarm farm = SPFarm.Local;
SPServiceProxyCollection spc = farm.ServiceProxies;
BdcServiceApplicationProxy sap =
(BdcServiceApplicationProxy)((from sp in spc
                              where sp.TypeName.Equals("Business Data Connectivity")
                              select sp).First.ApplicationProxies.First);
DatabaseBackedMetadataCatalog catalog = sap.GetDatabaseBackedMetadataCatalog;

In addition to using the BdcServiceApplicationProxy object to establish context, you may use the Microsoft.SharePoint.BusinessData.SharedService.BdcService class. The BdcService class is an abstraction of the BDC Service Application, which is useful for determining whether or not a BDC Service Application is available in the farm. The following code shows how to connect to the metadata catalog.

BdcService service = SPFarm.Local.Services.GetValue<BdcService>;
  if (service == null)
    throw new Exception("No BDC Service Application found.");
DatabaseBackedMetadataCatalog catalog =
  service.GetDatabaseBackedMetadataCatalog(SPServiceContext.GetContext(site));

If your code is running on the client, then you will connect to the client cache instead of the metadata catalog. The client cache is represented by the RemoteSharedFileBackedMetadataCatalog object. The following code shows how to make the connection.

RemoteSharedFileBackedMetadataCatalog catalog = new RemoteSharedFileBackedMetadataCatalog;

Once you make a connection to the appropriate catalog, you can read or write to the entities that it contains. These changes will be reflected in the External System, as well as any External Lists based on the ECT. Example 13-5 shows how to retrieve an entity and print the values of its fields using a Finder method.

IEntity ect = catalog.GetEntity("http://www.wingtip.com/products", "Product");
ILobSystem lob = ect.GetLobSystem;
ILobSystemInstance lobi = lob.GetLobSystemInstances["Wingtipdb"];
IFilterCollection filter = ect.GetDefaultFinderFilters;
IEntityInstanceEnumerator ects = ect.FindFiltered(filter, lobi);
while (ects.MoveNext) {
  Console.WriteLine(ects.Current["ProductName"].ToString);
}

If the Finder method defines filters (such as a limit, wildcard, or page filter), then these values must be provided in the call to the FindFiltered method. An IFilterCollection instance can be returned by calling the GetFilters method of the IMethodInstance. The values for the filters may then be set. The following code shows how to get the filter collection and set values.

IMethodInstance mi =
  ect.GetMethodInstance(FinderMethodInstanceName, MethodInstanceType.Finder);

IFilterCollection filters = mi.GetFilters;
(filters[0] as LimitFilter).Value = 10;
(filters[1] as PageNumberFilter).Value = 2;
(filters[3] as WildcardFilter).Value = "Bike";
(filters[4] as ComparisonFilter).Value = "CN123720";

Calling SpecificFinder is done through the FindSpecific method. When calling the FindSpecific method, you will always provide an Identity object, which represents the identifier for the desired entity instance. Simply create a new Identity object using the appropriate value and pass the object as an argument. Identity objects can be created with any data type, but be aware that String values are case-sensitive when used as Identifiers. The following code shows how to call the FindSpecific method.

//Connect to BDC Service Application
BdcService service = SPFarm.Local.Services.GetValue<BdcService>;

if (service != null) {
//Get Metadata elements
  DatabaseBackedMetadataCatalog catalog =
    service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
  IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
  ILobSystem lob = ect.GetLobSystem;
  ILobSystemInstance lobi =
    lob.GetLobSystemInstances[LobSystemInstanceName];
}

//Execute SpecificFinder
int id = 5;
IMethodInstance mi = ect.GetMethodInstance(SpecificFinderMethodInstanceName,
                                           MethodInstanceType.SpecificFinder);
IEntityInstance item =
   ect.FindSpecific(new Identity(id), SpecificFinderMethodInstanceName, lobi, true);

To invoke an Updater method, you first use the FindSpecific method to return the entity to update. The field values of the return entity may then be modified, and those modifications are committed through the Update method of the IEntityInstance interface. To invoke a Deleter method, you first use the FindSpecific method to return the entity instance to delete. The entity instance may then be deleted using the Delete method of the IEntityInstance interface.

Along with reading or updating entities, you can create new ones. As with other operations, these changes will flow all the way back to the External System. Of course, if you are writing to the client cache, the changes will be made only when the client is online. Example 13-6 shows how to add a new record to an External System through the ECT.

IView v = ect.GetCreatorView("Create");
IFieldValueDictionary dict = v.GetDefaultValues;
dict["ProductName"] = "New Toy";
dict["LastUpdate"] = DateTime.Today;
Identity id = ect.Create(dict, lobi);

Along with the Runtime Object Model, BCS has an Administration Object Model. The Administration Object Model allows you to manipulate the metadata for an Application Model. To work with the Administration Object Model, you must set references to Microsoft.BusinessData.dll and Microsoft.SharePoint.

As with the Runtime Object Model, you must first connect to the appropriate catalog before you can manipulate the data. In the case of the Administration Object Model, you must connect to the AdministrationMetadataCatalog object. Connecting to this catalog requires a reference to BdcServiceApplicationProxy, just as it did with the Runtime Object Model. Example 13-7 shows how to connect to the catalog if your code is running outside a SharePoint context. Inside the context, you can use the SPServiceContext object as discussed previously.

SPFarm farm = SPFarm.Local;
SPServiceProxyCollection spc = farm.ServiceProxies;
BdcServiceApplicationProxy sap =
  (BdcServiceApplicationProxy)
    ((from sp in spc
      where sp.TypeName.Equals("Business Data Connectivity")
      select sp).First.ApplicationProxies.First);

AdministrationMetadataCatalog catalog = sap.GetAdministrationMetadataCatalog;

The Administration Object Model provides a set of objects that allow you to manipulate the Application Model XML. The names of the objects correspond closely with the names of the elements in the Application Model. Example 13-8 shows a complete example of creating a simple Application Model from code and saving it into the metadata catalog.

Model model = Model.Create("MiniCRM", true, catalog);
LobSystem lob =
  model.OwnedReferencedLobSystems.Create("Customer", true, SystemType.Database);
LobSystemInstance lobi = lob.LobSystemInstances.Create("MiniCRM", true);

lobi.Properties.Add("AuthenticationMode", "PassThrough");
lobi.Properties.Add("DatabaseAccessProvider", "SqlServer");
lobi.Properties.Add("RdbConnection Data Source", "CONTOSOSERVER");
lobi.Properties.Add("RdbConnection Initial Catalog", "MiniCRM.Names");
lobi.Properties.Add("RdbConnection Integrated Security", "SSPI");
lobi.Properties.Add("RdbConnection Pooling", "true");

Entity ect = Entity.Create("Customer", "MiniCRM", true,
                           new Version("1.0.0.0"), 10000,
                           CacheUsage.Default, lob, model, catalog);

ect.Identifiers.Create("CustomerId", true, "System.Int32");

Method specificFinder =
  ect.Methods.Create("GetCustomer", true, false, "GetCustomer");

specificFinder.Properties.Add("RdbCommandText",
   "SELECT [CustomerId] ,[FullName] " +
   "FROM MiniCRM.Names " +
   "WHERE [CustomerId] = @CustomerId");

specificFinder.Properties.Add("RdbCommandType", "Text");

Parameter idParam =
  specificFinder.Parameters.Create("@CustomerId", true, DirectionType.In);

idParam.CreateRootTypeDescriptor(
    "CustomerId", true, "System.Int32", "CustomerId",
     new IdentifierReference("CustomerId",
        new EntityReference("MiniCRM", "Customer", catalog), catalog),
     null, TypeDescriptorFlags.None, null, catalog);

Parameter custParam =
  specificFinder.Parameters.Create("Customer", true, DirectionType.Return);

TypeDescriptor returnRootCollectionTypeDescriptor =
    custParam.CreateRootTypeDescriptor(
        "Customers", true,
        "System.Data.IDataReader, [full assembly name for System.Data]",
        "Customers", null, null, TypeDescriptorFlags.IsCollection, null, catalog);

TypeDescriptor returnRootElementTypeDescriptor =
    returnRootCollectionTypeDescriptor.ChildTypeDescriptors.Create(
        "Customer", true,
        "System.Data.IDataRecord, [full assembly name for System.Data]",
        "Customer", null, null, TypeDescriptorFlags.None, null);

returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
        "CustomerId", true, "System.Int32", "CustomerId",
        new IdentifierReference("CustomerId",
            new EntityReference("MiniCRM", "Customer", catalog), catalog),
        null, TypeDescriptorFlags.None, null);

returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
        "FirstName", true, "System.String", "FullName",
         null, null, TypeDescriptorFlags.None, null);

specificFinder.MethodInstances.Create("GetCustomer", true,
                                      returnRootElementTypeDescriptor,
                                      MethodInstanceType.SpecificFinder, true);

Method finder = ect.Methods.Create("GetCustomers", true, false, "GetCustomers");

finder.Properties.Add("RdbCommandText",
                      "SELECT [CustomerId] , [FullName]FROM MiniCRM.Names");
finder.Properties.Add("RdbCommandType", "Text");

Parameter custsParam = finder.Parameters.Create("Customer", true,
                                                DirectionType.Return);

TypeDescriptor returnRootCollectionTypeDescriptor2 =
    custsParam.CreateRootTypeDescriptor(
        "Customers", true,
        "System.Data.IDataReader, [full assembly name for System.Data]",
        "Customers", null, null, TypeDescriptorFlags.IsCollection, null, catalog);

TypeDescriptor returnRootElementTypeDescriptor2 =
    returnRootCollectionTypeDescriptor2.ChildTypeDescriptors.Create(
        "Customer", true,
        "System.Data.IDataRecord, [full assembly name for System.Data]",
        "Customer", null, null, TypeDescriptorFlags.None, null);

returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create(
        "CustomerId", true, "System.Int32", "CustomerId",
        new IdentifierReference("CustomerId",
           new EntityReference("MiniCRM", "Customer", catalog), catalog),
        null, TypeDescriptorFlags.None, null);

returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create(
        "FirstName", true, "System.String", "FullName",
         null, null, TypeDescriptorFlags.None, null);

finder.MethodInstances.Create("GetCustomers", true,
                              returnRootCollectionTypeDescriptor2,
                              MethodInstanceType.Finder, true);

ect.Activate();