Asignar ComplexType a procedimientos almacenados (Entity Framework)

El Entity Data Model (EDM) admite el uso de procedimientos almacenados para la modificación de datos de las propiedades de ComplexType. El ejemplo proporcionado en este tema agrega compatibilidad con los procedimientos almacenados al modelo de datos definido en el tema Cómo definir un modelo con tipo complejo.

Modelo de almacenamiento

Los procedimientos almacenados utilizados en este ejemplo se especifican en el lenguaje de definición de esquemas de almacenamiento (SSDL). El elemento Function identifica los procedimientos almacenados accesibles en la base de datos. Los procedimientos almacenados se especifican para tres funciones de modificación que se usan para crear, actualizar y eliminar instancias de 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>

La base de datos utilizada en este ejemplo se puede crear ejecutando el script siguiente en 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

Modelo conceptual

El CAddressComplexType y el CCustomerEntityType que lo utiliza como una propiedad se definen en el lenguaje de definición de esquemas conceptuales (CSDL). Utilice el esquema siguiente con edmgen.exe para generar el modelo de objetos para obtener este ejemplo.

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

Especificación de la asignación

El ModificationFunctionElement que asigna los procedimientos almacenados para el CAddressComplexType en el modelo conceptual se define en el archivo del lenguaje de especificación de asignaciones (MSL). El esquema siguiente muestra la asignación para las funciones de creación, actualización y eliminación identificadas en el modelo de almacenamiento.

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

Para ejecutar el código de la aplicación que utiliza los procedimientos almacenados definidos y asignados en este ejemplo, utilice el código que se proporciona en el tema Cómo agregar y modificar objetos con tipos complejos (Entity Framework). A los procedimientos almacenados asignados con ModificationFunctionElement se les llama implícitamente al utilizar el modelo de datos implementado en este tema. No se requiere ninguna modificación en el código de la aplicación.

Vea también

Tareas

Cómo definir un modelo con un tipo complejo (Entity Framework)

Conceptos

Compatibilidad con los procedimientos almacenados (Entity Framework)