How to: Define a Model with Modification Stored Procedures

  • Article

The Entity Framework enables you to specify stored procedures to be used when modifying entity data. These stored procedures replace the methods generated by the Entity Framework. Stored procedures are called implicitly, so no changes are required to the data model defined in the conceptual schema or existing application code.

Note

The recommended way to specify stored procedures for modifying entity data is to use the ADO.NET Entity Data Model Tools. For more information, see Walkthrough: Mapping an Entity to Stored Procedures and How to: Map Modification Functions to Stored Procedures.

This topic shows how to define modification stored procedures for the SalesOrderDetail entity type in the Adventure Works Sales Model. To use this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define the Model and Mapping Files.

Note

If you do not map all three of the insert, update, and delete operations of an entity type to stored procedures, the unmapped operations will fail if executed at runtime, and an UpdateException will be thrown.

To define modification stored procedures for the SalesOrderDetail entity type

  1. Execute the following Transact-SQL scripts to create the modification stored procedures in the AdventureWorks database:

    • CreateSalesOrderDetail procedure:

      USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.CreateSalesOrderDetail', 'P' ) IS NOT NULL 
DROP PROCEDURE dbo.CreateSalesOrderDetail;
GO
CREATE PROCEDURE [dbo].[CreateSalesOrderDetail] 
   @SalesOrderID int,
   @CarrierTrackingNumber nvarchar(25),
   @OrderQty smallint,
   @ProductID int,
   @SpecialOfferID int,
   @UnitPrice money,
   @UnitPriceDiscount money,
   @rowguid uniqueidentifier,
   @ModifiedDate datetime

AS

INSERT INTO [AdventureWorks].[Sales].[SalesOrderDetail]
           ([SalesOrderID]
           ,[CarrierTrackingNumber]
           ,[OrderQty]
           ,[ProductID]
           ,[SpecialOfferID]
           ,[UnitPrice]
           ,[UnitPriceDiscount]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           (@SalesOrderID,
           @CarrierTrackingNumber,
           @OrderQty,
           @ProductID,
           @SpecialOfferID,
           @UnitPrice,
           @UnitPriceDiscount,
           @rowguid,
           @ModifiedDate)

select SalesOrderDetailID, LineTotal
 from [AdventureWorks].[Sales].[SalesOrderDetail]
 where SalesOrderID = @SalesOrderID and SalesOrderDetailID = scope_identity()

    • UpdateSalesOrderDetail procedure:

      USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.UpdateSalesOrderDetail', 'P' ) IS NOT NULL 
DROP PROCEDURE dbo.UpdateSalesOrderDetail;
GO

CREATE PROCEDURE [dbo].[UpdateSalesOrderDetail]
   @OrderQty smallint, 
   @SalesOrderDetailID int,
   @SalesOrderID int

AS
UPDATE [AdventureWorks].[Sales].[SalesOrderDetail]
   SET [OrderQty] = @OrderQty
 WHERE SalesOrderDetailID = @SalesOrderDetailID

    • DeleteSalesOrderDetail procedure:

      USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.DeleteSalesOrderDetail', 'P' ) IS NOT NULL 
DROP PROCEDURE dbo.DeleteSalesOrderDetail;
GO

CREATE PROCEDURE [dbo].[DeleteSalesOrderDetail] 
   @SalesOrderDetailID int,
   @SalesOrderID int 
AS
DELETE FROM [AdventureWorks].[Sales].[SalesOrderDetail]
      WHERE SalesOrderDetailID = @SalesOrderDetailID

  2. Add the following function definitions to the top level Schema element of the .ssdl schema file for the AdventureWorks Sales storage model:

            <Function Name="CreateSalesOrderDetail" Aggregate="false"
                  BuiltIn="false" NiladicFunction="false"
                  IsComposable="false"
                  ParameterTypeSemantics="AllowImplicitConversion"
                  Schema="dbo">
          <Parameter Name="SalesOrderID" Type="int" Mode="In" />
          <Parameter Name="CarrierTrackingNumber" Type="nvarchar" Mode="In" />
          <Parameter Name="OrderQty" Type="smallint" Mode="In" />
          <Parameter Name="ProductID" Type="int" Mode="In" />
          <Parameter Name="SpecialOfferID" Type="int" Mode="In" />
          <Parameter Name="UnitPrice" Type="money" Mode="In" />
          <Parameter Name="UnitPriceDiscount" Type="money" Mode="In" />
          <Parameter Name="rowguid" Type="uniqueidentifier" Mode="In" />
          <Parameter Name="ModifiedDate" Type="datetime" Mode="In" />
        </Function>

        <Function Name="UpdateSalesOrderDetail" Aggregate="false"
                  BuiltIn="false" NiladicFunction="false"
                  IsComposable="false"
                  ParameterTypeSemantics="AllowImplicitConversion"
                  Schema="dbo">
          <Parameter Name="OrderQty" Type="smallint" Mode="In"/>
          <Parameter Name="SalesOrderDetailID" Type="int" Mode="In"/>
          <Parameter Name="SalesOrderID" Type="int" Mode="In"/>
        </Function>

        <Function Name="DeleteSalesOrderDetail" Aggregate="false"
                  BuiltIn="false" NiladicFunction="false"
                  IsComposable="false"
                  ParameterTypeSemantics="AllowImplicitConversion"
                  Schema="dbo">
          <Parameter Name="SalesOrderDetailID" Type="int" Mode="In"/>
          <Parameter Name="SalesOrderID" Type="int" Mode="In"/>
        </Function>

  3. Insert the following XML fragment within the EntityTypeMapping element for the SalesOrderDetail in the EntitySetMapping. Do this in the .msl mapping file for the AdventureWorks Sales storage model.

      <ModificationFunctionMapping >
    <InsertFunction
       FunctionName="AdventureWorksModel.Store.CreateSalesOrderDetail">
        <ScalarProperty Name="CarrierTrackingNumber"
              ParameterName="CarrierTrackingNumber" Version="Current"/>
        <ScalarProperty Name="OrderQty" ParameterName="OrderQty"
              Version="Current"/>
        <ScalarProperty Name="ProductID" ParameterName="ProductID" Version="Current"/>
         <ScalarProperty Name="SpecialOfferID"
              ParameterName="SpecialOfferID" Version="Current"/>
        <ScalarProperty Name="UnitPrice" 
              ParameterName="UnitPrice" Version="Current"/>
        <ScalarProperty Name="UnitPriceDiscount"
              ParameterName="UnitPriceDiscount" Version="Current"/>
        <ScalarProperty Name="rowguid" ParameterName="rowguid"
              Version="Current"/>
        <ScalarProperty Name="ModifiedDate"
              ParameterName="ModifiedDate" Version="Current"/>
        <AssociationEnd
           AssociationSet="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
          From="SalesOrderDetail" To="SalesOrderHeader">
        <ScalarProperty Name="SalesOrderID"
                   ParameterName="SalesOrderID" />
        </AssociationEnd>
        <ResultBinding ColumnName="SalesOrderDetailID"
                   Name="SalesOrderDetailID" />
         <ResultBinding ColumnName="LineTotal" Name="LineTotal" />
  </InsertFunction>

    <UpdateFunction
    FunctionName="AdventureWorksModel.Store.UpdateSalesOrderDetail" >
        <ScalarProperty Name="OrderQty" ParameterName="OrderQty"
             Version="Current"/>
        <ScalarProperty Name="SalesOrderDetailID"
           ParameterName="SalesOrderDetailID" Version="Current"/>
        <AssociationEnd
    AssociationSet="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
       From="SalesOrderDetail" To="SalesOrderHeader">
          <ScalarProperty Name="SalesOrderID"
            ParameterName="SalesOrderID" Version="Current" />
        </AssociationEnd>
    </UpdateFunction>

    <DeleteFunction
     FunctionName="AdventureWorksModel.Store.DeleteSalesOrderDetail" >
        <ScalarProperty Name="SalesOrderDetailID"
           ParameterName="SalesOrderDetailID" Version="Original"/>
        <AssociationEnd
           AssociationSet="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
          From="SalesOrderDetail" To="SalesOrderHeader">
        <ScalarProperty Name="SalesOrderID"
                  ParameterName="SalesOrderID" />
        </AssociationEnd>
    </DeleteFunction>
  </ModificationFunctionMapping>

See Also

Tasks

How to: Define a Model with a Stored Procedure

Other Resources

Defining Advanced Data Models
CSDL, SSDL, and MSL Specifications