다음을 통해 공유

복합 형식을 저장 프로시저에 매핑(Entity Framework)

  • 아티클

EDM(엔터티 데이터 모델)은 ComplexType의 속성에 대한 데이터 수정에 저장 프로시저 사용을 지원합니다. 이 항목에 제공된 예제에서는 방법: 복합 형식으로 모델 정의 항목에 정의된 데이터 모델에 저장 프로시저 지원을 추가합니다.

저장소 모델

이 예제에서 사용된 저장 프로시저는 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>

이 예제에서 정의 및 매핑된 저장 프로시저를 사용하는 응용 프로그램 코드를 실행하려면 방법: 복합 형식으로 개체 추가 및 수정(Entity Framework) 항목에 제공된 코드를 사용합니다. ModificationFunctionElement를 사용하여 매핑된 저장 프로시저는 이 항목에 구현된 데이터 모델을 사용할 때 암시적으로 호출됩니다. 응용 프로그램 코드를 수정할 필요는 없습니다.

참고 항목

작업

방법: 복합 형식으로 모델 정의(Entity Framework)

개념

저장 프로시저 지원(Entity Framework)