Microsoft SQL Server stored procedures and the Business Data Catalog

Stored procedures are the only way to access data in some organizations as they can be locked down and may also contain business logic. It is very good news that the Business Data Catalog can work with stored procedures just as well as with direct SQL queries. This article will demonstrate how you can use BDC Meta Man to define your entities and therefore use with the BDC.

** Please note that you need the professional version of BDC Meta Man to be able to use SQL Server stored procedures **

We also have a screencast recorded that demonstrates how to configure stored procedures for a single entity.

The first thing to be aware of is that you may need to build your own stored procedures specifically to work with the BDC. Each of your entities defined within your Business Data Catalog application definition file requires certain method to allow you to use your business data in certain ways within SharePoint.

A Finder BDC method should return rows of data – this is used in the BDC data list web part

A SpecificFinder BDC method should return a single row data which is specified by a parameter that is passed in and used corresponds to the primary key field of the data – this BDC method is used to create the profile page and, search, and the business data column

An IdEnumerator method should simply return the id column (or columns if you have a composite key). This method is used only by search.

If you want to create associations between your Business Data Catalog entities then will need to create stored procedures that implement them. For example imagine you have a customer and order entity and you want to bring back the particular orders for a customer. You would need to create a stored procedure called GetOrdersByCustomerId which accepts @customerId as a parameter and filters the orders being returned by this value.

So having said all of that here are the stored procedure we are going to make use of through this walk through:

1, Name : spGetCustomers
BDC type : Finder
Stored Procedure :
BEGIN
SELECT * FROM Customers
END

2, Name : spGetCustomerById
BDC type : SpecificFinder
Stored Procedure :
@customerId int
AS
BEGIN
SELECT * FROM Customers WHERE customerId = @customerId
END

3, Name : spGetCustomerIds
BDC type : IdEnumerator
Stored Procedure :
BEGIN
SELECT CustomerId FROM customers
END

4, Name : spGetOrders
BDC type : Finder
BEGIN
SELECT * FROM Orders
END

5, Name : spGetOrdersByCustomerId
BDC type : Association method
Stored Procedure
@customerId int
AS
BEGIN
SELECT * FROM Orders WHERE CustomerId = @customerId
END

Now you have your necessary stored procedures created we can open BDC Meta Man. Choose to connect to SQL Server and enter the server name and necessary credentials. Creating entities from stored procedures is a little different that using tables or views as there needs to be a little more configuration by you.

Select the database that you wish to use

select data source

With a database selected you can right click on the design surface and choose ‘Create blank Entity’. In the form that pops up enter a new name for your entity, in our case Customer, and click create. This will create a blank Business Data Catalog entity on your design surface

create a blank BDC entity

Now we need to get our BDC entity to use the stored procedures. Right click on the entity that is on the design surface and select ‘Edit Entity’. The Entity Management form will show up. Click on the methods tab and then the Add button. Finally we need to select the method type to be stored procedure

add a new BDC method to our entity

We are now presented with the stored procedure configuration window. Here we need to
-give our BDC method a name
-select the stored procedure we want to use
-configure any parameters
-select the BDC method type

configure the stored procedure

Once we have our Customer Finder stored procedure configured as above click OK and it will be added to our entity.

Now we want to configure our other stored procedures for the Customer entity – so back in Entity Management window click the Add button again and select stored procedure. Set/select the following:
-Method Name : GetCustomerById
-Stored Procedure : spGetCustomerById

When you select this stored procedure you will notice that a row is added to the parameters list. We need to quickly configure this parameter so double click on it in the list and a new form will open up, configure it as below:

configure the stored procedures parameter

Finally select the Business Data Catalog method type to be SpecificFinder and click OK.

Now you can add your IdEnumerator method in the same way as the previous two. Once you have your BDC methods setup the Entity Management screen should look as so…

you methods are listed for you

and your design surface like this…

entity on the design surface

Now go and configure the Order entity so that your Business Data Catalog application definition file will have 2 entities. The only method we need to add is the Finder which uses the spGetOrders stored procedure. Once we have that added our design surface should look as below with two entities on it

two entities on the design surface

Now we need to create our association method between our entities. Drag and drop between the fields that link your two entities together going from the Primary Key to the Foreign Key. Upon dragging between the two fields and letting go of the mouse button a new window will open up which lets you select how to setup your association method, select stored procedure

select the stored procedure to use

Then on the next screen select the stored procedure you want to use for the association – select spGetOrdersByCustomerId.

select the stored procedure

On the next window that pops up we need to tell the BDC how to map the customer entity identifier to the stored procedure parameter.

map the input parameter to the bdc identifier

You should only have a single value in the drop down to map anyway. There may be more if you have a complex relationship with composite keys – but in general I would try to avoid composite keys when working with the Business Data Catalog as much as possible. Now when we have finished configuring our Identifier and parameter and clicked OK our design surface should look like this…

you association method is created

We’ve now completed setting up our BDC entities. From the configuration options choose a place to generate your application definition file and then click the ‘Play’ button in the top right of the application or from the main menu choose Build -> Generate All.

Now you need to import your Business Data Catalog application defintion file into your Shared Service Provider, rather than go through these steps again please see our BDC getting started article to take you through those steps.

Configuring the Web Parts

Now we have the Business Data Catalog setup with our data source we can display the information in SharePoint. Browse to your SharePoint site and place the page into edit mode. Then click on ‘Add a Web Part’ in the zone you want to use and select the Business Data List and Business Data Related List web parts. When they are added to your page you will need to modify the List web part to use your Customer entity, and the Related List web part to use your Order entity. Once these are configured you will need to connect the two web parts together as displayed in the image below:

connect the web parts together

Now the connections between the web parts are complete you can select a customer, and you’ll see the orders associated with that customer appear in the Business Data Related List Web Part.

displaying your BDC data