Lightning Tools

Introduction

In this tutorial I will show you how to connect to your External System using BCS Meta Man, and use an SQL Stored Procedure to return data to a SharePoint Business Data List. In this walkthrough we will be creating just a Finder Method with no filters so our Stored Procedure should have no parameters defined.

Prerequisites

• SharePoint Foundation 2010 or SharePoint Server 2010
• Visual Studio Professional or higher
• BCS Meta Man
• AdventureWorks SQL Database
• An SQL Stored Procedure without parameters such as

USE [AdventureWorks]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Lightning Tools Ltd
-- Create date: 07/16/2010
-- Description:    Sample Stored Procedure to return Customer information
-- =============================================
CREATE PROCEDURE [dbo].[uspGetCustomer] 
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT     CustomerID, Title, FirstName, LastName, EmailAddress
FROM         SalesLT.Customer
END

GO

Walkthrough

1. Open Visual Studio 2010
2. Add a New Project
3. Select the ‘SharePoint 2010’ node, and then the ‘Empty SharePoint Project’ project type
4. Give your project a name i.e BCSMetaManStoredProcedure 
   
     
   
5. Click ‘OK’
6. On the next dialog box, enter the name of where you want to deploy your model, and make sure you select ‘Deploy as a farm solution’ , click ‘Finish’
   
    
   
7. Add a new item to the newly created Project, either right-click on the Project in the Solution Explorer and Navigate to Add New Item, or select the Project Item and press ‘Ctrl+Shift+A’
8. Select the ‘Lightning Tools’ node, select ‘BCSMetaMan’ Item Type, give your model a name, such as ‘CustomerModel’ and click ‘Add’ 
   
   
   
9. If prompted, click Trial on the licensing dialog
   
   
   
10. To show the ‘BCS Meta Man Data Source Explorer’ we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way.
    
    
    
11. Click the ‘Add Connection’ button to show the ‘Connection Dialog’
12. Select the Data Source type, enter your Server name and also select whether to ‘Use Windows Authentication’ or you provide logon credentials, click ‘Connect’ 
    
    
    
13. The Data Source Explorer will now be populated with your Server databases
    
    
    
14. Expand your Database and find the Finder Method Stored Procedure
    
    
    
15. Drag the Stored Procedure onto the main Diagram
    
     
    
16. Select ‘Database’ as the Model Type and click ‘OK’
    
    
    
17. You will be prompted for a name of the External Content Type being created
    
    
    
18. Select the Method Type as ‘Finder’' , click ‘OK’
    
    
    
19. The External Content Type will be created on the Diagram
    
    
    
20. Press F5 to deploy, this will load up your SharePoint Page once deployed
21. Add a new Business Data List Web Part to your SharePoint page
22. Click on the ‘Open the tool pane’ link
23. Click on the icon to show the available External Content Types
    
     
    
24. Select our ‘BCSMetaManStoredProcedure.Customer’ External Content Type, click OK 
    
    
    
25. Click ‘OK’ on the tool pane
26. Click the ‘Add’
27. Your External System data is now displayed in the SharePoint Web Part
    
    

<phill/>

Click here to get an email when we update our blog