Looking for help?
|
|
BDC Meta Man - using Stored Procedures with MS SQL Server
|
5/7/2008 10:08 AM |
|
Using Stored Procedures with MS SQL Server
Many organisations only allow applications to access data via stored procedures rather than doing SQL select statements directly against tables. This is so DBAs have more control over what and who is querying their data.
Watch this screencast to see how you can use BDC Meta Man and Stored Procedures in SQL Server to bring data into Microsoft Office SharePoint Server 2007 using the Business Data Catalog:
Or Alternatively you can follow this walkthrough.
Aim:
To create 2 entities with associations from using blank entities and SQL Stored Procedures.
Prerequisites:
We will assume that you have 2 related tables in your database, in our example we will be using an Employee and a Phone Number table. You will also need a Finder, Specific Finder and ID Enumerator Stored Procedure for each table as well as an Association Stored Procedure which connects the two tables.
Our Stored Procedures are:
| SP_GetAllEmployees | Finder Method | SELECT * FROM tblEmployee |
| SP_GetEmployeeByID | Specific Finder Method | SELECT * FROM tblEmployee WHERE employee_id = @EmployeeID |
| SP_GetEmployeeIDs | ID Enumerator | SELECT employee_id FROM tblEmployee |
| SP_GetAllPhoneNumbers | Finder Method | SELECT * FROM PhoneNumbers |
| SP_GetPhoneNumberByID | Finder Method | SELECT * FROM tblPhoneNumber WHERE PhoneID = @PhoneID |
| SP_GetPhoneNumberIDs | ID Enumerator | SELECT PhoneID FROM tblPhone |
| SP_GetPhoneNumbersByEmployeeID | Association Method | SELECT * from tblPhoneNumbers where employee_id = @EmployeeID |
Expected Result to achieve a pass:
User should be able to:
· Use the Business Data List Web Part
· Use the Business Data Related List Web Part
· View the profile page
Steps to take:
1. Open BDC Meta Man
2. Choose ‘SQL Server’ Enter your connection details, click connect
3. Click to Highlight your Database from the top left
4. Right Click on the Design Surface and click ‘Create blank Entity’
5. Enter a meaningful name for the new entity, in our case ‘Employees’, click create.
6. You should now have a blank entity on your design surface:
7. Right click on your new entity and click ‘Edit Entity’
8. Navigate to the ‘Method’ tab, click Add and then choose ‘Stored Procedure’ from the list and then click Select.
9. You will be presented with a list of the available Stored Procedures from your Database. First of all add your Finder Method, Give your method a name i.e. ‘GetAllEmployees’ choose the stored procedure from the list ‘SP_GetAllEmployees’ then select the Finder Method Type radio button and click OK
10. Next add the Specific Finder in the same way, this time we will need to enter the Parameter Configuration. Double click your parameter from the list box. Enter the details and click OK on the Configure Parameter form
11. Choose the ‘Specific Finder’ Method Type, click OK
12. Add your ‘ID Enumerator’ Method now making sure you select the correct stored procedure and the Method Type is ‘ID Enumerator’
13. Your Methods should now look something like, click Save.
And your Design Surface should look like:
14. You need to repeat steps 4 – 12 with your second Entity, I have created a Finder, Specific Finder and an ID Enumerator Stored Procedure for Phone Numbers.
15. Once you have your second entity it should look like
16. Right click on both of your entities and choose ‘Edit Entity’, Select a column from the ‘Title Column’ drop down list which you want to link to the item profile, click save.
17. Now we need to create our association. On the Design Surface, Drag from your Source Entity to your Destination Entity, making sure you connect the columns which will form your association.
18. Choose the Stored Procedure which forms your Association, click select.
19. On the next screen select the Parameter for your association
20. Your Design Surface should now look like:
21. We are now done setting up our entities
22. Set the output path to be c:\appdef.xml from the Configuration Options
23. Generate XML
24. Import into MOSS Shared Service Provider
25. Open up a SharePoint site; add a BDC Data List Web Part, and a Business Data Related Web Part. Set the BDC Data list to our Employee data, and the Related List Web Part to the PhoneNumbers data.
26. Create your connections between the two Web Parts
27. You can test to see if this is working correctly by clicking the radio button next to a Employee and the Phone Number list will display the Phone Numbers of that Employee
28. One of the columns for you items will be linked to the profile page, this column will be the one you set up in step 4, click this to take you to the profile page.
Profile Page:
