如何：使用修改存储过程定义模型（实体框架）

2008-08-22

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

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

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

执行下面的 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

将下面的函数定义添加到 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>

在 EntitySetMapping 中的 SalesOrderDetail 的 EntityTypeMapping 元素内插入以下 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>

通过

如何：使用修改存储过程定义模型（实体框架）

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

另请参见

概念

其他资源

其他资源