How to: Define a Model with Modification Stored Procedures

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