Introduzione all'integrazione con CLR

Articolo
06/13/2017

In questo argomento viene fornita una panoramica degli spazi dei nomi e delle librerie necessari per compilare oggetti di database usando l'integrazione SQL Server con Common Language Runtime (CLR). Inoltre viene illustrato come scrivere, compilare ed eseguire una semplice stored procedure CLR scritta in Microsoft Visual C#.

Spazi dei nomi necessari

A partire da SQL Server. La funzionalità di integrazione CLR viene esposta in un assembly denominato system.data.dll, che fa parte di .NET Framework. Questo assembly è disponibile in Global Assembly Cache (GAC) e nella directory di .NET Framework. In genere, viene aggiunto automaticamente un riferimento a questo assembly sia dagli strumenti della riga di comando sia da Microsoft Visual Studio, pertanto non è necessario aggiungerlo manualmente.

L'assembly system.data.dll contiene gli spazi dei nomi seguenti, necessari per la compilazione di oggetti di database CLR:

System.Data

System.Data.Sql

Microsoft.SqlServer.Server

System.Data.SqlTypes

Scrittura di una stored procedure "Hello World" semplice

Copiare e incollare il codice Visual C# o Microsoft Visual Basic in un editor di testo e salvarlo in un file denominato "helloworld.cs" o "helloworld.vb".

using System;  
using System.Data;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlTypes;  
  
public class HelloWorldProc  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void HelloWorld(out string text)  
    {  
        SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);  
        text = "Hello world!";  
    }  
}

Imports System  
Imports System.Data  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlTypes  
Imports System.Runtime.InteropServices  
  
Public Class HelloWorldProc  
    <Microsoft.SqlServer.Server.SqlProcedure> _   
    Public Shared  Sub HelloWorld(<Out()> ByRef text as String)  
        SqlContext.Pipe.Send("Hello world!" & Environment.NewLine)  
        text = "Hello world!"  
    End Sub  
End Class

Questo semplice programma contiene un singolo metodo statico su una classe pubblica. Questo metodo utilizza due nuove classi, SqlContext e SqlPipe, per creare oggetti di database gestiti e restituire un messaggio di testo semplice. Il metodo assegna inoltre la stringa "Hello world!" come valore di un parametro di output. Questo metodo può essere dichiarato come stored procedure nella stored procedure Transact-SQL.

Questo programma verrà quindi compilato come una libreria, caricato in SQL Server ed eseguito come una stored procedure.

Compilazione della stored procedure "Hello World"

Per impostazione predefinita, i file di ridistribuzione di Microsoft .NET Framework. Tali file includono csc.exe e vbc.exe, i compilatori della riga di comando per programmi Visual C# e Visual Basic. Per compilare l'esempio, è necessario modificare la variabile del percorso in modo che punti alla directory contenente csc.exe o vbc.exe. Di seguito viene indicato il percorso di installazione predefinito di .NET Framework.

C:\Windows\Microsoft.NET\Framework\(version)

version contiene il numero di versione del file di ridistribuzione di .NET Framework installato. Ad esempio:

C:\Windows\Microsoft.NET\Framework\v2.0.31113

Dopo avere aggiunto la directory di .NET Framework al percorso, è possibile compilare la stored procedure di esempio in un assembly con il comando indicato di seguito. L'opzione /target consente di compilare la stored procedure in un assembly.

Per i file di origine di Visual C#:

csc /target:library helloworld.cs

Per i file di origine di Visual Basic:

vbc /target:library helloworld.vb

Questi comandi consentono di avviare il compilatore Visual C# o Visual Basic utilizzando l'opzione /target per specificare la compilazione di una DLL della libreria.

Caricamento ed esecuzione della stored procedure "Hello World" in SQL Server

Dopo aver compilato correttamente la procedura di esempio, è possibile testarlo in SQL Server Management Studio e creare una nuova query, connettendosi a un database di test appropriato, ad esempio il database di esempio AdventureWorks.

La possibilità di eseguire il codice CLR (Common Language Runtime) è impostata su OFF per impostazione predefinita in SQL Server. Il codice CLR può essere abilitato usando la stored procedure di sistema sp_configure . Per altre informazioni, vedere Enabling CLR Integration.

A questo punto è necessario creare l'assembly per poter accedere alla stored procedure. Ai fini dell'esempio, si presuppone che sia stato creato l'assembly helloworld.dll nella directory C:\. Aggiungere alla query la seguente istruzione Transact-SQL.

CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

Dopo avere creato l'assembly, è possibile accedere al metodo HelloWorld utilizzando l'istruzione CREATE PROCEDURE. La stored procedure verrà denominata "hello":

  
CREATE PROCEDURE hello  
@i nchar(25) OUTPUT  
AS  
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld  
-- if the HelloWorldProc class is inside a namespace (called MyNS),  
-- the last line in the create procedure statement would be  
-- EXTERNAL NAME helloworld.[MyNS.HelloWorldProc].HelloWorld

Una volta creata, la routine potrà essere eseguita come una normale stored procedure scritta in Transact-SQL. Eseguire il comando seguente:

DECLARE @J nchar(25)  
EXEC hello @J out  
PRINT @J

In tal modo viene generato il seguente output nella finestra dei messaggi di SQL Server Management Studio.

Hello world!  
Hello world!

Rimozione della stored procedure "Hello World" di esempio

Al termine dell'esecuzione della stored procedure di esempio, è possibile rimuovere la procedura e l'assembly dal database di test.

Rimuovere innanzitutto la procedura utilizzando il comando drop procedure.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hello')  
   drop procedure hello

Dopo avere eliminato la procedura, è possibile rimuovere l'assembly contenente il codice di esempio.

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'helloworld')  
   drop assembly helloworld

Vedere anche

Stored procedure CLR
Estensioni specifiche in-process di SQL Server ad ADO.NET
Debug di oggetti di database CLR
Sicurezza per l'integrazione con CLR

Condividi tramite