Connecting to BCS Data Using SharePoint Designer Share Point 2010

As discussed earlier in this chapter, BCS data is surfaced in SharePoint via external content types. Regardless of the mechanism used to connect to the data, whether it’s via an out-ofthe- box connector such as ADO.NET or WCF or via a connectivity assembly or custom connector, the end result is a series of external content types, each of which represents a single entity definition in the source system. So, for example, in an underlying CRM system, entities may be defined for Customer, Address, and Sales Order.

Naturally, in any data system, entities have relationships—for example, a Customer entity may be related to a Sales Order entity. BCS allows for the modeling of such relationships using associations. It’s possible to create associations between any two external content types provided they have appropriate identifiers. To take our CRM example a bit further, let’s say we also had an ERP system with stock information on individual products. The product stock level was defined in an entity called Product in the ERP model. If the Sales Order entity in our CRM model contained a ProductId field of type Int32 and the Product Entity in our ERP model contained an identifier named ManufacturedProductId, also of type Int32, it would be possible to create a relationship between these entities regardless of the fact that they exist in separate systems.

You may wonder how a system that’s capable of retrieving data from practically any data source works. In general programming terms, where an object must communicate with other objects of unknown type, a common standard is adopted, either via the implementation of a known interface or via inheritance, which requires that all objects inherit from a common base class. The BDC service, the engine behind BCS, employs a similar mechanism known as stereotyping.

You may also be wondering, why call it stereotyping? Why not use an established term that makes more sense to developers? There is a very good reason for this: The BDC service is all about defining the connections between two systems, not physically making the connections. None of the code in the BDC service actually sends or receives data between A and B; instead, the BDC service simply delegates the request to the appropriate endpoint. As a consequence, there is nowhere to implement an interface and no abstract classes to inherit from—it’s all about metadata. Stereotyping denotes a particular endpoint and configuration as being appropriate for a particular operation. For example, one important stereotype is SpecificFinder. A model may contain metadata that specifies that requests should be sent to the ReadRecordFromDataBase function in the MyBDCModel assembly whenever a SpecificFinder operation is executed.

The BDC Service defines a number of stereotypes covering every data access operation supported by the platform. Not all of these operations are commonly required, although the following operations are used in most models to provide create, read, update, delete, and query (CRUDQ) functionality:


The following operations provide additional functionality for use in specific circumstances:


Create an External Content Type

Now that you understand what BCS is and how the BDC service uses metadata to connect to external systems, you’re ready to put this knowledge into practice by creating an external content type using SharePoint Designer.

  1. In SharePoint Designer, connect to the new site that we created earlier. From the Site Objects menu, choose External Content Types:
  2. From the ribbon, in the New section, select External Content Type. In the page that appears, double-click New External Content Type next to the Name label and change the name to Model.
  3. Create an External Content Type

  4. From the ribbon, select Operations Design View. You’ll notice that the title of the window changes to Model, confirming that the name change has been applied. In the window that appears, click the Add Connection button. In the dialog that appears, set the Data Source Type to SQL Server, as shown; then click OK to continue.
  5. Create an External Content Type

  6. In the SQL Server Connection dialog, enter the name of your SQL server in the Database Server text box. For example, if you’re using the local SQL Express instance, you will type .SQLExpress. In the Database Name text box, type AdventureWorks. Accept the default connection option of Connect With User’s Identity. Click on OK to create the connection.

Define SpecificFinder Operation

  1. After the connection has been verified, in the Data Source Explorer tab, you’ll be able to see the objects in the database. Expand the Tables node and then scroll down to the ProductModel table. Right-click the table, and in the context menu, you’ll see a number of options for defining operations on the ProductModel entity. Select New Read Item Operation.
  2. The Read Item Wizard will start, where you can define the metadata for a new SpecificFinder operation. In the Operation Name text box, type Read Item. Click Next to continue.
  3. Define SpecificFinder Operation

  4. On the Input Parameters page, you’ll notice a few important things:
    • ProductModelID is highlighted as an identifier. Each entity must define at least one identifier, though more than one identifier can be defined. For example, if you have a many-many relationship between two tables in a database, you may have a join table with a compound primary key. In this case, you’d have two identifiers.
    • Each item has a Display Name and Default Value option. Changing the Display Name in this page has no effect when creating external lists since the display name used by SharePoint is taken from the return values. Changing the Default Value will apply a default if the input parameter is null.
  5. Accept the default settings by clicking Next to move to the next page.

  6. On the Return Parameter page, notice a few more options:
    • Each field can be checked or unchecked, although unchecking the ProductModelID yields an error since each entity must have an identifier.
      Unchecking other fields may raise a warning if the field is not nullable in the underlying table, because updates and additions will be impossible since a value is required by the database schema.
    • Remember that even though we’re defining a SpecificFinder operation, unchecking columns here affects the overall definition of the entity. This means that it will not be possible to add or update values in fields that are unchecked even though the operations to perform these actions are defined separately. In effect, the SpecificFinder defines the columns of any external lists created from the External Content Type and consequently the columns used when adding and editing data.
    • A number of parameters appear for each field, including a Map to Identifier checkbox. Identifiers are defined in metadata separately and must be mapped to fields using this option.
    • The Display Name property defines the column name as it appears in External Lists created from the external content type. It also defines the text on the label that appears next to the item in add and edit forms.
  7. Click Finish to complete the creation of the Read Item operation.

NOTEIf we had an additional entity that could be correlated by using a particular field—such as Name—you would imagine that we could flag Name as an identifier, allowing associations between the entities to be made. However, this change has some undesirable implications: since Name is now an identifier, we effectively have a compound primary key. This means that all associations would be created based on both the Name and ProductModelID fields. It would not be possible to create an association based on one field or the other in isolation, thus defeating the object of the change.

Define Finder Operation
Now that we have defined a SpecificFinder operation to retrieve individual items from our data source, the next requirement for creating an external list is to define a Finder operation. The external list, like all other lists in SharePoint, makes use of the XsltListViewWebPart to render the contents of the list. However, rather than retrieving the list contents from the SharePoint database, the View definition contains a Method element specifying the name of a Finder operation that’s been defined on the external content type.

Creating a Finder operation follows a similar process to the creation of the SpecificFinder operation:

  1. Right-click a table on which to define the operation and then select the type of operation from the context menu. In this case, we’ll create a new Read List operation on the ProductModel table.
  2. In the wizard that appears, set the Operation Name to Read List, and then click Next to continue.
  3. On the Filter Parameters Configuration page, you’ll notice in the Errors and Warnings section a warning message relating to the creation of a limit filter. By default, a limit filter is not created, and this has implications when creating external lists. The default maximum number of rows that can be supported by an external list is 2000 .If the Finder operation returns more rows than this, we’ll end up with a pretty cryptic web part error when we try to view the data in our external list.

TIPEven when external lists are not required, by not setting a filter, we’re allowing the BDC service to return all rows in a table. In most cases, this would represent a significant waste of system resources.

Add a Limit Filter

  1. To add a limit filter, click the Add Filter Parameter button. A new filter will be added to the list of Filter Parameters.
  2. From the Data Source Element drop-down, select the field that contains the data to be filtered. In this case, it doesn’t matter which column we select since we’re applying a limit filter. Leave the default of ProductModelID selected.
  3. Next to Filter, click the Click To Add hyperlink to display the filter configuration dialog.
  4. In the New Filter text box, enter Limit Filter as the name. Select Limit from the Filter Type drop-down and <<None>> from the Filter Field drop-down. Click OK to create the filter.
  5. In the Properties section of the Filter Parameters Configuration page, in the Default Value combo box, enter 2000 as the default. The completed page should look as illustrated next:
  6. Add a Limit Filter

  7. Click Next to move on to the Return Parameter Configuration step. You’ll notice that this page is similar to the Return Parameter page used when creating a SpecificFinder method. However, there is one significant difference: the inclusion of the Show In Picker checkbox in the properties for each field. As you’ll see later, the External Data Picker control allows the user to search for an item from an external data source. Selecting the Show In Picker flag will include the associated field as a column in the results displayed in the External Data Picker control. Set the Show In Picker flag for the Name field only. Click Finish to complete the wizard.

TIPBy default, none of the fields have Show In Picker selected. Since the External Data Picker doesn’t know which fields to include, it simply includes all of them. A much better user experience can be gained by displaying only useful columns in the picker control.

Create All Operations
We’ve now added the minimum operations required to generate an external list. If we generate a list using only these operations, users will be able to view data in a list but will not be able to add, edit, or delete since we haven’t defined those operations.

We manually created the Finder and the SpecificFinder to give us a chance to review the various configuration options. Thankfully, in the real world, you don’t need to go through the same steps for each operation; you can simply select Create All Operations from the context menu. A wizard will automatically generate the required operations to allow users to read and write to the external data store.

  1. Use the Create All Operations wizard to add additional operations to the Model content type. Once the wizard has completed, delete both the Read Item 2 operation and the Read List 2 operation since these are duplicates of the operations that we manually created. When using SharePoint Designer, items can be deleted using the Remove command.
  2. Save the changes by clicking the Save icon in the upper-left corner of the window.

Create an External List

  1. Select the Model External Content Type, and then click the Create Lists & Form button in the ribbon.
  2. In the dialog that appears, make sure that Create New External List is selected.
  3. In the List Name text box, enter Product Models. Read Item Operation should be set to Read Item and System Instance should be set to Adventure Works.You can add a List Description if you’re feeling particularly conscientious.
  4. Click OK to create a new external list based on our Model External Content Type. Once the list has been created, navigating show a link to the new list on the left side of the page. When you open the list, you can see that it’s populated with data from our AdventureWorks database, as expected. Since the list is rendered directly from the Adventure Works database whenever the page is loaded, any changes made in the database will have immediate effect. By the same token, any changes made in SharePoint are applied directly to the database.

Create an Associated External Content Type
Now that you’re familiar with the tools used to create external content types, you’re ready to create another external content type for product information. Then we’ll define a parent-child relationship between our new Product content type and our existing Model content type.

  1. Follow the steps detailed earlier to create a new external content type named Product based on the AdventureWorks Product table. Rather than manually configuring each operation, select Create All Operations from the context menu to allow the wizard to do most of the work. This time flag the Name and ProductNumber fields to appear in the picker. Remember to include a Limit filter to restrict the number of rows returned.
  2. In the Operations Design View, right-click the Product table to show the operations context menu. This time, select New Association to create an association between this entity and our Model entity.
  3. In the Association wizard, change the Association Name and the Association Display Name to Product Model. The Association Display Name is shown in any form as the label for the External Data Picker control.
  4. Click the Browse button and in the dialog that appears, select Model. When an entity is selected, its identifiers are listed in the Related Identifier column. To the right is a Field column that contains drop-down lists, where we can select the field in our content type that maps to the identifier in the associated entity. In effect, the Related Identifier column contains the primary key columns of the entity that we’re associating with, and by selecting a matching field in our entity, we’re creating a foreign key relationship.
  5. Click Next to proceed to the Input Parameters page. Even though in the preceding step we defined the relationships between the entities, in the Input Parameters page we have to select the foreign key field from the list of Data Source Elements, and then check the Map To Identifier checkbox to create the foreign key relationship physically. Click Finish to complete the wizard. The list of external content type operations should be populated, as shown here:

Create an Associated External Content Type

External Data Picker Control
We’ve now created a new Product external content type that has a relationship with our existing Model content type. To see the effects of this association in action, create a new external list based on the Product content type. In the Create List and Form dialog, set the List Name to Products.

By browsing to the new Products list using Internet Explorer, you’ll see that when editing an item in the list, models can be selected from a list using an External Data Picker control, as shown here:

External Data Picker Control

You should notice a few things when you’re using the data picker. First, the list displays all rows from the Models table. If you enter criteria in the Find box, you’ll get an error message, as shown next. Second, if you select an item from the list and then click OK, the Product Model control contains the ID of the selected item rather than the user-friendly text you might expect.

External Data Picker Control

Both of these problems are easy to resolve and take us back to our Model content type.

Setting Picker Display Text
When opening the Model content type in Summary View, you’ll notice a list of fields on the right side of the page.

  1. Select the Name field, and from the ribbon, click the Set As Title button.
  2. Save the changes to the external content type, and then review the Products list in Internet Explorer.
    This time, when editing an item, the model name is displayed when a model is selected rather than the ID. Each external content type can have a title column defined. If no title is defined, the ID is used instead. Any column in the entity can be flagged as the title.

Adding Picker Search Functionality
The next problem requires a few more mouse clicks to resolve. Open the Model content type in Operations Design View. Since we want to change the way lists of items are returned, we need to adjust the settings for the Finder operation.

  1. In SharePoint Designer, Finder operations are created using the Read List type. Highlight the Read List operation, and then select Edit Operation from the ribbon.
  2. In the Filter Parameters step of the Edit Operation wizard, click Add New Filter Parameter to add an additional filter.
  3. Set the Data Source Element to Name, and then click the Click To Add link to show the Filter Configuration dialog.
  4. Create a new filter named Search Filter and set the Filter Type to Wildcard.
  5. Since we don’t want to apply this filter where no criteria have been entered, check the Ignore Filter If Value Is checkbox. The default option of Null is fine. A couple additional checkboxes warrant some explanation:
    • The Is Default checkbox determines whether the filter should be selected by default in the picker control. You’ll remember that the picker control contains a drop-down list of search types as well as a text box for the user to enter search criteria. A check in the Is Default checkbox means the filter will automatically be selected as the default in the search types drop-down.
    • The Use To Create Match List In Data Picker checkbox also relates to the default search. However, rather than setting the default search in the search types dropdown, the checkbox defines which filter should be used when the user types a value in the External Data Picker control without clicking the picker button. So, for example, on an edit page containing an External Data Picker control, the control is rendered as a text box with two buttons. The rightmost button opens up the picker, and the left button performs a behind-the-scenes search using the value entered in the text box. If a single match is found, the item is selected. If not, a list of suggestions are presented.
    • With an understanding of the Is Default and Use To Create Match List In Data Picker options, set both of these options to true.

  6. Click Finish to apply the changes, and then save the external content type.

This time, when viewing the changes in the edit form, try entering jersey in the product model text box, and then click the button to the immediate right. You’ll see an error message indicating that no exact match was found, and you can click the underlined text to see a list of suggestions, as illustrated here:

adding picker search functionality

The picker now behaves as expected, filtering results based on the criteria entered in the textbox and allowing search within the pop-up dialog.

Figure:Pinning object explorers to the navigation bar

Figure:Pinning object explorers to the navigation bar

TIPWhen moving between different object types in SharePoint Designer, it’s possible to pin one category of objects to the left sidebar. In Figure the External Content Types explorer is pinned to the navigation bar while the List and Libraries explorer is visible in the main pane, making it easy to switch to a particular external content type without first having to bring up the explorer.

Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Share Point 2010 Topics