Home > SQLCLR instead of OLE Automation (sp_OA* method)

SQLCLR instead of OLE Automation (sp_OA* method)

Riprendo il tema relativo all’aggiornamento dei dati esposti su una tabella pivot di un foglio di lavoro Microsoft Excel.

Abbiamo analizzato una possibile soluzione basata sull’utilizzo di OLE Automation (sp_OA*) con questo post Aggiornare una tabella pivot di Microsoft Excel. Vediamo ora com’è possibile raggiungere lo stesso risultato utilizzando però il linguaggio CLR.

Considerazioni sull’utilizzo del linguaggio CLR (common language runtime)

Talvolta chi sviluppa applicazioni database ha la necessità di estendere le funzionalità del linguaggio T-SQL per interagire meglio con il sistema operativo e con l’ambiente. Con le versioni di SQL Server che hanno preceduto SQL Server 2005 si poteva arricchire il T-SQL utilizzando le stored procedure estese e le classi di oggetti COM (Component Object Model). Era però necessario prestare molta attenzione nell’utilizzo delle stored procedure estese in quanto un malfunzionamento di queste ultime poteva compromettere l’engine di SQL Server con il rischio di arrestare il servizio principale (SQL Server service).

SQL Server 2005 introduce un’importante caratteristica: CLR Integration che rappresenta la capacità di poter sfruttare la potenza delle librerie del Framework .NET aumentando il livello di protezione, la sicurezza e in alcune situazioni anche le prestazioni rispetto al codice equivalente T-SQL.

Utilizzando un linguaggio .NET come ad esempio Microsoft Visual C# è quindi possibile implementare oggetti di programmazione per un database SQL Server.

Gli oggetti CLR non hanno dipendenze esterne, l’assembly completo di un oggetto CLR risiede (memorizzato) all’interno del database, diversamente da quanto avviene per gli oggetti COM e per le stored procedure estese che hanno relazioni con librerie esterne. E’ proprio per l’assenza di relazioni esterne, che gli oggetti CLR vengono eseguiti nello stesso processo del database engine, questo assicura prestazioni e livelli di sicurezza migliori rispetto agli oggetti COM che verrebbero eseguiti in un processo separato. L’engine di SQL Server potrà quindi gestire meglio i propri oggetti CLR evitando che un malfunzionamento possa compromettere il servizio principale di SQL Server stesso.

Per ragioni di sicurezza, sull’istanza SQL Server, il CLR è disabilitato by default. Per abilitare il supporto a SQLCLR è necessario utilizzare l’opzione clr enabled con la stored procedure di sistema sp_configure. E’ inoltre possibile abilitare il supporto CLR utilizzando SQL Server Area Configuration (SAC).

Creazione di una stored procedure CLR per aggiornare i dati esposti su una tabella pivot di Excel

Per prima cosa è necessario abilitare sull’istanza SQL Server (nel caso non è già stato fatto) il supporto a SQLCLR:

— Abilitazione di SQLCLR sull’istanza
sp_configure ‘clr enabled’, 1
go

reconfigure
go

Abilitiamo ora l’opzione trustworthy che consente al database di poter accedere a risorse esterne considerandole attendibili:

use [AdventureWorks] go

— Abilito il database per l’accesso a risorse esterne
alter database [AdventureWorks]   set trustworthy on
go

Il linguaggio .NET che utilizzeremo per implementare la stored procedure CLR sarà Microsoft Visual C#. Da questo ambiente, per poter accedere ad una risorsa esterna del pacchetto Office (come ad esempio un foglio di lavoro Microsoft Excel) è necessario installare il package che contiene gli assembly di interoperabilità primari (PIA, Primary Interop Assembly) di Microsoft Office disponibile per il download a partire da questo link: http://support.microsoft.com/kb/897646/it

Dopo l’installazione del pacchetto sarà possibile creare, nell’ordine, i seguenti assembly:

use [AdventureWorks] go

— Creazione assembly
create assembly stdole
  from ‘C:\Programmi\File comuni\Microsoft Shared\MSEnv\PublicAssemblies\stdole.dll’
  with permission_set = unsafe — external_access
go

create assembly Office
  from ‘C:\Documents and Settings\Administrator\Documenti\Visual Studio 2005\Projects\SQLCLR\CLR_Excel_Interf\obj\Office.dll’
  with permission_set = unsafe — external_access
go

create assembly Microsoft_Vbe_Interop
  from ‘C:\WINDOWS\assembly\GAC\Microsoft.Vbe.Interop\11.0.0.0__71e9bce111e9429c\Microsoft.Vbe.Interop.dll’
  with permission_set = unsafe — external_access
go

create assembly Excel
  from ‘C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Excel\11.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll’
  with permission_set = unsafe — external_access
go

Procediamo con l’implementazione della stored procedure CLR up_excel_pivot_refreshtable utilizzando Microsoft Visual C#, creando una nuova soluzione, un nuovo progetto in cui aggiungere un elemento di tipo stored procedure come illustrato nelle figure seguenti:

Microsoft Visual C# - Nuovo Progetto DataBase

Figura 1 – Microsoft Visual C# (Nuovo Progetto DataBase)

Microsoft Visual C# - Nuovo Elemento Stored Procedure CLR

Figura 1 – Microsoft Visual C# (Nuovo Elemento Stored Procedure CLR)

Utilizziamo il seguente codice per implementare la stored procedure up_excel_pivot_refreshtable:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Excel = Microsoft.Office.Interop.Excel;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]     public static void up_excel_pivot_refreshtable
        (string FilePath,
         string Password,
         string WriteResPassword,
         Int32 Debug)
    {
        /*
         * Descrizione:
         *        
         * Refresh automatico di una tabella pivot
         * posizionata su un foglio di lavoro di un
         * file Microsoft Excel.
         *
         * Parametri:
         *
         * – @FileName:
         * Path e nome del file excel (es. e:\temp\refresh.xls)
         *
         * – @Password:
         *     Specifica una stringa contenente la password richiesta
         *     per l’apertura di una cartella di lavoro protetta
         *
         * – @WriteResPassword:
         *     Specifica una stringa contenente la password richiesta
         *     per la scrittura di una cartella di lavoro a scrittura riservata
         *
         * – @Debug:
         *     Modalità di debug
         *     Può assumere i valori: 1= Debug ON, 0= Debug OFF
         *
         *
         * Link utili:
         *
         * http://msdn.microsoft.com/en-us/library/aa159923(office.11).aspx
         *
         * SQL-CLR Type Mapping (LINQ to SQL):
         *   http://msdn.microsoft.com/en-us/library/bb386947.aspx
         *
        */

        // Creazione dell’oggetto DebugRecord
        SqlDataRecord DebugRecord = new SqlDataRecord(new SqlMetaData("Message", SqlDbType.VarChar, 256));
        if (Debug == 1)
        {
            DebugRecord.SetSqlString(0, "Start debug");
        }

        // Controllo parametri di input
        if (FilePath == "")
        {
            if (Debug == 1)
            {
                DebugRecord.SetSqlString(0, "FilePath Parameter = " + FilePath);
                SqlContext.Pipe.Send(DebugRecord);
            }
            return;
        }
        else
        {
            Excel.Application thisExcel = new Excel.Application();
            try
            {
                Excel.Workbook theWorkbook = thisExcel.Workbooks._Open
                  (FilePath,
                   0,
                   false,
                   5,
                   Password,
                   WriteResPassword,
                   false,
                   System.Reflection.Missing.Value,
                   System.Reflection.Missing.Value,
                   true,
                   false,
                   System.Reflection.Missing.Value,
                   false);

                if (Debug == 1)
                {
                    DebugRecord.SetSqlString(0, "Workbooks.Open completed successfully");
                }

                theWorkbook.RefreshAll();
                if (Debug == 1)
                {
                    DebugRecord.SetSqlString(0, "Workbook.RefreshAll() completed successfully");
                }

                theWorkbook.Save();
                if (Debug == 1)
                {
                    DebugRecord.SetSqlString(0, "Workbook Saved successfully");
                }

                theWorkbook.Close(false,
                                  System.Reflection.Missing.Value,
                                  System.Reflection.Missing.Value);
                if (Debug == 1)
                {
                    DebugRecord.SetSqlString(0, "Execution completed successfully");
                }
            }
            finally
            {
                thisExcel.Quit();
                SqlContext.Pipe.Send(DebugRecord);
            }
        }
    }
};

Dopo l’implementazione della stored procedure sarà possibile farne il deploy direttamente da Visual Studio oppure manualmente utilizzando i seguenti comandi:

use [AdventureWorks] go

— Creazione assembly
create assembly CLR_Excel_Interf
  from ‘C:\Documents and Settings\Administrator\Documenti\Visual Studio 2005\Projects\SQLCLR\CLR_Excel_Interf\bin\Debug\CLR_Excel_Interf.dll’
  with permission_set = unsafe — external_access
go

— Creazione procedura .NET
create procedure dbo.up_excel_pivot_refreshtable
(
 @FilePath [nvarchar](max),
 @Password [nvarchar](max),
 @WriteResPassword [nvarchar](max)
)
as
  — EXTERNAL NAME [nome_assembly].[nome_classe].[nome_metodo]   external name [CLR_Excel_Interf].[StoredProcedures].[up_excel_pivot_refreshtable] go

Esplorando il ramo Programmabilità/Assembly troveremo i seguenti oggetti:

Assembly

Esempio:

Per aggiornare i dati esposti dalle tabelle pivot presenti nel file Excel C:\Temp\Refresh.xls sarà sufficiente eseguire:

— exec StoredProcedureName
exec dbo.up_excel_pivot_refreshtable
  @FilePath = ‘C:\temp\refresh.xls’,
  @Password = ”,
  @WriteResPassword = ”,
  @Debug = 1

Risultato:

Message
———————————-
Execution completed successfully

(Righe interessate: 1)

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Azure Key Vault e certificati code-signing: Strategie per la conformità post 1° Giugno 2023!

In questi giorni, ho avuto l’opportunità di esplorare il rinnovo di un certificato di code-signing …