In our previous blog posts we have demonstrated how to build up a BCS Model with Visual Studio 2010 and define BCS methods (Finder, Specific Finder, Id Enumerator, Creator and Updater) for the AdventureWorks database Department table.
In this blog post we are going to show you how to configure association between two BCS external content types created in the Visual Studio 2010 BCS designer. In the previous articles we have used the Department table, so Employee table is be the best choice for the second external content type and we’ll create association between the Department and Employee tables. Firstly, we need to create Employee BCS external content type, so please open the project which you use for Department external content type, and using exactly the same steps create Employee external content type. You don’t need to define all the methods, Finder and Specific finder methods are enough.
1) Make sure you define a Finder and SpecificFinder method for Employee an Employee External Content Type. You can use the BCS Data List Web Part and BCS Item Web Part to check if data is being retrieved properly. Here is how it should look like in BCS diagram in the Visual Studio 2010.
The Employee table contains lots of columns, in this example we have used EmployeeID, FirstName, LastName, Title, DepartmentId.
[click images for a larger view]
2) In Visual Studio 2010 view the toolbox and then click on the “Association”.
3) Create an association between Department and Employee, so after you have clicked the Association in the toolbox, click on the Department and then on the Employee.
4) New dialog will be shown where you can configure association methods.
5) In the first section – Identifier mapping , you can map identifiers from the source entity to the destination entity, and in the second section you can add/remove association navigator methods. In our case we will remove the EmployeeToDepartment method and will leave only DepartmentToEmployee method.
6) Click “OK” button and it will create association between Department(Source) and Employee(Destination) BCS external content types.
7) Next we need to write the C# code for association method. Open DepartmentService.cs file and there you will find DepartmentToEmployee method. Replace the body of that method with the following C# code.
DataClasses1DataContext dcontext = new DataClasses1DataContext(“server=localhost;database=adventureworks2000;uid=***;pwd=***”); Security=true”);
IEnumerable<Employee> records = from record in dcontext.Employees
where record.DepartmentID == departmentId
select new Employee
EmployeeID = record.EmployeeID,
FirstName = record.FirstName,
LastName = record.LastName,
Title = record.Title,
DepartmentId = record.DepartmentID
8) Rebuild and deploy the solution by pressing CTRL+F5. Go to your SharePoint site and add a BCS Data List Web Part and BCS Related Data List web parts. Choose Department as the type for BCS Data List Web Part and Employee as the type for the related list web part, then make a connection between them so the BCS List Web part should send data to the Related list web part. When everything is done, you can selec
t a Department record in BCS List web part and it will call the association method and show appropriate Employee records in the Related List Web Part.