Cosa si può veramente fare con una CLR Stored Procedure? Di per sé tutto quello che un Assembly può fare normalmente, la difficoltà è sapere come configurare l'assembly e come scrivere il metodo .NET...
Innanzi tutto bisogna sapere se il nostro assembly si deve accontentare di interagire con il database, se deve leggere/scrivere sul file system o deve interagire con l'ambiente Win32 (anche se si tratta di programmi .NET); tale necessità è dovuta dal fatto che nel momento in cui creo l'assembly in SQL Server ho la possibilità di impostare un PERMISSION_SET a tre valori, SAFE, EXTERNAL_ACCESS e UNSAFE.
Questi non sono altro che set di autorizzazioni che vengono attribuite all'assembly:
- SAFE (default) è il più restrittivo, infatti limita il campo d'azione dei metodi/procedure al solo database, escludendo l'accesso a risorse esterne
- EXTERNAL_ACCESS permette anche l'accesso a risorse esterne, quali il file system, il registry
- UNSAFE non ha restrizioni: posso eseguire programmi esterni (CLR o no), lanciare comandi, ecc.
Per poter utilizzare gli ultimi due bisogna modificare il database con il seguente comando
ALTER DATABASE MIO_DB SET TRUSTWORTHY ON
in modo tale che il codice .NET venga trustato.
Adesso vediamo cosa serve a un metodo per poter diventare una CLR Stored Procedure
-
Innanzi tutto deve essere Public (public in C#), Shared (static)
-
Deve essere marcato con l'attributo Microsoft.SQLServer.Server.SQLPRocedure (necessita l'Imports - using - del NameSpace Microsoft.SQLServer.Server);
-
Per poter accedere al DB con una SQLConnection si può sfruttare il Context Connection, cioé la connessione corrente istanziabile tramite SQLConnection("context connection = true") che non permette però di aggiungere altro (l'alternativa, magari per modificare l'utenza di collegamento al DB, è creare una SQLConnection con una ConnectionString "standard");
-
La definizione dei parametri di input e output del metodo devono essere dei tipi specificati nel NameSpace System.Data.SqlTypes; il parametro di OUTPUT deve essere, oltre che ByRef (out), marcato con l'attributo Out() appartenente al Namespace System.RunTime.InteropServices.