Defining Associations in Business Connectivity Services Using SharePoint Designer 2010

Article
10/20/2016

Summary: Business Connectivity Services in SharePoint 2010 lets you access data from external systems. This article discusses how to use Business Connectivity Services to define relationships, called associations, between external tables. SharePoint 2010 supports associations in the picker on an Edit form, in Master/Detail views in Profile pages, and in the Business Data Web Parts. Many resources explain how to define an association for a one-to-many relationship by using a foreign key value. This article explains how to define associations when you do not have a clear foreign key value, or when you have many-to-many relationships.

Provided by: Bob McClellan

Contents

Introduction to Defining Associations in Microsoft Business Connectivity Services
Creating SharePoint External Content Types and an Association: A Review
Creating an Implicit Foreign Key for Defining Associations
Establishing Associations That Allow SharePoint Designer to Work with Many-to-Many Database Relationships
Conclusion
Additional Resources
About the Author

Introduction to Defining Associations in Microsoft Business Connectivity Services

Microsoft SharePoint Designer 2010 makes it very easy to create an association for one-to-many relationships that have a simple foreign key value. If you have a more complex relationship and are not sure how to make it work, this article can help you learn how to do the following:

Create a one-to-many relationship when there is no simple foreign key value.
Define associations for many-to-many relationships.
Use those associations in various Microsoft SharePoint 2010 user interfaces (UIs) that support associations; specifically, the picker in an Edit form, the Master/Detail view on a Profile page, and the Business Data Web Parts.

Before you read this article, you should already know how to create a simple association by using SharePoint Designer 2010 and understand how to use that association in at least one of the SharePoint 2010 UIs. (For more information, see the references in Additional Resources.) You must also be able to run Microsoft SQL Server Management Studio and execute SQL queries to modify the AdventureWorks database.

Before you start, ensure that you have the following:

Access to SharePoint 2010 Central Administration, including the Business Data Connectivity (BDC) service.
Access to SharePoint Designer.
A SharePoint 2010 web application that has a site collection. For more information, see Create a Site Collection (SharePoint Server 2010).
Access to Microsoft SQL Server.

Note

You can use the same instance of SQL Server that is used by the computer running SharePoint Server 2010.
A sample database for SQL Server and permissions to modify the tables and data. (The examples in this article use AdventureWorks. For information on how to install this sample database, see AdventureWorks 2008 R2.)

A virtual machine that meets all these requirements, 2010 Information Worker Demonstration and Evaluation Virtual Machine (RTM), is available for download from Microsoft. If you use that virtual machine, you can follow the steps in this article exactly. You need only the "A" machine.

The examples in this article are more meaningful if you have a general understanding of simple SQL queries and views. In addition, a general understanding of XML is useful because as you work through the examples, you will look at the XML that is used to define BDC models.

Creating SharePoint External Content Types and an Association: A Review

This first example reviews how you can use a simple association in various SharePoint 2010 UIs. The external content types that you create here are the base for the more advanced examples. Before you start, do the following:

Verify that the web application has connections to the Business Connectivity Services.
Configure the Business Data Connectivity (BDC) service so that you have proper access.
Create a Profile Page site and define it in the Business Data Connectivity Service.

If you have used Business Connectivity Services before, these steps should already be done. If you need detailed instructions, see Searching External Data in SharePoint 2010 Using Business Connectivity Services.

Using SharePoint Designer 2010, create an external content type for the ProductModel table from the AdventureWorks database. Define all of the standard operations for the external content type (Read List, Read Item, Create, Update, and Delete).

To create the ProductModel external content type

Create an external content type in SharePoint Designer 2010 using ProductModel as the Name and Product Model as the Display Name.
If it is the first time that you have used the AdventureWorks database, you must add a new Microsoft SQL Server connection.
Click Create All Operations for the ProductModel table.
On the Parameters page, select Show In Picker for the ProductModelID field and the Name field. Click Finish.
Set the Name field as the Title (from the Summary View).
Save the external content type. Figure 1 shows the ProductModel external content type.

Figure 1. ProductModel external content type

Next, create an external content type for Product and define an association with ProductModel.

To create the Product external content type with an association

In SharePoint Designer 2010, create an external content type by using Product as the Name and Display Name.
Click Create All Operations for the Product table.
On the Parameters page, select Show In Picker for the ProductID field, Name field, and ProductNumber field.
Create an association on the Product table. Use RelatedProductModel as the association name and Products by Product Model as the association display name.
Click the Browse button and then select ProductModels as the related external content type.
On the Input Parameters page, for ProductModelID, select Map to Identifier.
On the Return Parameter page, clear the following fields: MakeFlag, FinishedGoodsFlag, Size, SizeUnitMeasureCode, WeightUnitMeasurecode, Weight, DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, SellStartDate, SellEndDate, DiscontinuedDate, rowguid, and ModifiedDate. Click Finish.
Set the Name field as the Title (from the Summary View).
Save the external content type. Figure 2 shows the Product external content type.

Figure 2. Product external content type

Now you can create Profile Pages for each external content type, and then create an external list for each external content type. Create the Profile Pages first, or the option to view the Profile from the external list is not available. If you go to the Products external list, you can edit an item to see how the picker is defined in that form. (To see only records that have a Product Model selected, add a filter for Yes on the FinishedGoodsFlag column.) Notice that the Product Model field shows the name of the product instead of its identifier. That is the result of having a Title set on the Product Model external content type. If you want to type in a value instead of using the picker list, you must still type the identifier. Figure 2 shows the Edit Item form for the Product external content type.

Figure 3. Edit Item form for Product

Edit Item form for Product

Next, go to the external list for Product Models, and select View Profile for one of the items to see a Master/Detail list that uses the association. Notice how the fields that you selected to return for the association define what is shown in this list. Also, the title for the list is the display name that is defined for the association, as shown in Figure 4.

Figure 4. Profile page for Product Model

Profile page for Product Model

The last example uses two related Business Data Web Parts. Create a new page for the site. From the Insert ribbon, you can add a Web Part. In the Business Data group, click Business Data List Web Part, and then click Product Model as the type. Insert a Business Data Related List Web Part and select Product as the type. Before you close the Web Part dialog box, on the Web Part menu, click Connections, click Get Related Item From, and then select the Product Model List. Now when you select an item in the first list, the items that are related through the association appear on the related list, as shown in Figure 5.

Figure 5. Business Data Web Parts

Business Data Web Parts

This review showed how SharePoint can use an association in various UIs. The next section discusses how to create an association that does not have a direct foreign key value.

Creating an Implicit Foreign Key for Defining Associations

When there is a simple foreign key, SharePoint Designer 2010 can do most of the work to define the association. This section shows an example of a valid one-to-many association that SharePoint Designer 2010 cannot handle. Many database systems use standard foreign keys, but you will often encounter legacy systems that might not use a straightforward relationship. In a case like that, you must modify the XML for the BDC model.

First, you must make some modifications to the AdventureWorks database, which does not have an example of an implicit key value. You must execute the following SQL statements to modify the AdventureWorks data by using SQL Server Management Studio.

ALTER TABLE Production.UnitMeasure
ADD MeasureType char(1), UnitMeasureID int
GO

UPDATE Production.UnitMeasure
SET MeasureType = 'W'
WHERE UnitMeasureCode IN ('G','KG','KT','LB','MG','OZ')

UPDATE Production.UnitMeasure
SET MeasureType = 'S'
WHERE UnitMeasureCode IN ('CBM','CCM','CDM','CM','CM2','DM','FT3','GAL','IN','KGV','KM','L','M', 
'M2','M3','ML','MM','PT')

UPDATE Production.UnitMeasure
SET MeasureType = 'O'
WHERE MeasureType IS NULL
GO

The code adds two new columns to the UnitMeasure and then sets the types: S for Size, W for Weight, and O for Other. The UnitMeasureID then must be sequential for each type. The following statements set those appropriately.

DECLARE @currentID int, @temp int
SET @currentID = 1

DECLARE c CURSOR
    FOR SELECT UnitMeasureID FROM Production.UnitMeasure
        WHERE MeasureType = 'W'
    FOR UPDATE OF UnitMeasureID
OPEN c
FETCH NEXT FROM c INTO @temp
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Production.UnitMeasure SET UnitMeasureID = @currentID
        WHERE CURRENT OF c
    SET @currentID += 1
    FETCH NEXT FROM c INTO @temp
END
CLOSE c
DEALLOCATE c

SET @currentID = 1

DECLARE c CURSOR
    FOR SELECT UnitMeasureID FROM Production.UnitMeasure
        WHERE MeasureType = 'S'
    FOR UPDATE OF UnitMeasureID
OPEN c
FETCH NEXT FROM c INTO @temp
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Production.UnitMeasure SET UnitMeasureID = @currentID
        WHERE CURRENT OF c
    SET @currentID += 1
    FETCH NEXT FROM c INTO @temp
END
CLOSE c
DEALLOCATE c

SET @currentID = 1

DECLARE c CURSOR
    FOR SELECT UnitMeasureID FROM Production.UnitMeasure
        WHERE MeasureType = 'O'
    FOR UPDATE OF UnitMeasureID
OPEN c
FETCH NEXT FROM c INTO @temp
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Production.UnitMeasure SET UnitMeasureID = @currentID
        WHERE CURRENT OF c
    SET @currentID += 1
    FETCH NEXT FROM c INTO @temp
END
CLOSE c
DEALLOCATE c
GO

Finally, the following statements add the new IDs to the Product table.

ALTER TABLE Production.Product ADD SizeUnitMeasureID int, WeightUnitMeasureID int
GO

UPDATE p SET SizeUnitMeasureID = u.UnitMeasureID
FROM Production.Product p JOIN Production.UnitMeasure u ON u.UnitMeasureCode = p.SizeUnitMeasureCode

UPDATE p SET WeightUnitMeasureID = u.UnitMeasureID
FROM Production.Product p JOIN Production.UnitMeasure u ON u.UnitMeasureCode = p.WeightUnitMeasureCode
GO

At this point, SizeUnitMeasureCode and WeightUnitMeasureCode could be dropped from the Product table. The new SizeUnitMeasureID and WeightUnitMeasureID fields can be used instead. The only issue is that the ID values are not unique. The new key for UnitMeasure is a combination of MeasureType and UnitMeasureID, but only UnitMeasureID is stored in Product. The other key value, MeasureType, is implicit in the field itself. In other words, SizeUnitMeasureID implies that the MeasureType is S and WeightUnitMeasureID implies that the MeasureType is W.

Although SharePoint Designer 2010 cannot define the association in a way that works correctly, it can still help a lot. First, you create two external content types, one for the size association and the other for the weight association. Create each identically, except for the external content type name and display name.

To create the UnitMeasure external content types

Create a new external content type in SharePoint Designer 2010, using UnitMeasureSize as the Name and Sizes as the Display Name.
Choose New Read Item Operation for the UnitMeasure table.
Change the Name and Display Name to Read Item.
On the Input Parameters page, clear Map to Identifier for the UnitMeasureCode field and select it for the UnitMeasureID field. Do the same thing on the Return Parameter page. Click Finish.
Choose New Read List Operation for the UnitMeasure table.
Change the Name and Display Name to Read List.
On the Return Parameter page, select Show In Picker for the Name field. Ensure Map to Identifier is not selected for the UnitMeasureCode field, and select it for the UnitMeasureID field. Click Finish.
Set the Name field as the Title (from the Summary View).
Save the external content type.
Repeat using UnitMeasureWeight as the name and Weights as the Display Name.

Those external content types are not exactly right. They have one part of the key defined, but not the implicit part. At this point, examine the XML for the BDC model. External content types can be exported from SharePoint Designer 2010. From the list of external content types, select the external content type to export and then click Export BDC Model in the ribbon, as shown in Figure 6.

Figure 6. Export a BDC model

Export a BDC model

A dialog box prompts you for a name for the BDC model. An external content type cannot exist on its own in the XML for BDC; it has to be part of a BDC model. So you specify, in the dialog box, what name to use for the model it creates to hold the external content type. It is good practice to use the same name as the external content type, so type UnitMeasureSize or UnitMeasureWeight as appropriate.

The next dialog box prompts you for a file name and location. Many developers like to have a folder just for BDC models so that those XML files are never confused with other XML files. The Save as type box defaults to BDCM Files. If you use that type, the extension .bdcm is attached to the file name that you specify. That can be useful if you want to associate the BDCM file type with your preferred XML editor. The other option is to change Save as type to All Files and then put .xml on the end of the file name. The resulting file has the .xml extension, so it is associated with your XML viewers and editors.

After you get both external content types exported, look at the resulting XML file. Although the change that you have to make is very small, it is important to take the time to examine the format of this file in detail so that you understand how it works. To begin, take a look at Searching External Data in SharePoint 2010 Using Business Connectivity Services, which explains the format of the XML, including the details about the elements and what they mean in the section titled "Explanation of the BCS Model XML."

Important

Please read, or at least skim, "Explanation of the BCS Model XML" in Searching External Data in SharePoint 2010 Using Business Connectivity Services before continuing.

Building on the information in that article, the next few paragraphs specifically discuss how the various operations and associations are defined and how you can modify them.

If you examine either of the XML files, notice that there are two Method elements. One is named Read Item and the other is named Read List. As you might suspect, these correspond to the operations that you defined in SharePoint Designer 2010. However, the information that really identifies them as a specific operation is not the name; it is the Type attribute in the MethodInstance element. You should see that the Read Item method has SpecificFinder as its type and the Read List method has Finder as its type. When you create an external list in SharePoint 2010, SharePoint looks for the Finder type to get a list of records to show in that list. The return parameter for the Finder method is defined to return a list of records. The enclosing data type is System.Data.IDataReader, which is a collection of System.Data.IDataRecord objects. That is a collection of fields with types that match the field. These are all created by SharePoint Designer 2010 to match the data types of the SQL table. These records must also have an identifier defined. If you look very carefully at the Return parameter for the Read List method, the IdentifierName attribute is set for the UnitMeasureID field. This part of the method definition also contains many options for the fields, such as the show in picker flag. There is no input parameter to Read List because it simply returns all the records in the list.

However, the Read Item method is meant to get a single record; thus the type SpecificFinder. Notice that the "in" parameter for this method is defined as @UnitMeasureID. The type of that parameter must match the identifier that is defined in the Finder method because it is used for this method to retrieve a specific record. The return parameter for the SpecificFinder is almost the same to that of the Finder, but it does not have to be. For example, you could have very few fields in the list, but include them all in the SpecificFinder so that you see more detail when you view a single item in SharePoint 2010.

The other types that SharePoint Designer 2010 can define for you are Creator, Updater, and Deleter. Those correspond to the Create, Update, and Delete operations. Associations are also defined as methods and you will see much more about those later in this article.

The part of the method that defines how to interact with the external system is primarily the RdbCommandText property of the method. Conveniently, an SQL query can easily provide any record, group of records, or values that are needed. For example, the Finder method uses the following query.

SELECT [UnitMeasureCode] , [Name] , [ModifiedDate] , [MeasureType] ,
    [UnitMeasureID] FROM [Production].[UnitMeasure]

This query retrieves all the records from the UnitMeasure. The SpecificFinder method uses the following query.

SELECT [UnitMeasureCode] , [Name] , [ModifiedDate] , [MeasureType] ,
    [UnitMeasureID] FROM [Production].[UnitMeasure]
    WHERE [UnitMeasureID] = @UnitMeasureID

Notice how you can use the "in" parameter, @UnitMeasureID, as part of the query to retrieve the specific requested record. These queries are all that have to be modified to get the required implicit key behavior. The following is the new Finder query for the Sizes external content type.

SELECT [UnitMeasureCode] , [Name] , [ModifiedDate] , [MeasureType] ,
    [UnitMeasureID] FROM [Production].[UnitMeasure]
    WHERE MeasureType = 'S'

The WHERE clause limits the results from the UnitMeasure table to only those records that represent Size units. By using that added restriction, UnitMeasureID is now unique for each of those rows. However, we have to add that restriction to the SpecificFinder query so that it only examines the Size records also, or it could find Weight records with the same ID. The following is that updated query.

SELECT [UnitMeasureCode] , [Name] , [ModifiedDate] , [MeasureType] ,
    [UnitMeasureID] FROM [Production].[UnitMeasure]
    WHERE [UnitMeasureID] = @UnitMeasureID AND MeasureType = 'S'

Now the previous query can only ever return one record for any particular value of UnitMeasureID. After you make those changes to the Size model, make the similar changes to the Weight model using W instead of S for MeasureType.

Importing these changes back into SharePoint is not quite as easy as the export. You must use the following steps in the BDC Service in Central Administration to import the modified model.

To import a BDC model into SharePoint

Open Central Administration and navigate to the Business Data Connectivity Service.
You must delete any existing external content type or model that matches the one that you want to import. If you have not imported previously, the external content type exists without a model and you must delete that external content type. If you have imported previously, a model and an external content type are related. Deleting the model deletes the related external content type. However, deleting both the external content type and the model works too.
On the ribbon, click Import to begin importing the model.
Click the Browse button and navigate to the file that you want to import.
Click the Import button to start the import process.
A progress bar appears, and then a summary of any errors or warnings. The warnings are worth reading, but usually are not a problem. If an error occurred, you must fix it and then try the import again. Click OK to continue.

To finish the definitions, you create two new associations for the Product table.

To add Size and Weight associations to the Product external content type

Open the Product external content type in SharePoint Designer 2010 (if it is not already open). Switch to the Operations Design View.
Double-click the Read List operation in the list of External Content Type Operations on the right side. On the Return Parameter page, select SizeUnitMeasureID and WeightUnitMeasureID in the Data Source Elements list. Click Finish.
Double-click the Read Item operation in the list of External Content Type Operations. On the Return Parameter page, select SizeUnitMeasureID and WeightUnitMeasureID in the Data Source Elements list. Click Finish.
Create an association on the Product table. Use RelatedUnitMeasureSize as the association name and Products by Size as the association display name.
Use the Browse button to select UnitMeasureSize as the related external content type. In the list below it, look for UnitMeasureID in the Related Identifier column. In the Field column next to it, use the drop-down list to select SizeUnitMeasureID.
On the Input Parameters page, select the Map to Identifier box for the SizeUnitMeasureID field.
On the Return Parameter page, clear everything except ProductID, Name, and ProductNumber. Ensure that ProductID has the Map to Identifier box selected. Click Finish.
Create an association on the Product table. Use RelatedUnitMeasureWeight as the association name and Products by Weight as the association display name.
Use the Browse button to select UnitMeasureWeight as the related external content type. In the list below it, look for UnitMeasureID in the Related Identifier column. In the Field column next to it, use the drop-down list to select WeightUnitMeasureID.
On the Input Parameters page, select the Map to Identifier box for the WeightUnitMeasureID field.
On the Return Parameter page, clear everything except ProductID, Name, and ProductNumber. Ensure that ProductID has the Map to Identifier box selected. Click Finish.
Save the changes to the Product external content type.

Now re-create the list and forms for the Product external content type to see the changes. Go to the Lists and Libraries, delete the existing list, and then create it again in the external content type. Now when you go to the list and edit an item, you see new pickers for the size and weight associations. The picker uses the Finder method (Read List operation) from the associated external content type, exactly like an external list, except that it displays only the fields marked as Show in Picker, as shown in Figure 7.

Figure 7. Product Edit Item form with Size and Weight associations

Product Edit Item form with associations

Notice that the same approach can be used to modify the Creator, Updater, and Deleter types. The Updater and Deleter need the same added condition for MeasureType. The Creator needs the proper value, S or W, to be part of the INSERT statement.

Establishing Associations That Allow SharePoint Designer to Work with Many-to-Many Database Relationships

The most common way to represent a many-to-many relationship in SQL is with three tables. This example uses Product, Location, and ProductInventory from the AdventureWorks database. The many-to-many relationship is created by ProductInventory, which contains foreign keys to both Product and Location. A record exists in ProductInventory for each product in each location. Because SharePoint Designer 2010 can create associations between only two external content types, it cannot represent this relationship directly. This article shows you two ways to easily look at the many-to-many association. SharePoint has no built-in interfaces to update many-to-many associations. Creating a Web Part or other SharePoint extension for that purpose is beyond the scope of this article.

The two interfaces that are useful for many-to-many relationships are the Master/Detail Profile page and the Business Data Related List Web Part. In this example, we could treat either Product or Location as the master record. The trick is to create two associations, one for each as master. If Product is the master record, SharePoint can treat the combination (or join) of ProductInventory and Location as a table that contains a foreign key to Product. If Location is the master record, SharePoint can treat the combination of ProductInventory and Product as a table that contains a foreign key to Location. There are two simple methods to tell SharePoint to treat two tables as though they were one.

The simplest method is to create a view for each combination. They resemble the following example.

CREATE VIEW Production.vInventoryByLocation AS
SELECT i.LocationID,i.Shelf,i.Bin,i.Quantity,p.*
FROM Production.ProductInventory i
JOIN Production.Product p ON p.ProductID = i.ProductID
GO
CREATE VIEW Production.vInventoryByProduct AS
SELECT i.ProductID,i.Shelf,i.Bin,i.Quantity,l.*
FROM Production.ProductInventory i
JOIN Production.Location l ON l.LocationID = i.LocationID
GO

The first view enables you to see products and their inventory information for a particular location. The second view enables you to see locations and their inventory information for a particular product. Notice how the inventory information is common to both because it appears in the ProductInventory table. (Often, that "middle" table contains only the foreign key values and nothing else. In that case, there would be no common data between the two views.)

Now use SharePoint Designer 2010 to create external content types for these. Although the identifying value for these views is really the combination of LocationID and ProductID, you must specify to SharePoint Designer that the identifier for vInventoryByLocation is merely LocationID and that the identifier for vInventoryByProduct is merely ProductID. This works because the results are limited to a single value for the other half of the key. For example, when you view a list of Inventory by Location, a particular LocationID is specified, so the resulting records are uniquely identified by ProductID. Because you already have an external content type for Product, you start by creating the Inventory external content type to go with it.

To create the InventoryByProduct external content type with an association to Product

Create a new external content type in SharePoint Designer 2010, using InventoryByProduct as the Name and Inventory By Product as the Display Name.
Use the Create All Operations options for the vInventoryByProduct view. Views are in a separate section below the tables.
On the Parameters page, click Map to Identifier for LocationID. Click Finish.
Create an association on the vInventoryByProduct view. Use RelatedProduct as the association name and Inventory by Product as the association display name.
Use the Browse button to select Product as the related external content type.
On the Input Parameters page, select the Map to Identifier box for the ProductID field.
On the Return Parameter page, select the Map to Identifier box for the LocationID field. Click Finish.
Save the external content type.

Now you should be able to create (or re-create) a profile page for the Product external content type, and then generate a new external list of Products. When you look at the profile page for a product, it shows a list of the inventory locations for that product.

You can also create an association to see the many-to-many data the other way, as a list of products for a particular location. First, create the external content type for the Location table, and then create an external content type for the vInventoryByLocation view with an association that is similar to the one for products. The only difference is that the location and product identifiers are reversed. You should be comfortable enough with these steps to do that on your own as a learning exercise. If not, continue with the specific instructions that follow.

To create the Location external content type and the InventoryByLocation external content type with an association to Location

Create a new external content type in SharePoint Designer 2010, using InventoryByProduct as the Name and Inventory By Product as the Display Name.
Use the Create All Operations options for the vInventoryByProduct view. Views are in a separate section below the tables.
On the Parameters page, select Map to Identifier for LocationID. Click Finish.
Create an association on the vInventoryByProduct view. Use RelatedProduct as the association name and Inventory by Product as the association display name.
Use the Browse button to select Product as the related external content type.
On the Input Parameters page, select the Map to Identifier box for the ProductID field.
On the Return Parameter page, select the Map to Identifier box for the LocationID field. Click Finish.
Save the external content type.

The previous approach works very well if you have permission to create views on a database, but that is not necessary. There is another trick, but it requires digging into the XML again. Start by exporting a BDC model for the InventoryByProduct external content type so that you can examine how the association is defined there. Look at the generated XML file and find the Method element with the name RelatedProduct. This method has one "in" parameter, the product ID, and one return parameter, the list of inventory records. The product ID is determined by the specific product selected. For example, when you view a product profile page, the product ID selected for that profile page is used for the association method. The result is a list of inventory and locations that are then shown in the associated list. For an association, the MethodInstances element has an Association element, instead of a MethodInstance element. The Association element identifies the type of association (AssociationNavigator) and the two entities (external content types) it is using. SharePoint then puts all these definitions together to properly retrieve the requested records.

As with the other methods, the actual functionality of the association is defined by an SQL statement. It can still be found in the RdbCommandText property. The following is that statement copied from the InventoryByProduct XML.

SELECT [ProductID] , [Shelf] , [Bin] , [Quantity] , [LocationID] ,
    [Name] , [CostRate] , [Availability] , [ModifiedDate]
    FROM [Production].[vInventoryByProduct]
    WHERE [ProductID] = @ProductID

This is a very simple query on the view. If you were unable to create a view, you could replace that query with the following.

SELECT i.ProductID,i.Shelf,i.Bin,i.Quantity,l.LocationID,l.Name,
    l.CostRate,l.Availability,l.ModifiedDate
FROM Production.ProductInventory i
    JOIN Production.Location l ON l.LocationID = i.LocationID
WHERE i.ProductID = @ProductID

This is the same query as the view (with the full list of columns for Location) with the WHERE clause for the association. If you make this change to the association, you no longer need the view. It is certainly possible to create the entire association from scratch, and here are a few tips on how to do that successfully. First, remember that the association is defined for the table or view that contains the foreign key. The return parameter should be about the same as the return parameter for the Read List operation (Finder type). The "in" parameter should have a TypeDescriptor element that is the same as the correct field from the return parameter. Ensure that you select a good name for the "in" parameter also. Then you can modify the SQL query to add a WHERE clause that compares to the "in" parameter name. Most of the changes involve checking and changing the names of various elements. The whole process is prone to errors, so it is a good idea to let SharePoint Designer 2010 do as much as it can.

Conclusion

This article discusses two specific examples of advanced Business Connectivity Services associations, but many more are possible. If an SQL query can define the relationship based on some "identifier," you should be able to define it by using the BDC XML model, and then SharePoint can recognize it.

You may have noticed that this article skipped one step. In both examples, the article lets SharePoint Designer 2010 create the type definitions for the input and output parameters. This may not always be possible, but it is strongly recommended. All the attributes of the type definitions are straightforward, but there is a lot of detail. SharePoint Designer 2010 does a great job of handling that part of the XML. If you cannot get a matching table or view in the database because you do not have permission to do so, there are alternatives. One is to create a table with the proper types in another database and use that to generate the XML. You can then make your changes and switch to the actual database. Another approach is to find one or more tables that have the same types defined and created external content types for those, and then export them and copy and paste them together to make the definition that you need.

Business Connectivity Services in is a tremendously powerful and flexible system for accessing external data. It does not take a lot of work to go beyond the built-in capabilities in SharePoint Designer 2010, but still get the advantages of automatic user-interface creation from SharePoint.

About the Author

Bob McClellan has been developing applications, mostly for database systems, for almost thirty years. He recently worked on the Power Tools for Open XML, which are a series of Windows PowerShell cmdlets for manipulating Open XML documents. Bob has extensive programming experience in a variety of computer languages, database systems, and development environments.

Additional Resources

For more information, see the following resources: