共用方式為

將複雜類型對應至預存程序 (Entity Framework)

  • 發行項

Entity Data Model (EDM) 支援使用預存程序來修改 ComplexType 的屬性資料。本主題提供的範例會在主題 HOW TO：定義具有複雜類型的模型所定義的資料模型中，加入預存程序支援。

儲存體模型

這個範例中使用的預存程序，是在存放結構定義語言 (SSDL) 中指定的。Function 項目會識別資料庫中可存取的預存程序。指定的預存程序是針對用於建立、更新和刪除 CAddressComplexType 之執行個體的三個修改函式。

<?xml version="1.0" encoding="utf-8"?>
    <Schema Namespace="CustomerComplexAddress.Store"
        Alias="Self" Provider="System.Data.SqlClient"
        ProviderManifestToken="2005"
        xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">

        <EntityContainer Name="dbo">
          <EntitySet Name="SCustomer"
             EntityType="CustomerComplexAddress.Store.SCustomer" />
        </EntityContainer>

        <EntityType Name="SCustomer">
          <Key>
            <PropertyRef Name="CustomerId" />
          </Key>
          <Property Name="CustomerId" Type="int" Nullable="false" />
          <Property Name="CompanyName" Type="nvarchar" MaxLength="50"/>
          <Property Name="ContactName" Type="nvarchar" MaxLength="50"/>
          <Property Name="ContactTitle" Type="nvarchar" MaxLength="50"/>
          <Property Name="Address" Type="nvarchar" MaxLength="50" />
          <Property Name="City" Type="nvarchar" MaxLength="50" />
          <Property Name="Region" Type="nvarchar" MaxLength="50" />
          <Property Name="PostalCode" Type="nvarchar" MaxLength="50" />
          <Property Name="Country" Type="nvarchar" MaxLength="50" />
          <Property Name="Phone" Type="nvarchar" MaxLength="50" />
          <Property Name="Fax" Type="nvarchar" MaxLength="50" />
        </EntityType>
        <Function Name="CreateCustomerComplexAddress" 
                   Aggregate="false" BuiltIn="false" 
                   NiladicFunction="false" IsComposable="false"
                   ParameterTypeSemantics="AllowImplicitConversion"
                   Schema="dbo">
          <Parameter Name="CustomerId" Type="int" Mode="In" />
          <Parameter Name="CompanyName" Type="nvarchar" Mode="In" />
          <Parameter Name="ContactName" Type="nvarchar" Mode="In" />
          <Parameter Name="ContactTitle" Type="nvarchar" Mode="In" />
          <Parameter Name="Address" Type="nvarchar" Mode="In" />
          <Parameter Name="City" Type="nvarchar" Mode="In" />
          <Parameter Name="Region" Type="nvarchar" Mode="In" />
          <Parameter Name="PostalCode" Type="nvarchar" Mode="In" />
          <Parameter Name="Country" Type="nvarchar" Mode="In" />
          <Parameter Name="Phone" Type="nvarchar" Mode="In" />
          <Parameter Name="Fax" Type="nvarchar" Mode="In" />
        </Function>
        <Function Name="DeleteCustomerComplexAddress" 
                   Aggregate="false" BuiltIn="false"
                   NiladicFunction="false" IsComposable="false"
                   ParameterTypeSemantics="AllowImplicitConversion"
                   Schema="dbo">
          <Parameter Name="CustomerId" Type="int" Mode="In" />
        </Function>
        <Function Name="UpdateCustomerComplexAddress" 
                   Aggregate="false" BuiltIn="false"
                   NiladicFunction="false" IsComposable="false"
                   ParameterTypeSemantics="AllowImplicitConversion"
                   Schema="dbo">
          <Parameter Name="CustomerId" Type="int" Mode="In" />
          <Parameter Name="Address" Type="nvarchar" Mode="In" />
          <Parameter Name="City" Type="nvarchar" Mode="In" />
          <Parameter Name="Region" Type="nvarchar" Mode="In" />
          <Parameter Name="PostalCode" Type="nvarchar" Mode="In" />
          <Parameter Name="Country" Type="nvarchar" Mode="In" />
          <Parameter Name="Phone" Type="nvarchar" Mode="In" />
          <Parameter Name="Fax" Type="nvarchar" Mode="In" />
        </Function>
    </Schema>

藉由在 SQL Server Management Studio 中執行下列指令碼，可以建立這個範例中使用的資料庫。

USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'CustomerComplexAddress')
DROP DATABASE [CustomerComplexAddress]

CREATE DATABASE [CustomerComplexAddress] 
GO

USE [CustomerComplexAddress]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[SCustomer]') 
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SCustomer](
    [CustomerId] [int] NOT NULL,
     [CompanyName] [nvarchar](50) NULL,
     [ContactName] [nvarchar](50) NULL,
     [ContactTitle] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
     [City] [nvarchar](50) NULL,
     [Region] [nvarchar](50) NULL,
     [PostalCode] [nvarchar](50) NULL,
     [Country] [nvarchar](50) NULL,
     [Phone] [nvarchar](50) NULL,
     [Fax] [nvarchar](50) NULL,
 CONSTRAINT [PK_SCustomer] PRIMARY KEY CLUSTERED 
 (
    [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
   ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[CreateCustomerComplexAddress]') 
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[CreateCustomerComplexAddress]
   @CustomerId int,
   @CompanyName nvarchar(50),
   @ContactName nvarchar(50),
   @ContactTitle nvarchar(50),
   @Address nvarchar(50),
   @City nvarchar(50),
   @Region nvarchar(50),
   @PostalCode nvarchar(50),
   @Country nvarchar(50),
   @Phone nvarchar(50),
   @Fax nvarchar(50)
   
AS

INSERT INTO [dbo].[SCustomer]
           ([CustomerId]
           ,[CompanyName]
           ,[ContactName]
           ,[Address]
           ,[City]
           ,[Region]
           ,[PostalCode]
           ,[Phone]
           ,[Fax])
     VALUES
           (@CustomerId,
           @CompanyName,
           @ContactName,
           @Address,
           @City,
           @Region,
           @PostalCode,
           @Phone,
           @Fax)
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[UpdateCustomerComplexAddress]') 
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdateCustomerComplexAddress]
   @CustomerId int,
   @Address nvarchar(50),
   @City nvarchar(50),
   @Region nvarchar(50),
   @PostalCode nvarchar(50),
   @Country nvarchar(50),
   @Phone nvarchar(50),
   @Fax nvarchar(50)
AS
UPDATE [dbo].[SCustomer]
   SET [Address] = @Address,
   [City] = @City,
   [Region] = @Region,
   [PostalCode] = @PostalCode,
   [Country] = @Country,
   [Phone] = @Phone,
   [Fax] = @Fax
 WHERE CustomerId = @CustomerId' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[DeleteCustomerComplexAddress]') 
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[DeleteCustomerComplexAddress] 
   @CustomerId int
AS
UPDATE [dbo].[SCustomer]
   SET [Address] = Null,
   [City] = Null,
   [Region] = Null,
   [PostalCode] = Null,
   [Country] = Null,
   [Phone] = Null,
   [Fax] = Null
 WHERE CustomerId = @CustomerId

' 
END

概念模型

CAddressComplexType 和使用其做為屬性的 CCustomerEntityType，是在概念結構定義語言 (CSDL) 中定義的。使用下列結構描述搭配 edmgen.exe，即可產生這個範例的物件模型。

<?xml version="1.0" encoding="utf-8"?>
    <Schema Namespace="CustomerComplexAddress"
        Alias="Self"
        xmlns="https://schemas.microsoft.com/ado/2006/04/edm">

        <EntityContainer Name="CustomerComplexAddressContext">
          <EntitySet Name="CCustomers"
               EntityType="CustomerComplexAddress.CCustomer" />
        </EntityContainer>

        <EntityType Name="CCustomer">
          <Key>
            <PropertyRef Name="CustomerId" />
          </Key>
          <Property Name="CustomerId" Type="Int32" Nullable="false" />
          <Property Name="CompanyName" Type="String" />
          <Property Name="ContactName" Type="String" />
          <Property Name="ContactTitle" Type="String" />
          <Property Name="Address" Type="Self.CAddress"
                              Nullable="false" />
        </EntityType>

        <ComplexType Name="CAddress">
          <Property Name="StreetAddress" Type="String" />
          <Property Name="City" Type="String" />
          <Property Name="Region" Type="String" />
          <Property Name="PostalCode" Type="String" />
          <Property Name="Country" Type="String" />
          <Property Name="Phone" Type="String" />
          <Property Name="Fax" Type="String" />
        </ComplexType>
    </Schema>

對應規格

用於將預存程序對應到概念模型中 CAddressComplexType 的 ModificationFunctionElement，是在對應規格語言 (MSL) 中定義的。下列結構描述顯示的對應用於建立、更新和刪除儲存體模型中識別的函式。

<?xml version="1.0" encoding="utf-8"?>
    <Mapping Space="C-S"
        xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">

        <EntityContainerMapping StorageEntityContainer="dbo"
                  CdmEntityContainer="CustomerComplexAddressContext">

          <EntitySetMapping Name="CCustomers">
            <EntityTypeMapping 
              TypeName="CustomerComplexAddress.CCustomer">
              <MappingFragment StoreEntitySet="SCustomer">
                <ScalarProperty Name="CustomerId" 
                                 ColumnName="CustomerId" />
                <ScalarProperty Name="CompanyName" 
                                 ColumnName="CompanyName" />
                <ScalarProperty Name="ContactName" 
                                 ColumnName="ContactName" />
                <ScalarProperty Name="ContactTitle" 
                                 ColumnName="ContactTitle" />
               <ComplexProperty Name="Address"
                       TypeName="CustomerComplexAddress.CAddress">
                 <ScalarProperty Name="StreetAddress" 
                                 ColumnName="Address" />
                 <ScalarProperty Name="City" 
                                 ColumnName="City" />
                 <ScalarProperty Name="Region" 
                                 ColumnName="Region" />
                 <ScalarProperty Name="PostalCode" 
                                 ColumnName="PostalCode" />
                 <ScalarProperty Name="Country" 
                                 ColumnName="Country" />
                 <ScalarProperty Name="Phone" 
                                 ColumnName="Phone" />
                 <ScalarProperty Name="Fax" 
                                 ColumnName="Fax" />
                </ComplexProperty>
              </MappingFragment>
                <ModificationFunctionMapping >
                  <InsertFunction
FunctionName="CustomerComplexAddress.Store.CreateCustomerComplexAddress">
                    <ScalarProperty Name="CustomerId"
                          ParameterName="CustomerId" Version="Current"/>
                    <ScalarProperty Name="CompanyName" 
                                    ParameterName="CompanyName"
                          Version="Current"/>
                    <ScalarProperty Name="ContactName" 
                                    ParameterName="ContactName" 
                                    Version="Current"/>
                     <ScalarProperty Name="ContactTitle"
                          ParameterName="ContactTitle" 
                                     Version="Current"/>
                    <ComplexProperty 
                      TypeName="CustomerComplexAddress.CAddress" 
                      Name="Address">
                    <ScalarProperty Name="StreetAddress" 
                                    ParameterName="Address" 
                                    Version="Current"/>
                    <ScalarProperty Name="City"
                                    ParameterName="City" 
                                    Version="Current"/>
                    <ScalarProperty Name="Region" 
                                    ParameterName="Region" 
                                    Version="Current"/>
                    <ScalarProperty Name="PostalCode"
                          ParameterName="PostalCode" 
                                    Version="Current"/>
                    <ScalarProperty Name="Country"
                                    ParameterName="Country" 
                                    Version="Current"/>
                    <ScalarProperty Name="Phone"
                                    ParameterName="Phone" 
                                    Version="Current"/>
                    <ScalarProperty Name="Fax"
                                    ParameterName="Fax" 
                                    Version="Current"/>
                    </ComplexProperty>
              </InsertFunction>

                <UpdateFunction
FunctionName="CustomerComplexAddress.Store.UpdateCustomerComplexAddress">
                  <ScalarProperty Name="CustomerId"
                                  ParameterName="CustomerId" 
                                  Version="Current"/>
                  <ComplexProperty 
                    TypeName="CustomerComplexAddress.CAddress" 
                    Name="Address">
                  <ScalarProperty Name="StreetAddress"
                        ParameterName="Address" Version="Current"/>
                  <ScalarProperty Name="City"
                        ParameterName="City" Version="Current"/>
                  <ScalarProperty Name="Region" 
                        ParameterName="Region"
                        Version="Current"/>
                  <ScalarProperty Name="PostalCode"
                        ParameterName="PostalCode" Version="Current"/>
                  <ScalarProperty Name="Country"
                        ParameterName="Country" Version="Current"/>
                  <ScalarProperty Name="Phone"
                        ParameterName="Phone" Version="Current"/>
                  <ScalarProperty Name="Fax"
                        ParameterName="Fax" Version="Current"/>
                  </ComplexProperty>
                </UpdateFunction>

                <DeleteFunction
FunctionName="CustomerComplexAddress.Store.DeleteCustomerComplexAddress" >
                  <ScalarProperty Name="CustomerId"
                      ParameterName="CustomerId" Version="Original"/>
                </DeleteFunction>
              </ModificationFunctionMapping>
            </EntityTypeMapping>
          </EntitySetMapping>
       </EntityContainerMapping>
    </Mapping>

若要執行使用這個範例所定義和所對應預存程序的應用程式程式碼，請使用主題 HOW TO：加入及修改具有複雜類型的物件 (Entity Framework) 中提供的程式碼。藉由使用 ModificationFunctionElement 對應的預存程序，是在使用本主題中實作的資料模型時隱含呼叫的。您不需要對應用程式程式碼進行任何修改。

另請參閱

工作

HOW TO：定義具有複雜類型的模型 (Entity Framework)

概念

預存程序支援 (Entity Framework)