Resumé

SQL2005 OPENXML e Array Custom

Cristian Merighi () 0,00

Utilizzare i dati tipo XML e le istruzioni T-SQL disponibili da SQL 2005 per gestire, in un unica istruzione, l'inserimento di un'intera lista di elementi in database.
Questo articolo è da considerarsi obsoleto. Alcune funzionalità potrebbero non essere più disponibili e non è possibile aggiungere commenti.

In un'applicazione enterprise ad oggetti, è necessario prendersi carico delle implicazioni derivate dall'utilizzo di custom entities e delle relazioni gerarchiche che le governano. Esse riproporranno, all'incirca, quelle stesse relazioni che si è ritenuto opportuno definire nel database relazionale utilizzato per lo storage.
I tools ORM possono intervenire nelle fasi di aggiornamento dati e comunicazione tra stato Business Logic e database, generando scripts SQL ai quali il più delle volte ci si affida senza indagini supplementari.

Questo è, personalmente, un approccio che tendo ad evitare: preferisco scrivere personalmente le instruzioni SQL (tramite stored procedures ad hoc) nel tentativo di sfruttare a pieno le appetitose potenzialità che una data tecnologia database ed il suo dialetto SQL possono offrire.

Al momento sto disegnando, come pretesto per approcciare nuovi aspetti delle tecnologie .Net e per affinare la mia conoscenza sulle tematiche di accesso ai dati, un engine per la gestione di Blog.
Con questo articolo vorrei condividere il metodo che ho utilizzato per inserire - in una sola mossa con l'istruzione INSERT INTO... SELECT - un'intera collection di business entities.
La chiave è lo statement T-SQL OPENXML di SQL 2005 associato ad una preventiva serializzazione XML.
L'idea è quella di serializzare in un frammento xml una lista generica di elementi e di passarli al database SQL 2005, il quale si preoccuperà del parsing e della conseguente trasformazione dell'xml in un oggetto tabella (TABLE). Il passaggio all'istruzione INSERT INTO... SELECT viene da sé.
In questo caso specifico vado ad inviare al database una nuova istanza di Blog (che consiste semplicemente nel nome univoco ed una serie di dati riguardanti l'autore) più elementi accessori con dati in lingua per il blog stesso (List<BlogLocalizedData>).

database extract

Di seguito un estratto della mia classe BlogLocalizedData utile per intelleggere il significato delle variabili all'interno dello script SQL:

namespace Pacem.Providers.Blog.Logic

{

    [XmlRoot("LocalizedData")]

    public partial class BlogLocalizedData : ILocalizedData, IProvided<Pacem.Providers.Blog.BlogProvider>

    {

         

         // [...] Omitted code

 

        /// <summary>

        /// Gets or sets the title in the relevant culture.

        /// </summary>

        [XmlElement("Title")]

        public string Title

        {

            get

            {

                return _Title;

            }

            set

            {

                _Title = value;

            }

        }

 

        /// <summary>

        /// Gets or sets the description in the relevant culture.

        /// </summary>

        [XmlElement("Description")]

        public string Description

        {

            get

            {

                return _Description;

            }

            set

            {

                _Description = value;

            }

        }

 

        /// <summary>

        /// Gets or sets the culture of the data.

        /// </summary>

        [XmlElement("Culture")]

        public string Culture

        {

            get

            {

                return _Culture;

            }

            set

            {

                // check if it is a valid culture:

                // throws an exception if value isn't a valid culture.

                CultureInfo culture = new CultureInfo(value);

                _Culture = culture.Name;

            }

        }

 

         // [...] Omitted code

    }

}

Prego di porre attenzione ai custom attributes che marchiano le proprietà pubbliche come XmlElements.
Segue lo script della stored procedure. Come è possibile notare (2a delle righe evidenziate) e come potete carpire dalla documentazione ufficiale, ho avvisato il parser XML di SQL di considerare il contenuto del blocco xml in una prospettiva elemento-centrica. Ciò significa che i dati vanno pescati all'interno degli omonimi (vedi clausola WITH) nodi xml!
Ci sono anche modalità ibride - che cioè tengono conto sia del contenuto dei nodi sia del contenuto degli attributi - per la corretta lettura dei dati. Fate pure riferimento alla documentazione ufficiale.

-- =============================================

-- Author:        Cristian Merighi

-- =============================================

CREATE PROCEDURE [sproc_Pacem_Blog_Insert]

    (   

        @Key varchar(64),

        @AuthorFirstName nvarchar(32),

        @AuthorLastName nvarchar(32),

        @AuthorEmail varchar(128),   

        @LocalizedDataCollection xml

        /*

        -- sample xml:

        <LocalizedDataCollection>

            <LocalizedData>

                <Title>title</Title>

                <Description>description</Description>

                <Culture>en-US</Culture>

            </LocalizedData>

            <LocalizedData>

                <Title>titolo</Title>

                <Description>descrizione</Description>

                <Culture>it-IT</Culture>

            </LocalizedData>

            <LocalizedData>

                <Title>titre</Title>

                <Description>description</Description>

                <Culture>fr-FR</Culture>

            </LocalizedData>

        </LocalizedDataCollection>

        */

        , @NewID int output

    )

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

 

    BEGIN TRANSACTION

 

    BEGIN TRY

 

        -- inserting blog

        INSERT INTO [Pacem_Blogs]

           ([strBlogKey]

           ,[strAuthorFirstName]

           ,[strAuthorLastName]

           ,[strAuthorEmail])

        VALUES

           (@Key

           ,@AuthorFirstName

           ,@AuthorLastName

           ,@AuthorEmail)

 

        SELECT @NewID = SCOPE_IDENTITY()

 

        -- Insert statements for procedure here

        DECLARE @XmlHandle int

        EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @LocalizedDataCollection

 

        INSERT INTO [Pacem_BlogsLocalized]

           ([IDBlog]

           ,[strCulture]

           ,[strTitle]

           ,[strDescription])

        SELECT @NewID, [Culture], [Title], [Description]

        FROM OPENXML (@XmlHandle, '//LocalizedData',    2 /* element centric */)

            WITH ([Culture]        varchar(8),

                  [Title]        nvarchar(128),

                  [Description]    nvarchar(512))

 

        COMMIT TRANSACTION

 

    END TRY

    BEGIN CATCH

        ROLLBACK TRANSACTION

        -- RAISE ERROR

        DECLARE @ErrorMessage NVARCHAR(4000);

        DECLARE @ErrorSeverity INT;

        DECLARE @ErrorState INT;

 

        SELECT

            @ErrorMessage = ERROR_MESSAGE(),

            @ErrorSeverity = ERROR_SEVERITY(),

            @ErrorState = ERROR_STATE();

 

        -- Use RAISERROR inside the CATCH block to return error

        -- information about the original error that caused

        -- execution to jump to the CATCH block.

        RAISERROR (@ErrorMessage, -- Message text.

                   @ErrorSeverity, -- Severity.

                   @ErrorState -- State.

                   );

 

    END CATCH

END

Ed infine, per mettere insieme tutti i pezzi di questo semplice ma potente puzzle, le linee di codice che permettono la serializzazione della mia collection di BlogLocalizedData nella forma in cui verrà passata alla stored procedure come parametro @LocalizedDataCollection:

// serializing localizedData

// localizedData is an instance of List<BlogLocalizedData>

XmlSerializer serializer = new XmlSerializer(

    typeof(List<BlogLocalizedData>),

    new XmlRootAttribute("LocalizedDataCollection"));

 

StringBuilder sb = new StringBuilder();

using (StringWriter tw = new StringWriter(sb))

{

    XmlTextWriter xtw = new XmlTextWriter(tw);

    serializer.Serialize(xtw, localizedData);

}

string xmlFragment = sb.ToString();

Take care. Bye.

feedback
 

Syndicate

Autore

Cristian Merighi facebook twitter google+ youtube

Ultimi articoli

Top rated

Archivio

Dove sono?

Autore

Cristian Merighi facebook twitter google+ youtube

Le mie letture

Feeds