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

    External Content Type on Design Surface

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

    Drag and drop your Creator Stored Procedure

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

    Map each parameter to each column

  6. Our Creator Method has been added

    External Content Type with Creator method

  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

    External Content Type with Updater method

  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

    Create an External List

  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

    Edit items through External List

  17. You will be presented with the editing dialog  

      Edit Item 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.

    Add new data through the external list

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

    New Item Dialog 

<phill/>