Rapid Application Development Using InfoPath and BCS – Part 2 (Anweshi Deverasetty)

Artigo
07/26/2011

Creating Associations

In this section, you start by creating an association on the Employee table. When you generate the New and Edit forms on the Employee entity, you see that the Deptno field displays a textbox. The user needs to enter the Dept ID as shown in the following figure (these forms are generated later in this section).

Figure 3. Edit form on the Employee entity

In this screen, it would be helpful if there is an option to select the Departments available. To achieve this, you can create an association. In the following discussion, you see what the edit form will look like. Later, you generate the InfoPath forms.

To create an association it must be created on the master table (the Department Entity table).

Step 1: Create a stored procedure that returns the Employee details specific to a Department. Make sure that it returns the same set of parameters as that the other CRUD operations return or pass in or, as stated previously, the forms may not be generated.

Stored Procedure Name

Parameters

Return Parameters

DepartmentEmployeeAssociation

@Deptno

Empno

Empname

JobNo

DeptNo

User

Step 2: Create an Association on the Employee Entity. Create an association method by calling the previously defined stored procedure.

Listing 9

<Property Name="BackEndObject" Type="System.String">DepartmentEmployeeAssociation</Property>

<Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine</Property>

<Property Name="RdbCommandText" Type="System.String">DepartmentEmployeeAssociation</Property>

<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">StoredProcedure</Property>

</Properties>

</Parameter>

</TypeDescriptor>

</TypeDescriptor>

</TypeDescriptor>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

</Association>

</MethodInstances>

</Method>

Step 3: Create an Association Group on the Employee. Create the AssociationGroup element under the entity element.

Listing 10

</AssociationGroup>

</AssociationGroups>

Step 4: Apply the association to the Deptno parameter in the Employee entity in all of the operations. Make sure that this association is applied to all of the methods; or else you may have issues generating the forms. Notice that an association created for Deptno parameter while defining association is also defined in the previous XML association snippet. The following is a sample of a Create operation on the Employee entity where you are applying an association on the Deptno column. This should be done for all the operations to include the Create, Update, ReadItem, and ReadList operations; in other words, where ever Deptno is referred to.

Listing 11

</TypeDescriptor>

</Parameter>

Now, the association is ready. Try to add or edit an item on the Employee External List and notice that the Picker control is available. The following is a screenshot of the Picker control.

Figure 4. Picker control

On the Picker control, you see the list of Departments from where you can make a selection as shown in Figure 5.

Figure 5. List of departments

You can create a similar association on Jobno column as well. You need to create an Entity for Job with ReadItem and ReadList operations and the create an association on the Department column.

Associations can be used to create connections on BDC Web Parts and to display the Picker controls.

Performance Check on the BDC Model

Ensure that your BDC Model has undergone the following changes:

All operations are stored procedure based.
All read list operations must have parameters.
Stored procedures are internally configured to return a maximum number of records.
All read list operations must have filters set on the maximum number of rows to return.
If you don’t have the filter on, on the read-list, then during BDC Model import, warnings will be generated.

Importing the BDC Model

Now, you are ready to import the BDC Model. Open Central Administration and select from Manage Service Applications under Application Management. Select Business Data Connectivity Services that you configured. Click Import to import the BDC Model that you just created.

Figure 6. Central Administration – Import BDC Model

Ensure that:

You have Secure Store Service turned on.
You have set the database name correctly in the “External System” for your imported model.
The model is using SSO.

Creating the External Lists

After importing the BDC Model, create the external lists for the Department and Employee entities. To create an external list, under Site Actions, select View All Site Content, and then select Create Custom List. Next, select External List, and then select the External Content Type as Department.

Figure 7. Create the Department external lists