如何:定义具有修改存储过程的模型(实体框架)

使用实体框架可以指定要在修改实体数据时使用的存储过程。 这些存储过程取代了实体框架所生成的方法。 存储过程是隐式调用的,因此不需要更改概念架构或现有应用程序代码中定义的数据模型。

Cc716731.note(zh-cn,VS.100).gif注意:
建议使用 ADO.NET 实体数据模型工具来指定用于修改实体数据的存储过程。有关更多信息,请参见Walkthrough: Mapping an Entity to Stored ProceduresHow to: Map Modification Functions to Stored Procedures

本主题演示如何为 Adventure Works 销售模型中的 SalesOrderDetail 实体类型定义修改存储过程。 若要使用此示例,必须已将 AdventureWorks 销售模型添加到您的项目中,并将项目配置为使用实体框架。 为此,请完成如何:手动配置实体框架项目如何:手动定义模型和映射文件(实体框架)中的过程。

Cc716731.note(zh-cn,VS.100).gif注意:
如果没有将实体类型的插入、更新和删除这三种操作全部映射到存储过程,则在运行时执行的情况下未映射的操作将失败且会引发 UpdateException

为 SalesOrderDetail 实体类型定义修改存储过程

  1. 执行下面的 Transact-SQL 脚本,在 AdventureWorks 数据库中创建修改存储过程:

    • CreateSalesOrderDetail 过程:

      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 过程:

      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 过程:

      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. 将以下函数定义添加到 AdventureWorks 销售存储模型的 .ssdl 架构文件的顶层 Schema 元素:

            <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. EntitySetMapping 中的 SalesOrderDetailEntityTypeMapping 元素内插入以下 XML 片段。 在 AdventureWorks 销售存储模型的 .msl 映射文件中执行此操作。

      <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> 
    

另请参见

任务

如何:使用存储过程定义模型(实体框架)

其他资源

定义高级数据模型(实体框架任务)
CSDL、SSDL 和 MSL 规范