Sdílet prostřednictvím


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