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 **
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 :
SELECT * FROM Customers
2, Name : spGetCustomerById
BDC type : SpecificFinder
Stored Procedure :
SELECT * FROM Customers WHERE customerId = @customerId
3, Name : spGetCustomerIds
BDC type : IdEnumerator
Stored Procedure :
SELECT CustomerId FROM customers
4, Name : spGetOrders
BDC type : Finder
SELECT * FROM Orders
5, Name : spGetOrdersByCustomerId
BDC type : Association method
SELECT * FROM Orders WHERE CustomerId = @customerId
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
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
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
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
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:
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…
and your design surface like this…
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
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
Then on the next screen select the stored procedure you want to use for the association – select spGetOrdersByCustomerId.
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.
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…
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:
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.