Creator and Updater BCS Methods using Stored Procedures – BCS Meta Man

Introduction

In this tutorial we will show you how to use Stored Procedures to add a new record to a Microsoft SQL Server database using an External List and also to Update and existing record using BCS Meta Man.

We are going to follow on from the ‘BCS Meta Man – Using Stored Procedures – Adding a Specific Finder and IdEnumerator’ walk through.

Prerequisites

• SharePoint Foundation 2010 or SharePoint Server 2010
• Visual Studio Professional or higher
• BCS Meta Man
• AdventureWorks SQL Database
• An SQL Stored Procedure for the Specific Finder Method

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 single Customer information
-- =============================================
CREATE PROCEDURE [dbo].[uspGetCustomerByCustomerID]
    -- Add the parameters for the stored procedure here
    @CustomerID int = null
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,
            MiddleName,
            LastName,
            EmailAddress

FROM         SalesLT.Customer
WHERE     (CustomerID = @CustomerID)
END

GO

• A SQL Stored Procedure for the Creator Method for the Customer Table 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 create new Customer
-- =============================================

CREATE PROCEDURE [dbo].[uspCreateCustomer]
       @Title                       nvarchar(8),
       @FirstName                   nvarchar(50),
       @MiddleName                  nvarchar(50),
       @LastName                    nvarchar(50),
       @Suffix                      nvarchar(10),
       @CompanyName                 nvarchar(128),
       @Phone                       nvarchar(25),
       @EmailAddress                nvarchar(50)

AS
BEGIN
     SET NOCOUNT ON

     INSERT INTO SalesLT.Customer
          (
            NameStyle,
            Title,
            FirstName,
            MiddleName,
            LastName,
            Suffix,
            CompanyName,
            SalesPerson,
            EmailAddress,
            Phone,
            PasswordHash,
            PasswordSalt,
            rowguid,
            ModifiedDate
                      )
     VALUES
          (
            0,
            @Title,
            @FirstName,
            @MiddleName,
            @LastName,
            @Suffix,
            @CompanyName,
            'adventure-workslinda3',
            @EmailAddress,
            @Phone,
            'passwordhash',
            'salt',
            NEWID(),
            GetDate()

          ) select [CustomerID] from SalesLT.Customer

END

GO

• A SQL Stored Procedure for the Updater Method for the Customer Table 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 update Customer by Customer ID
  -- =============================================
  
  CREATE PROCEDURE [dbo].[uspUpdateCustomer]
         @CustomerID               int,
         @Title                    nvarchar(8) = null,
         @FirstName                nvarchar(50) = null,
         @MiddleName               nvarchar(50) = null,
         @LastName                 nvarchar(50) = null,
         @EmailAddress             nvarchar(50) = null
  AS
  BEGIN
       SET NOCOUNT ON
  
       UPDATE SalesLT.Customer
       SET
      [Title] = IsNull(@Title,Title),
      [FirstName] = IsNull(@FirstName,FirstName),
      [MiddleName] = IsNull(@MiddleName,MiddleName),
      [LastName] = IsNull(@LastName,LastName),
      [EmailAddress] = IsNull(@EmailAddress,EmailAddress)
      WHERE
      CustomerID = @CustomerID
  END
  
  
  GO

Walkthrough

1. Complete the ‘BCS Meta Man – Using Stored Procedures – Adding a Specific Finder and IdEnumerator’ Tutorial but make sure you use the Specific Finder Method Stored Procedure from above as the Update and the Specific Finder Method need to use the same columns.

2. You should have the following External Content Type on your Diagram

   

3. From the ‘BCS Meta Man Data Source Explorer’ drag and drop your Creator method Stored Procedure onto the Customer External Content Type.

   

4. Select ‘Creator’ as the ‘Method Type’
5. For each Parameter select the appropriate column to map it to, click ‘OK’

   

6. Our Creator Method has been added

   

7. Next drag and drop your Updater method onto the ‘Customer’ External Content Type
8. Again, map the the Parameters to the columns, click ‘Save’
9. The complete External Content Type will look like the following

   

10. Press F5 to deploy, this will load up your SharePoint Page once deployed
11. Next we need to create an External List
12. While viewing your SharePoint site through the browser, click on Site Actions –> More Options…
13. From the list of items to create select ‘External List’ and click the ‘Create’ button
14. In the configuration screen that opens up, give your External List a name and use the External Content Type picker to select the External Content Type we have just deployed to SharePoint

    

15. Click ‘Create’
16. You can hover over the first column of your list and you’ll see a drop down appear. If you click the drop down arrow you’ll get a number of options for that particular row of data – one of which will be Edit Item

    

17. You will be presented with the editing dialog  

     

18. This will save the changes directly back to your external data system that the external content type is hooked up to.
19. While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button.

    

20. This will bring up the ‘New Item’ dialog where you can add a new customer.

     

<phill/>