Grazie al Framework .NET è stato introdotto il nuovo concetto di Assembly e reference. In pratica, adesso possiamo creare un semplice contenitore di funzioni (dll) e farla funzionare da tutti i programmi che sfruttano il Framework.
Visto che anche SQL Server fa parte di questi programmi, allo stesso modo possiamo creare un assembly in .NET, inserirlo in SQL Server e da questo eseguirlo tramite un comando T-SQL.
Creazione del progetto .NET
Per creare il progetto utilizziamo Visual Studio 2005. Creiamo un nuovo progetto di tipo libreria di classi, lo chiamiamo DATA_FSX, ovvero funzioni per i dati e lo salviamo. A questo punto dobbiamo inserire una nuova classe che chiamiamo Motore.
Listato 1. Classe Motore
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class Motore
'Inizializzo connessione e datareader
Private Const SQLCONN As String = "context connection=true"
Private CN As SqlConnection
Private CMD As SqlCommand
Private DR As SqlDataReader
Public Shared Function Saluta() As Integer
InviaSQL("Ciao da VB.NET!!")
Return 0
End Function
'Funzioni in Overload per inviare al motore di database le richieste
Public Shared Sub InviaSQL(ByVal value As String)
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(value)
End Sub Public Shared
Sub InviaSQL(ByVal value As SqlDataReader)
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(value)
End Sub
Public Shared Sub InviaSQL(ByVal value As Microsoft.SqlServer.Server.SqlDataRecord)
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(value)
End Sub
End Class
Abbiamo messo nella classe una serie di oggetti che ci consentono di eseguire operazioni sul database. Poi abbiamo creato in overloads, ovvero con lo stesso nome ma parametri di tipo diverso, una routine che invia i messaggi o i dati a SQL.
La prima funzione saluta(), dichiarata Shared (altrimenti non sarebbe visibile e utilizzabile da SQL), invia un semplice test sull'output. Per poter eseguire la funzione dobbiamo prima compilare la dll e andare a vedere dove è stata compilata. Poi apriamo l'editor T-SQL ed eseguiamo lo script per registrare l'assembly e associare una Stored Procedure alla funzione Saluta().
Listato 2. Registrazione assembly
CREATE ASSEMBLY [DATA_FSX]
FROM 'D:DATABASEDATA_FSXDATA_FSXbinReleaseDATA_FSX.dll'
WITH PERMISSION_SET = SAFE
--Associo una funzione dell' assembly ad una stored procedure
CREATE PROCEDURE Saluta
AS EXTERNAL NAME DATA_FSX.[DATA_FSX.Motore].Saluta
--eseguo la stored procedure
EXEC Saluta
Questa è il metodo standard per poter lavorare con SQLCLR, ovvero il linguaggio che fa da tramite tra .NET e SQL server.
Funzioni avanzate
Il secondo passo che dobbiamo fare è quello di interagire con i dati. Nella seconda funzione dobbiamo eseguire una SELECT e restituire dei dati particoli, filtrati. Quindi la funzione dovrà anche ricevere un parametro di input.
Nota: stiamo usando un assembly di tipo Shared, quindi alcune operazioni quali variabili locali o dispersione di dati, non sono consentite. In secondo luogo, ogni volta che modifichiamo il contenuto dell'assembly (dll), dobbiamo ricompilare, cancellare riferimenti (stored procedure) e sovrascrivere la vecchia registrazione assembly con una nuova. Questo perchè l'assembly è stato inglobato da SQL Server.
Per prima cosa modifichiamo la classe motore con questa nuova funzione.
Listato 3. Funzione per ordini
'Ricerca ordini per ID cliente
Public Shared Function Seleziona_Ordini(ByVal cliente As Int32) As Integer
'dichiaro qui le variabili perchè la funzione è shared
Dim CN As SqlConnection
Dim CMD As SqlCommand
Dim DR As SqlDataReader
'Istruzione SQL
CN = New SqlConnection("context connection=true")
CN.Open() CMD = New SqlCommand("SELECT ANA_NOME, ANA_COGNOME,ORD_CODICE
" & _
"FROM ANAGRAFICA INNER JOIN ORDINI " & _
"ON ANAGRAFICA.ANA_ID = ORDINI.ORD_ID_ANA " & _
"WHERE ANA_ID = " & cliente, CN)
'-->inviamo il lettore carico di dati
DR = CMD.ExecuteReader
InviaSQL(DR)
Return 0
End Function
Adesso dobbiamo registrare nuovamente tutto, questa volta però, quando andiamo a registrare la stored procedure, SQL ci fornirà un messaggio di errore perchè necessita di una variabile per passare il valore di cliente alla funzione VB. Nel listato 4.
Listato 4. Dichiarazione assembly con variabile
CREATE PROCEDURE Seleziona_Ordini
(@Cliente int)
AS EXTERNAL NAME DATA_FSX.[DATA_FSX.Motore].Seleziona_Ordini
SQLCLR consente di rendere il database uno strumento completo, in grado di effettuare anche operazioni complesse, non sempre eseguibili con il solo motore di database. Dobbiamo dire che in prima esecuzione, gli assembly sono più lenti di una normale query, ma una volta cachati si eguagliano.