RAD using InfoPath and BCS, and Overcoming performance limitations using SPs and Filters

 

Contributors : Anweshi Deverasetty, Ashutosh Mahamia

Background

In our current Sharepoint project, we had a requirement to create a user interface for performing CRUD operations on Master detailed tables. We had to generate forms for Create/Edit/Delete/View operations on Master and Detailed tables. We came up with an approach to generate all these forms OOB with less C# code. So we started with BCS ( Business Connectivity Services).

Contents

  1. Introduction.
    1. The Challenge
    2. The Solution.
  2. BCS Entities: The Business cum Data Access Layer
    1. Create CRUD operations on Entities using Stored Procedures.
    2. Creating Associations.
    3. Performance Check on BDC Model
    4. Importing BDC Model
    5. Creating The External Lists.
  3. SharePoint InfoPath: For Application Presentation Layer
    1. Generating InfoPath for External Lists.
    2. Enabling Search/Filters on External Content Types.
  4. The Application: A SharePoint site / Site collection
    1. Creating a Site for Application.
    2. Creating An Application Page

Introduction

There is growing demand and expectation as well in the field to be able to leverage the new features in SharePoint 2010 to create database driven simple SharePoint solutions which do not involve lot of customization, business rules and validations. This article will help you to understand how BCS and InfoPath, together can supplement this requirement and facilitate database CRUD operations based simple applications with decent User Interface.

The Challenge

One of the key challenges in this approach is Performance. If the number of records maintained by the application outgrow to what the entities can handle, then it reflects upon a poor user experience. The pages rendering slow, the data searches taking significant time, and other related issues would need an adequate mitigation. So to encounter this challenge, we start with exploring BCS capabilities, then onto SharePoint InfoPath and lastly turning to the programmable objects in the database.

The Solution

First, we take a decision to generate BCS Entities which will use Stored Procedures (SP). As an alert designer or developer one can easily visualize the high level benefits of using the SP to support the BCS (which in turn is our Data Access Layer for the application in question). With this we have the flexibility of using parameters, governing the resultant data expected, applying set of business rules within the SPs, and the tuning of queries can give enormous performance boost.

Secondly, designing and developing rich InfoPath based UI forms, completely out-of-the-box. We harvest a rich UI, New/Edit/View/List forms, with absolutely zero lines of code. Besides the regular CRUD operation support, we will also learn how to create Associations, Filters, and generating these InfoPath forms in the document forward.

BCS Entities: The Business cum Data Access Layer

Let’s go step by step and see how we can create this as an end to end solution.

Here is a snapshot of the Database Diagram that we are going to use in our solution. We are going to use Department, Employee and Job tables.

Create CRUD operations on Entities using Stored Procedures

Create the following Stored procedures on Department table for all the CRUD operations.

Stored Procedure Name

Parameters

Return parameters

InsertDepartment

@Deptname

@Location

@User

Deptno

UpdateDepartment

@Deptno

@Deptname

@Location

@User

 

DeleteDepartment

@Deptno

 

GetDepartment

@Deptno

Deptno

Deptname

Location

User

GetAlDepartments

 

Deptno

Deptname

Location

User

InsertEmployee

@Empname

@Jobno

@Deptno

@User

Empno

UpdateEmployee

@Empno

@Empname

@Jobno

@Deptno

@User

 

DeleteEmployee

@Empno

 

GetEmployee

@Empno

Empno

Empname

Jobno

Deptno

User

GetAllEmployees

 

Empno

Empname

Jobno

Deptno

User

Create Entity for Department table

Create an Entity for Department on which we are going to create all the CRUD operations. Also specify the Primary Key/Identity Column as given in the below snippet.

<Entity Namespace=”DepartmentDB” Version=”1.0.0.0″ EstimatedInstanceCount=”10000″ Name=”Department” DefaultDisplayName=” Department “>

<Identifiers>

<Identifier TypeName=”System.Int32″ Name=”Deptno” />

</Identifiers>

. . . . .

</Entity>

Generate CRUD operations on Department using Stored Procedures

You might have often used SharePoint Designer to easily generate CRUD operations on a table. But when it comes to using Stored Procedures, it’s not often easy. SharePoint Designer might not help in this case. We will have to create operations manually. Let us see how to generate CRUD operations using Stored Procedures. Create an element Methods in the Entity Element and start adding all the operations.

Let’s start creating the following operations on the Department Entity.

1. Create

2. Update

3. Read Item

4. Read List

If you are using SharePoint Designer to generate operations on a Table then the BackEndObjectType is set to Table automatically. But our plan is to generate CRUD operations using Stored Procedure. The following table shows you what needs to be set for a Table and Stored Procedure

Property Name

For Stored Procedure

For Table

BackEndObject

Name of the Stored Procedure

Name of the Table

BackEndObjectType

SqlServerRoutine

SqlServerTable

RdbCommandText

Name of the Stored Procedure

Insert the SQL Statement.

RdbCommandType

StoredProcedure

Text

Schema

Name of the Schema

Name of the Schema

For example ,

See the below XML snippet for a stored procedure. For every operation the following properties need to be set if you are calling a stored procedure.

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>[StoredProcedureName]</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>NameoftheStoredProcedure</Property>

<Property Name=”RdbCommandType” Type=”System.Data.CommandType, System.Data, Version=2.0.0.0,

Culture=neutral,

PublicKeyToken=b77a5c561934e089″>StoredProcedure</Property>

<Property Name=”Schema” Type=”System.String”>[SchemaName]</Property>

</Properties>

Before generating all the operations, you need to note few things

1. All CRUD operations should return and pass same set of parameters , else New/Edit/View will not get generated. This indicates, the set of parameters that you return from ReadItem and ReadList should be the same and also the parameters that you pass to Create and Update should also be the same. In case you have a parameter in ReadItem and you do not return it in ReadList and do not pass that parameter in Create or Delete then the forms do not get generated.

2. Parameters are case sensitive. Make sure the parameter names are same and they are case sensitive. Forms will not get generated if the names are different or case sensitive. You might receive the following error if the parameters are case sensitive

Could not save changes. No Finders available in the View Group associated with the SpecificFinder (Read Item) operation ‘ReadItem’ on Entity (External Content Type) with Namespace = ‘XXXXX’, Name =’XXXX’. The list cannot be created

3. SPs should be created before creating the External lists. Though you created CRUD operations on Entities, if you do not have corresponding Stores procedures, then the forms do not get generated.

Create Item Operation

Write the Create method with all the input parameters required to be passed to InsertDepartment Stored Procedure.

<Method Name=”Create” DefaultDisplayName=”Department Create”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>InsertDepartment</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>InsertDepartment</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Deptname” >

<TypeDescriptor TypeName=”System.String” CreatorField=”true” Name=”DeptName” >

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@Location” >

<TypeDescriptor TypeName=”System.String” CreatorField=”true” Name=”Location” >

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@User”>

<TypeDescriptor TypeName=”System.String” CreatorField=”true” Name=”User”>

</TypeDescriptor>

</Parameter>

<Parameter Direction=”Return” Name=”Create”>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”Create”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”CreateElement”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” IdentifierName=”Deptno” Name=”Deptno” />

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Creator” ReturnParameterName=”Create” ReturnTypeDescriptorPath=”Create[0]” Default=”true” Name=”Create” DefaultDisplayName=”Department Create”>

</MethodInstance>

</MethodInstances>

</Method>

Update Item Operation

Write the Update method by passing the Department Id with all the input parameters the need to be modified into the UpdateDepartment Stored Procedure.

<Method Name=”Update” DefaultDisplayName=”Department Update”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>UdpateDepartment</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>UdpateDepartment</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Deptno”>

<TypeDescriptor TypeName=”System.Int32″ UpdaterField=”true” IdentifierName=”Deptno” Name=”Deptno” />

</Parameter>

<Parameter Direction=”In” Name=”@Deptname”>

<TypeDescriptor TypeName=”System.String” UpdaterField=”true” Name=”Deptname”>

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@Location”>

<TypeDescriptor TypeName=”System.String” UpdaterField=”true” Name=”Location”>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Updater” Name=”Update” DefaultDisplayName=”Employee Update”>

</MethodInstance>

</MethodInstances>

</Method>

Delete Item Operation

Write the Delete method by passing the DepartmentId that you want to delete into the DeleteDepartment Stored Procedure.

<Method Name=”Delete” DefaultDisplayName=”Department Delete”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>DeleteDepartment</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>DeleteDepartment</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Deptno”>

<TypeDescriptor TypeName=”System.Int32″ IdentifierName=”Deptno” Name=”Deptno” />

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Deleter” Name=”Delete” DefaultDisplayName=”Department Delete”>

</MethodInstance>

</MethodInstances>

</Method>

Read Item Operation

Write a Read Item method which returns the Department values for the specified Department Id using the GetDepartment Stored Procedure

<Method Name=”Read Item” DefaultDisplayName=”Department Read Item”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>GetDepartment</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>GetDepartment</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Deptno”>

<TypeDescriptor TypeName=”System.Int32″ IdentifierName=”Deptno” Name=”Deptno” />

</Parameter>

<Parameter Direction=”Return” Name=”Read Item”>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”Read Item”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”Read ItemElement”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” IdentifierName=”Deptno” Name=”Deptno” />

<TypeDescriptor TypeName=”System.String” Name=”Deptname”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”Location”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”User”>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”SpecificFinder” ReturnParameterName=”Read Item” ReturnTypeDescriptorPath=”Read Item[0]” Default=”true” Name=”Read Item” DefaultDisplayName=”Department Read Item”>

<Properties>

<Property Name=”LastDesignedOfficeItemType” Type=”System.String”>None</Property>

</Properties>

</MethodInstance>

</MethodInstances>

</Method>

</MethodInstances>

</Method>

Read List Operation

Write the Read List method which would return all the Departments by calling GetAllDepartments Stored Procedure.

<Method Name=”Read List” DefaultDisplayName=”Department Read List”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>GetAllDepartments</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>GetAllDepartments</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”Return” Name=”Read List”>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”Read List”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”Read ListElement”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” IdentifierName=”Deptno” Name=”Deptno” />

<TypeDescriptor TypeName=”System.String” Name=”Deptname”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”Location”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”User”>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Finder” ReturnParameterName=”Read List” Default=”true” Name=”Read List” DefaultDisplayName=”Department Read List”>

<Properties>

<Property Name=”RootFinder” Type=”System.String”></Property>

<Property Name=”UseClientCachingForSearch” Type=”System.String”></Property>

</Properties>

</MethodInstance>

</MethodInstances>

</Method>


Create Entity for Employee table

Now, create an Entity for Employee table with all the CRUD Operations.

Here is the XML snippet for the Employee entity with all the CRUD Operations

<Entity Namespace=”EmployeeDB” Version=”1.0.0.0″ EstimatedInstanceCount=”10000″ Name=”Employee” DefaultDisplayName=”Employee”>

<Identifiers>

<Identifier TypeName=”System.Int32″ Name=”Empno” />

</Identifiers>

<Methods>

<Method Name=”Create” DefaultDisplayName=”Employee Create”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>InsertEmployee</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>InsertEmployee</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Empname” >

<TypeDescriptor TypeName=”System.String” CreatorField=”true” Name=”Empname” >

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@Jobno” >

<TypeDescriptor TypeName=”System.Int32″ CreatorField=”true” Name=”Jobno” >

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@Deptno” >

<TypeDescriptor TypeName=”System.Int32″ CreatorField=”true” Name=”Deptno” >

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@User”>

<TypeDescriptor TypeName=”System.String” CreatorField=”true” Name=”User”>

</TypeDescriptor>

</Parameter>

<Parameter Direction=”Return” Name=”Create”>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”Create”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”CreateElement”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” IdentifierName=”Empno” Name=”Empno” />

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Creator” ReturnParameterName=”Create” ReturnTypeDescriptorPath=”Create[0]” Default=”true” Name=”Create” DefaultDisplayName=”Employee Create”>

</MethodInstance>

</MethodInstances>

</Method>

<Method Name=”Update” DefaultDisplayName=”Employee Update”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>UdpateEmployee</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>UdpateEmployee</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Empno”>

<TypeDescriptor TypeName=”System.Int32″ UpdaterField=”true” IdentifierName=”Empno” Name=”Empno” />

</Parameter>

<Parameter Direction=”In” Name=”@Empname”>

<TypeDescriptor TypeName=”System.String” UpdaterField=”true” Name=”Empname”>

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@Jobno”>

<TypeDescriptor TypeName=”System.Int32″ UpdaterField=”true” Name=”Jobno”>

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@Deptno”>

<TypeDescriptor TypeName=”System.Int32″ UpdaterField=”true” Name=”Deptno”>

</TypeDescriptor>

</Parameter>

<Parameter Direction=”In” Name=”@User”>

<TypeDescriptor TypeName=”System.String” UpdaterField=”true” Name=”User”>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Updater” Name=”Update” DefaultDisplayName=”Employee Update”>

</MethodInstance>

</MethodInstances>

</Method>

<Method Name=”Delete” DefaultDisplayName=”Employee Delete”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>DeleteEmployee</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>DeleteEmployee</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Empno”>

<TypeDescriptor TypeName=”System.Int32″ IdentifierName=”Empno” Name=”Empno” />

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Deleter” Name=”Delete” DefaultDisplayName=”Employee Delete”>

</MethodInstance>

</MethodInstances>

</Method>

<Method Name=”Read List” DefaultDisplayName=”Employee Read List”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>GetAllEmployees</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>GetAllEmployees</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”Return” Name=”Read List”>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”Read List”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”Read ListElement”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” IdentifierName=”Empno” Name=”Empno” />

<TypeDescriptor TypeName=”System.String” Name=”Empname”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.Int32″ Name=”Jobno”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.Int32″ Name=”Deptno”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”User”>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Finder” ReturnParameterName=”Read List” Default=”true” Name=”Read List” DefaultDisplayName=”Employee Read List”>

<Properties>

<Property Name=”RootFinder” Type=”System.String”></Property>

<Property Name=”UseClientCachingForSearch” Type=”System.String”></Property>

</Properties>

</MethodInstance>

</MethodInstances>

</Method>

<Method Name=”Read Item” DefaultDisplayName=”Employee Read Item”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>[dbo].[GetEmployee]</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>[dbo].[GetEmployee]</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Empno”>

<TypeDescriptor TypeName=”System.Int32″ IdentifierName=”Empno” Name=”Empno” />

</Parameter>

<Parameter Direction=”Return” Name=”Read Item”>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”Read Item”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”Read ItemElement”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” IdentifierName=”Empno” Name=”Empno” />

<TypeDescriptor TypeName=”System.String” Name=”Empname”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.Int32″ Name=”Jobno”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.Int32″ Name=”Deptno”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”User”>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”SpecificFinder” ReturnParameterName=”Read Item” ReturnTypeDescriptorPath=”Read Item[0]” Default=”true” Name=”Read Item” DefaultDisplayName=”Employee Read Item”>

<Properties>

<Property Name=”LastDesignedOfficeItemType” Type=”System.String”>None</Property>

</Properties>

</MethodInstance>

</MethodInstances>

</Method>

</Methods>

</Entity>

Creating Associations

Let us create an association on Employee table. When you generate the New/Edit forms on the Employee Entity you will see that the Deptno just displays a textbox. And the user needs to enter the Dept ID as shown in the figure below ( we are going to generate the forms later in this section) .

In the above screen, it would have been good if there was an option to select the Departments available. To achieve this your need to create Association. Let us see, how the edit form would look like. Btw, later we are going to generate Infopath forms.

Let us see how to create an Association. Association has to be created on the master table ie., Department Entity.

Step 1: Create a Stored Procedure which would return the Employee details specific to a Department. Make sure it returns same set of parameters as the other CRUD operations return/Pass, else as specified earlier, the forms might not get generated.

Stored Procedure Name

Parameters

Return parameters

DepartmentEmployeeAssociation

@Deptno

Empno

Empname

JobNo

DeptNo

User

Step 2: Create Association on the Employee Entity. Create an association method by calling the above defined Stored Procedure.

<Method IsStatic=”false” Name=”DepartmentEmployee Association” DefaultDisplayName=”DepartmentEmployee Association”>

<Properties>

<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>

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<Parameters>

<Parameter Direction=”In” Name=”@Deptno”>

<TypeDescriptor TypeName=”System.Int32″ Name=”Deptno” IdentifierName=”Deptno” IdentifierEntityName=”Department” IdentifierEntityNamespace=”DepartmentDB” ForeignIdentifierAssociationName=”DepartmentEmployee Association” />

</Parameter>

<Parameter Direction=”Return” Name=”DepartmentEmployee Association”>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”DepartmentEmployee Association”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”DepartmentEmployee Association”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” Name=”Empno”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”Empname”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.Int32″ Name=”Jobno”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.Int32″ Name=”Deptno” IdentifierName=”Deptno” IdentifierEntityName=”Department” IdentifierEntityNamespace=”DepartmentDB” ForeignIdentifierAssociationName=”DepartmentEmployee Association” >

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”User”>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<Association Name=”DepartmentEmployee Association” Type=”AssociationNavigator” ReturnParameterName=”DepartmentEmployee Association” DefaultDisplayName=”DepartmentEmployee Association”>

<SourceEntity Namespace=”DepartmentDB” Name=”Department” />

<DestinationEntity Namespace=”EmployeeDB” Name=”Employee” />

</Association>

</MethodInstances>

</Method>

Step 3: Create Association Group on Employee. Create the AssociationGroup elemen under Entity element.

<AssociationGroups>

<AssociationGroup Name=”DepartmentEmployee Association”>

<AssociationReference AssociationName=”DepartmentEmployee Association” Reverse=”false” EntityNamespace=”EmployeeDB” EntityName=”Employee” />

</AssociationGroup>

</AssociationGroups>

Step 4: Apply the association to the Deptno parameter in the Employee entity in all the operations. Make sure this association is applied to all the methods, else you might have issue with the generation of the forms. You can see that, the similar association is done for Deptno parameter while defining association as well in the above XML association snippet. Here is a sample of Create Operation on Employee Entity where we are applying association on Deptno column. This should be done for all the operations ie., Create/Udpate/ReadItem/ReadList, in other words where ever Deptno is being referred.

<Parameter Direction=”In” Name=”@Deptno” >

<TypeDescriptor TypeName=”System.Int32″ CreatorField=”true” Name=”Deptno” IdentifierName=”Deptno” IdentifierEntityName=”Department” IdentifierEntityNamespace=”DepartmentDB” ForeignIdentifierAssociationName=”DepartmentEmployee Association” >

</TypeDescriptor>

</Parameter>

Now, the association is ready. Try to add / edit an item on the Employee External List and see that the Picker control is available. Here is the screenshot.

On the click on Show Picker control , you will be able to see the list of Departments from where you can make selection.

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

Associations can be used to create connections on BDC Webparts and also to display the Picker controls.

Performance Check on BDC Model

Ensure that your BDC Model has undergone the following changes

1) All operations are Stored Procedure based

2) All read list operations must have parameters

3) Stored procedures are internally configured to return a max number of records

4) Or, All read list operation must have filters set on maximum rows to return

5) If you don’t have the filter ON on read-list, then during BDC Model import warnings will be generated.

2.3 Importing BDC Model

Now, it is time to import the BDC Model. Open Central Admin, add select from “Manage Service Applications” under ”Application Management” . Select “Business Data Connectivity Services” that you configured .Click on Import and Import the BDC Model that you created just now.

Ensure that

· You have Secure Store Service turn 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, let us create External Lists for Department and Employee entities. To create an External List, from Site Actions select View All Site Content and select Create “Custom List”. Select “External List”.

Select the External Content Type as “Department”.

Here is the screenshot of how the Department External List would look like.

Similarly, create “Employee” External List.

Here is the screenshot of Employee External List.


SharePoint InfoPath: For Application Presentation Layer

Observe that in all the operations ie., New/Edit and View forms are automatically generated. Here is the screen shot of how the forms would look like for an Employee External List.

New Item

Edit Item

View Item


Generating InfoPath for External Lists

You can observe that all the forms generated look like the normal out of box forms of a custom list. But it is difficult to customize these forms. InfoPath is one of the solutions to customize. Here we will see how InfoPath forms can be generated.

Open SharePoint Designer and open the Department External list that you’ve created. You can see that all the 3 forms are listed in the External list under Forms.

To generate InfoPath forms for all these 3 operations, from “List Settings” ribbon menu under “Design forms in InfoPath Designer” select “Item”.

This option generates all the 3 InfoPath forms and then opens up the InfoPath designer. You can observe that 3 extra infopath forms displayifs.aspx, editifs.aspx and newifs.aspx would be created and they would be marked as default forms.

Here you can customize the whole form. Here is the screen shot of the InfoPath designer.

Enabling Search/Filters on External Content Types

Let us try to search in the Show Picker Dialog box. When you perform a search, this is what you see

This is because filters are not enabled on the External Content Type Read List method. Let us know what needs to be changed for this.

Step 1: Change the stored procedure GetAllEmployees to accept Deptname as a parameter and write the query accordingly.

Step 2: Update the ReadList with the Filter Descriptor as shown below

<Method Name=”Read List” DefaultDisplayName=”Department Read List”>

<Properties>

<Property Name=”BackEndObject” Type=”System.String”>GetAllDepartments</Property>

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

<Property Name=”RdbCommandText” Type=”System.String”>GetAllDepartments</Property>

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

<Property Name=”Schema” Type=”System.String”>dbo</Property>

</Properties>

<FilterDescriptors>

<FilterDescriptor Type=”Wildcard” FilterField=”Deptname” Name=”Filter”>

<Properties>

<Property Name=”CaseSensitive” Type=”System.Boolean”>false</Property>

<Property Name=”IsDefault” Type=”System.Boolean”>false</Property>

<Property Name=”UsedForDisambiguation” Type=”System.Boolean”>false</Property>

<Property Name=”UseValueAsDontCare” Type=”System.Boolean”>false</Property>

</Properties>

</FilterDescriptor>

</FilterDescriptors>

<Parameters>

<Parameter Direction=”In” Name=”@Deptname”>

<TypeDescriptor TypeName=”System.String” AssociatedFilter=”Filter” Name=”Deptname”>

<Properties>

<Property Name=”LogicalOperatorWithPrevious” Type=”System.String”>None</Property>

<Property Name=”Order” Type=”System.Int32″>0</Property>

</Properties>

<Interpretation>

<NormalizeString FromLOB=”NormalizeToNull” ToLOB=”NormalizeToEmptyString” />

</Interpretation>

<DefaultValues>

<DefaultValue MethodInstanceName=”Read List” Type=”System.Object” xsi:nil=”true” />

</DefaultValues>

</TypeDescriptor>

</Parameter>

<TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”Read List”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Name=”Read ListElement”>

<TypeDescriptors>

<TypeDescriptor TypeName=”System.Int32″ ReadOnly=”true” IdentifierName=”Deptno” Name=”Deptno” />

<TypeDescriptor TypeName=”System.String” Name=”Deptname”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”Location”>

</TypeDescriptor>

<TypeDescriptor TypeName=”System.String” Name=”User”>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Type=”Finder” ReturnParameterName=”Read List” Default=”true” Name=”Read List” DefaultDisplayName=”Department Read List”>

<Properties>

<Property Name=”RootFinder” Type=”System.String”></Property>

<Property Name=”UseClientCachingForSearch” Type=”System.String”></Property>

</Properties>

</MethodInstance>

</MethodInstances>

</Method>

Now, when you search items in Show Picker dialog, you will be able to get correct results.

The Application: A SharePoint site / Site collection

Creating a Site for Application

Let’s say you are planning to create a site, “My First BCS Site” for your application. Follow the standard site provisioning steps in Central Admin to create your site. (site collection from SharePoint perspective).

Apply applicable security settings to your site. Once this is done, create a content page to place the external Lists and connect them to show limited amount of data at a time.

Creating An Application Page

Create a “New Page” (select from “Site Actions”) with name “Employee Master” and add the 2 external list. To add the external list in to the page, from “Insert “ ribbon menu select External List and select Department and Employee External Lists. By default, when you add the external list, this is how the page would look like. It would by default display BDC Identity columns

Edit both the web parts and select view as “Department Read List” and “Employee Read List”. This is how the screen would look like once the views are applied.

We will include in the blogpost to connect these 2 External lists.