Introduzione all'integrazione di CLR

  • Articolo

Si applica a:SQL Server

Questo articolo offre informazioni generali sugli spazi dei nomi e sui cataloghi necessari per generare oggetti database usando l'integrazione di Microsoft SQL Server con Common Language Runtime (CLR) di .NET Framework. In questo articolo viene inoltre illustrato come scrivere, generare ed eseguire una stored procedure CLR semplice scritta in Microsoft Visual C# e Visual Basic.

Spazi dei nomi necessari

I componenti necessari per sviluppare oggetti di database CLR di base vengono installati con SQL Server. La funzionalità di integrazione CLR viene esposta in un assembly chiamato System.Data.dll, che fa parte di .NET Framework. Questo assembly è disponibile in Global Assembly Cache (GAC) e nella directory di .NET Framework. Poiché un riferimento a questo assembly viene in genere aggiunto tramite l’utilità da riga di comando e Microsoft Visual Studio, non è necessario aggiungerlo manualmente.

L'assembly System.Data.dll contiene gli spazi dei nomi seguenti, necessari per generare oggetti database CLR:

  • System.Data
  • System.Data.Sql
  • Microsoft.SqlServer.Server
  • System.Data.SqlTypes

Suggerimento

Il caricamento di oggetti database CLR in Linux è supportato, ma deve essere compilato con .NET Framework (l'integrazione CLR di SQL Server non supporta .NET Core o .NET 5 e versioni successive). Inoltre, gli assembly CLR con il set di autorizzazioni EXTERNAL_ACCESS o UNSAFE non sono supportati in Linux.

Scrivere una stored procedure "Hello World"

Copia e incolla il codice Visual C# o Visual Basic seguente in un editor di testo e salvalo in un file chiamato 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!";
    }
}

Questo programma contiene un singolo metodo statico su una classe pubblica. Questo metodo usa due nuove classi, SqlContext e SqlPipe, per creare oggetti database gestiti e restituire un messaggio semplice. Il metodo assegna anche la stringa "Hello world!" come valore di un parametro out. Questo metodo può essere dichiarato come stored procedure in SQL Server e quindi eseguito allo stesso modo come stored procedure di Transact-SQL.

Il programma verrà quindi generato come catalogo, caricato in SQL Server ed eseguito come stored procedure.

Generazione della stored procedure "Hello World"

Per impostazione predefinita, SQL Server installa i file di ridistribuzione di .NET Framework. Tali file includono csc.exe e vbc.exe, i compilatori della riga di comando per programmi Visual C# e Visual Basic. Per generare il campione, è necessario modificare la variabile del percorso in modo che punti all’elenco contenente csc.exe o vbc.exe. Il percorso di installazione seguente è quello predefinito di .NET Framework.

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

Version contiene il numero di versione del .NET Framework installato. Ad esempio:

C:\Windows\Microsoft.NET\Framework\v4.8.0

Dopo avere aggiunto l’elenco di .NET Framework al percorso, puoi generare la stored procedure di campionamento 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 avere generato con successo la procedura di campionamento, è possibile testarla in SQL Server. A tale scopo, apri SQL Server Management Studio e creare una nuova query, connettendoti a un database di test appropriato (ad esempio il database di campionamento AdventureWorks).

L'esecuzione di codice CLR (Common Language Runtime) in SQL Server è impostata per impostazione predefinita a OFF. Il codice CLR può essere abilitato tramite la stored procedure sp_configure di sistema. 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 nell’elenco C:\. Aggiungi l'istruzione Transact-SQL seguente alla query.

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 stored procedure può 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;

Verrà restituito l'output seguente nella finestra dei messaggi di SQL Server Management Studio.

Hello world!
Hello world!

Rimozione della stored procedure "Hello World" di campionamento

Al termine dell'esecuzione della stored procedure di campionamento, è 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;

Passaggi successivi

Per altre informazioni sull'integrazione di CLR con SQL Server, vedi gli articoli seguenti: