HOW TO：使用修改預存程序定義模型 (Entity Framework)

發行項
08/21/2008

Entity Framework 可讓您指定在修改實體資料時要使用的預存程序 (Stored Procedure)。這些預存程序會取代 Entity Framework 所產生的方法。預存程序是以隱含方式呼叫的，因此不必對概念結構描述或現有應用程式程式碼中定義的資料模型進行變更。如需詳細資訊，請參閱預存程序支援 (Entity Framework)。

本主題說明如何針對 Adventure Works Sales Model 中的 SalesOrderDetail 實體類型，定義修改預存程序。若要使用此範例，您必須已經將 AdventureWorks Sales Model 加入到專案中，並設定您的專案使用 Entity Framework。若要這樣做，請完成 HOW TO：手動設定 Entity Framework 專案和 HOW TO：以手動方式定義 Entity Data Model (Entity Framework) 中的程序。

若要定義 SalesOrderDetail 實體類型的修改預存程序

在 AdventureWorks 資料庫中，執行下列 Transact-SQL 指令碼建立修改預存程序：

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 Sales 儲存模型 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 Sales 儲存模型的 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>

HOW TO：使用修改預存程序定義模型 (Entity Framework)

若要定義 SalesOrderDetail 實體類型的修改預存程序

另請參閱

概念

其他資源

其他資源