Il wiki della programmazione: MSSQL - Installare un assembly

Ancora dubbi? Suggerimenti?

MSSQL - Installare un assembly

Installare un assembly in SQL server è un'operazione abbastanza semplice, ma in Internet è difficile trovare delle "dritte" su alcuni dettagli che possono far perdere un mucchio di tempo.

Compilare un assembly

Per prima cosa, un assembly non è un'applicazione che si interfaccia con un utente, quindi non dispone né di un oggetto Form né di un Console, ma è una libreria di classi.

Creata la classe nuova, bisogna definire un metodo statico che restituisce void, int32 o int.

namespace Example
{
	public class ExampleClass
	{
	    public static void DoSomething(Int32 Parametro)
	    {
	        // Do something
	    }
	}
}

Può essere molto comodo includere nella soluzione anche un secondo progetto che ha il solo scopo di eseguire il metodo statico, per permettere di farne il debug. Per poter fare riferimento alla classe, bisogna aggiungere un riferimento.

Connessione a SQL server

Dal momento che un assembly nasce per essere eseguito come una funzione di SQL server, anche la connessione al "proprio" database ha una sintassi particolare.
Invece di utilizzare la "solita" stringa di connessione, del tipo "SERVER=server\istanza;DATABASE=nomeDatabase;UID=sa;PWD=as", bisogna utilizzare questa particolare stringa di connessione: "context connection = true". Context connection=true significa "usa la connessione al database corrente, con le credenziali con cui stai eseguendo la query".
Può essere comodo mantenere entrambe le stringhe di connessione, da utilizzare di volta in volta per i test o per i rilasci in SQL server:

//            string ConnectionString = "SERVER=server\istanza;DATABASE=nomeDatabase;UID=sa;PWD=as";
	        string ConnectionString = "context connection = true";
	        SqlConnection sconn = new SqlConnection(ConnectionString);
	        sconn.Open();

Installazione dell'assembly

Per utilizzare un assembly occorre verificare che il SQL server sia abilitato ad eseguire codice CLR (Common Language Runtime).
Se il server non è abilitato, è possibile tentare di abilitarlo con queste istruzioni, da eseguire da una shell del Management Studio di SQL:

SP_CONFIGURE 'clr_enabled',1 -- abilita l'uso di librerie CLR
GO
RECONFIGURE

è possibile che queste istruzioni resitutiscano degli errori, ma la maggior parte sono già spiegati dettagliatamente in questo sito.
Una volta accertato che si può eseguire codice CLR, bisogna:
1) installare la DLL
2) creare una funzione "alias" per il metodo statico nella DLL

Installare la DLL

Una volta compilato il progetto, è possibile prelevare la DLL e copiarla nel server di produzione. Di seguito, è possibile installare la DLL andando in programmabilità -> assembly -> (tasto destro) -> nuovo assembly, oppure usare la query:

CREATE ASSEMBLY XMLRead 
FROM 'C:\percorso\progetto.dll'
WITH PERMISSION_SET = SAFE;

PERMISSION_SET è molto importante, perché un valore impostato a SAFE permette all'assembly di accedere solo a SQL server, EXTERNAL_ACCESS permette anche di accedere a file e registro di sistema; UNSAFE da l'accesso totale all'assembly, ed è il tipo di accesso più sconsigliato.

Creare la funzione alias per la DLL

L'istruzione di CREATE PROCEDURE è standard, ma invece della definizione della funzione è inserito un riferimento al metodo statico creato precedentemente.
Due annotazioni importanti, trascurate in molti siti web:

Il nome della procedura DEVE essere diverso da quello del metodo statico
Il nome della classe DEVE essere prefissata dal namespace, e quindi deve essere racchiuso tra parentesi quadre

CREATE PROCEDURE FunzioneEsempio @parametro int
AS EXTERNAL NAME Example.[Example.ExampleClass].DoSomething

Suggerimenti

Vorrei ampliare questo argomento, a mio avviso interessante. Per fare questo, vorrei conoscere l'opinione di chi è capitato qui e ha letto tutto :-).
Potete farlo tramite il bax di fianco.

Il wiki della programmazione - MSSQL - Installare un assembly