RSS Feed Feed your read!

Scot Hillier is an independent consultant and Microsoft SharePoint Most Valuable Professional focused on creating solutions for Information Workers with SharePoint, Office, and related .NET technologies. A frequent speaker at TechEd and SharePoint Connections, he is also the author many books on Microsoft technologies including 5 for SharePoint 2010. Scot splits his time between consulting on SharePoint projects and training for Critical Path Training. Scot is a former U. S. Navy submarine officer and graduate of the Virginia Military Institute. Scot can be reached at scot@shillier.com

Archives

January 2010 (17)
February 2010 (1)
March 2010 (1)
April 2010 (2)
May 2010 (1)
June 2010 (1)
August 2010 (4)
September 2010 (6)
October 2010 (3)
November 2010 (2)
February 2011 (1)
March 2011 (4)
April 2011 (3)
May 2011 (1)
August 2011 (1)
March 2012 (1)
April 2012 (1)
July 2012 (3)
August 2012 (1)
September 2012 (1)
October 2012 (2)
November 2012 (3)
January 2013 (2)
February 2013 (3)
March 2013 (3)
April 2013 (3)
May 2013 (1)
September 2013 (2)
November 2013 (5)
January 2014 (2)
February 2014 (2)
March 2014 (1)
April 2014 (1)
May 2014 (1)
July 2014 (1)

Links

IT Unity
SharePoint Team Blog
Andrew Connell's Blog

Tag Cloud

Apps, Books, Business Data Connectivity, Claims, Conferences, JavaScript, jQuery, MOSS 2007 Archived Post, PowerShell, REST and CSOM, Search, SharePoint 2010, SharePoint 2013, Web Content Management,

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