Mapping di un tipo complesso alle stored procedure (Entity Framework)

Articolo
08/22/2008

Il modello EDM (Entity Data Model) supporta l'uso delle stored procedure per la modifica dei dati delle proprietà di ComplexType. Nell'esempio riportato in questo argomento viene aggiunto il supporto per le stored procedure al modello di dati definito nell'argomento Procedura: definire un modello con un tipo complesso.

Modello di archiviazione

Le stored procedure utilizzate in questo esempio sono specificate nel linguaggio SSDL (Store Schema Definition Language). L'elemento Function identifica le stored procedure accessibili nel database. Le stored procedure sono specificate per tre funzioni di modifica che consentono di creare, aggiornare ed eliminare istanze di 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>

Il database utilizzato in questo esempio può essere creato eseguendo lo script seguente in 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

Modello concettuale

L'oggetto CAddressComplexType e l'oggetto CCustomerEntityType che lo utilizza come proprietà sono definiti nel linguaggio CSDL (Conceptual Schema Definition Language). Per generare il modello a oggetti di questo esempio, utilizzare lo schema seguente con 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>

Specifica del mapping

L'oggetto ModificationFunctionElement che esegue il mapping delle stored procedure a CAddressComplexType nel modello concettuale è definito nel linguaggio MSL (Mapping Specification Language). Nello schema seguente è mostrato il mapping per le funzioni di creazione, aggiornamento ed eliminazione identificate nel modello di archiviazione.

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

Per eseguire il codice dell'applicazione che utilizza le stored procedure definite e mappate in questo esempio, utilizzare il codice contenuto nell'argomento Procedura: aggiungere e modificare oggetti con tipi complessi (Entity Framework). Le stored procedure mappate mediante ModificationFunctionElement vengono chiamate in modo implicito quando si utilizza il modello di dati implementato in questo argomento. Non è richiesta alcuna modifica al codice dell'applicazione.

Condividi tramite

Mapping di un tipo complesso alle stored procedure (Entity Framework)

Modello di archiviazione

Modello concettuale

Specifica del mapping

Vedere anche

Attività

Concetti

Risorse aggiuntive