How to Use Stored Procedures in Business Connectivity Services 

Tags:

Many BCS demos uses direct access to database tables, and never really show how to use stored procedures.
 
When a stored procedure with input parameters is used for Finders, SpecificFinders, and Deleters, the wizard will present a screen that will allow you to assign filters to the parameters. These filter values can then be set in the view definition of the External List by an end user or as an input parameter in the Business Data web parts. The following code shows a stored procedure that supports a wildcard as an input parameter.

<Method IsStatic="false" Name="NamesByWildcardProcedure">
 <Properties>
  <Property Name="BackEndObject" Type="System.String">
   sp_GetNamesWildcard
  </Property>
  <Property Name="BackEndObjectType" Type="System.String">
   SqlServerRoutine
  </Property>
  <Property Name="RdbCommandText" Type="System.String">
   [dbo].[sp_GetNamesWildcard]
  </Property>
  <Property Name="RdbCommandType"
   Type="System.Data.CommandType, System.Data, Version=2.0.0.0,
   Culture=neutral, PublicKeyToken=b77a5c561934e089">
   StoredProcedure
  </Property>
  <Property Name="Schema" Type="System.String">
   dbo
  </Property>
 </Properties>
 <FilterDescriptors>
  <FilterDescriptor Type="Wildcard" FilterField="LastName" Name="Wildcard">
   <Properties>
    <Property Name="CaseSensitive" Type="System.Boolean">
     false
    </Property>
    <Property Name="IsDefault" Type="System.Boolean">
     false
    </Property>
    <Property Name="UsedForDisambiguation" Type="System.Boolean">
     false
    </Property>
   </Properties>
  </FilterDescriptor>
 </FilterDescriptors>
 <Parameters>
  <Parameter Direction="In" Name="@wildcard">
   <TypeDescriptor TypeName="System.String"
    AssociatedFilter="Wildcard" Name="@wildcard">
    <Properties>
     <Property Name="Order" Type="System.Int32">0</Property>
    </Properties>
    <DefaultValues>
     <DefaultValue
      MethodInstanceName="NamesByWildcardProcedure" Type="System.String">
      A
    </DefaultValue>
    </DefaultValues>
   </TypeDescriptor>
  </Parameter>
  <Parameter Direction="Return" Name="NamesByWildcardProcedure">
   ...
  </Parameter>
 </Parameters>
 <MethodInstances>
  <MethodInstance Type="Finder"
   ReturnParameterName="NamesByWildcardProcedure"
   Name="NamesByWildcardProcedure"
   DefaultDisplayName="Names by Wildcard Procedure">
  </MethodInstance>
 </MethodInstances>
</Method>

In the code, notice that a FilterDescriptor of type Wildcard has been added to the model. This filter is associated with the LastName field and the @wildcard input parameter. What this does is to pass the value of the filter into the @wildcard input parameter. This means that the stored procedure must have a parameter by that exact name available as shown in the following code.

CREATE PROCEDURE [dbo].[sp_GetNamesWildcard]
@wildcard nvarchar(10)
AS
SELECT ID,Title,FirstName,MiddleName,LastName,Suffix,EMailAddress,Phone
FROM MiniCRM.Names
WHERE LastName LIKE @wildcard + '%'
 
When a stored procedure is used as a Creator, you must return the ID of the new entity instance from the procedure. The following stored procedure uses an identity column, and returns that from the procedure.
 
CREATE PROCEDURE NewEmployee
@Username nvarchar (255),
@FirstName nvarchar (255),
@LastName nvarchar (255),
@SecondLanguage nvarchar (255)
AS
INSERT INTO [dbo].[Employees]([Username] , [FirstName] , [LastName] , [SecondLanguage])
VALUES(@Username , @FirstName , @LastName , @SecondLanguage)
SELECT [EmployeeID] FROM [dbo].[Employees] WHERE [EmployeeID] = SCOPE_IDENTITY()
 
In The SPD wizard, the Identifier must be set on the Return value for the model to work.
 
 
 
 
Posted by Scot Hillier on 18-Oct-10
0 Comments  |  Trackback Url  |  Link to this post | Bookmark this post with:        
 

Links to this post

Comments