Business Data Connectivity Model – Finder Method

By

In our last blog post on the BCS we had an introduction to using a .NET assembly as a data source for the Business Connectivity Services. In this blog post we’ll go through all the steps to create a BCS shim using Visual Studio 2010 beta 2.

In this walk through we’ll refer to Entities and External Content Types. They are actually the same thing in the BCS. Entity is a hangover term from the Business Data Catalog and doesn’t look like it is completely going to go away for 2010.

This blog post has been published using Visual Studio 2010 beta 2. As of writing SharePoint 2010 beta 2 hasn’t been released yet so you can follow along with the Visual Studio bits, but you may have to wait a little while until the public beta of SharePoint 2010 for you to be able to deploy and test your BCS Model.

1, Open up Visual Studio 2010 and create a new project. Select the SharePoint 2010 project types and pick to create a new BCS Model

image

2, When the project is created you’ll see an Entity created on the model design surface.

image

You’ll also see two classes within the BdcModel1 folder called Entity1.cs and Entity1Service.cs. We are going to leave all of these things here so we can refer to them when we are building our own custom BCS Entity.

3, For this walk through we are going to make use of AdventureWorks2000. If you need to download this SQL Server database you can get it from here:

Download AdventureWorks2000 database

4, We want to create our own Entity to reflect Departments within our database. Drag and drop an Entity shape from the Visual Studio controls toolbox onto the bcs model design surface.

image

5, You’ll notice that when you created your blank BCS External Content Type a new EntityService.cs file was also automatically created for you. The BCS expects all your C# methods to reside in here that this particular entity will use.
Rename your Entity – Department

Also right click on the Methods section within the entity and choose to add a new method called GetDepartments

image

Notice that when we change the name of our Entity to Department, EntityService.cs has been renamed to DepartmentService.cs. If you open DepartmentService.cs you’ll see that when we created our BCS methods within our External Content Type shape it generated the method stubs for us in our service class.

6, Now we are going to write some code to bring the data back. Right click on your project and add a LINQ 2 SQL class. With the LINQ 2 SQL view open connect to your AdventureWorks2000 database in the server explorer and drag and drop the Departments table on to the design surface

image

7, When we are returning our data we could use the LINQ 2 SQL Department class that is automatically generated. I’ve found it better however to define our own Department class within the BCS model folder to use for a couple of reasons

– you need to define all the columns being returned by the BCS. The LINQ 2 SQL class may return properties that you do not want to be used within your BCS model. If you do use the LINQ 2 SQL class, and have class properties that are not defined within the BCS model you will get an error.

– taking this approach will help you later on if you want to bring data in from 2 different sources and present as a single External Content Type.

– I have just found it easier and nicer to use my own class 🙂 It will help with Unit Testing and other code cleanliness that we’ll cover at a later date.

So add a new C# class to your BDCModel1 folder called Department.cs and add properties for each column of data you’d like to make use of with the BCS

public class Department
{
public Int16 DepartmentId { get; set; }

public string Name { get; set; }

public string GroupName { get; set; }
}

8, Now we can write some code within our DepartmentService.cs class. Open it up and change the GetDepartments method signature to be:

public static IEnumerable<Department> GetDepartments()

9, Now we can write some LINQ 2 SQL to get our departments and return them, replace the line

throw new System.NotImplementedException();

with

DataClasses1DataContext db = new DataClasses1DataContext(“server=localhost;database=adventureworks2000;uid=***;pwd=***”);

return from d in db.Departments
select new Department
{
DepartmentId = d.DepartmentID,
Name = d.Name,
GroupName = d.GroupName
};

10, Now we need to build up our BCS model to understand the data that is being returned by our method. Go back to your BCS model view and right click in the Identifiers section of our Department Entity. Choose to add a new identifier and give it a name of DepartmentId. With this identifier selected within your Entity you’ll see in the Properties pane you can see more information about it. Change the Type Name to be System.Int16

image

11, Before describing our data to our BCS model we need to create a return parameter. In the BDC Method details window use the parameters drop down entitled <add a parameter> to create a new one.

image

Set the Direction of this parameter to be Return.

There’s a bit of moving around between toolpane’s I’m afriad but this is the most logical way I have found of building up the BCS model

12, Now we want to go back to the BDC Explorer view. Expand the tree so you get to our Entity and Method and select the TypeDescriptor we have just created by adding the parameter. With it selected you’ll see we can edit it’s values in the Properties window. Make sure you have the following values set:

Name : DepartmentList
Type Name : System.Collections.Generic.IEnumerable`1[[BdcModelProject2.BdcModel1.Department, BdcModel1]]
IsCollection : True

13, Right click on the DepartmentList node and choose to Add Type Descriptor

14, With this new Type Descriptor selected set the following property values:

Name : Department
Type Name : BdcModelProject2.BdcModel1.Department, BdcModel1
IsCollection : False

15, Now we need to build up each Type Descriptor for each column of data we want to be returned from our Department table. Right click on the Department Type Descriptor and add the following 3 TypeDescriptors:

Name : DepartmentId
Type Name : System.Int16
Identifier : DepartmentId

Name : Name
Type Name : System.String

Name : GroupName
Type Name : System.String

Our BDC Explorer tree should now look like this:

image

16, Now we need to create a MethodInstance element – this describes to the BCS what type of method ours is – for example whether it is a Finder, SpecificFinder etc

Click the Department Entity and view the BDC Method Details window. Use the MethodInstance drop down in the Instance section to ‘Create Finder Instance’.

image

With the instance selected set the following properties within the Properties window:

Default : True
Default Display Name : Department List
Return Parameter Name : parameter
Return Type Descriptor Name : DepartmentList

17, Now before you can deploy your model, check in DepartmentService.cs. Depending on the steps you have used to create your BCS model you may have accidentally created a second GetDepartments() method. If you have, simply delete the second one added and all will be good. (I’m trying to figure out the exact step that creates this duplicate method – will update when found).

18, Now we can build our project – and if it compiles OK press F5 to package it up and deploy it to SharePoint as a WSP.

Here’s the screenshot of our BCS External Content Type being used within the Business Data List Web Part

image

As you can see there are quite a lot of steps. The aim of BCS Meta Man is to compliment and build on the tools already within Visual Studio 2010. We’ll have a beta of BCS Meta Man ready the same time that SharePoint 2010 beta 2 ships.

In coming blog posts we’ll take a look at:

– creating a SpecificFinder and IdEnumerator method

– creating write back and update methods

– creating association methods

and plenty more! If you have any comments and feedback, especially on how you have been using the BCS tooling in Visual Studio please feel free to leave a comment.

<nick/>

[The information contained above is correct as of 01st November 2009. As of writing we are currently working with beta products and although we will do our best to update this post where necessary please note that by the time you are reading this some things will have changed]