Procedura: creare ed eseguire un aggregato CLR di SQL Server

Articolo
09/12/2008

Aggiornamento: novembre 2007

Creare aggregati SQL aggiungendo elementi Aggregate a progetti SQL Server. Al termine della distribuzione, gli aggregati creati in codice gestito vengono chiamati ed eseguiti come qualsiasi altro aggregato di SQL Server.

Nota:
In Microsoft SQL Server la funzionalità di integrazione di CLR (Common Language Runtime) è disattivata per impostazione predefinita, ma deve essere attivata per poter utilizzare gli elementi del progetto SQL Server. Per attivare la funzionalità di integrazione di CLR, utilizzare l'opzione clr enabled della stored procedure sp_configure. Per ulteriori informazioni, vedere Attivazione dell'integrazione CLR.

In Microsoft SQL Server la funzionalità di integrazione di CLR (Common Language Runtime) è disattivata per impostazione predefinita, ma deve essere attivata per poter utilizzare gli elementi del progetto SQL Server. Per attivare la funzionalità di integrazione di CLR, utilizzare l'opzione clr enabled della stored procedure sp_configure. Per ulteriori informazioni, vedere Attivazione dell'integrazione CLR.

Nota:
Gli aggregati di SQL Server richiedono l'implementazione di quattro metodi specifici: Init, Accumulate, Merge e Terminate. Per ulteriori informazioni, vedere l'argomento relativo alle funzioni di aggregazione definite dall'utente SQL CLR .NET nella documentazione in linea di SQL.

Nota:
È possibile che le finestre di dialogo e i comandi di menu visualizzati siano diversi da quelli descritti nella Guida in linea a seconda delle impostazioni attive o dell'edizione del programma. Per modificare le impostazioni, scegliere Importa/Esporta impostazioni dal menu Strumenti. Per ulteriori informazioni, vedere Impostazioni di Visual Studio.

Creazione di un aggregato di SQL Server

Per creare un aggregato di SQL Server

Aprire un Progetto SQL Server esistente o crearne uno nuovo. Per ulteriori informazioni, vedere Procedura: creare un progetto SQL Server.
Scegliere Aggiungi nuovo elemento dal menu Progetto.
Selezionare Aggregato nella Finestra di dialogo Aggiungi nuovo elemento.
Nella casella Nome digitare un nome per il nuovo aggregato.
Aggiungere il codice da eseguire quando viene eseguito l'aggregato. Vedere il primo esempio riportato di seguito.

Nota:
Gli esempi di C++ devono essere compilati con l'opzione del compilatore /clr:safe.

Distribuire l'aggregato in un SQL Server. Per ulteriori informazioni, vedere Procedura: distribuire elementi del progetto SQL Server su SQL Server.
Eseguire il debug dell'aggregato eseguendolo in SQL Server. Vedere il secondo esempio riportato di seguito.

Esempio

In questo esempio viene creato un aggregato per contare le vocali. Questo aggregato conta le vocali presenti in una colonna di dati di tipo stringa. Contiene inoltre i quattro metodi richiesti riportati di seguito, che è possibile eseguire in multithreading: Init, Accumulate, Merge e Terminate.

Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<SqlUserDefinedAggregate(Format.Native)> _
Public Structure CountVowels

    ' count only the vowels in the passed-in strings
    Private countOfVowels As SqlInt32


    Public Sub Init()
        countOfVowels = 0
    End Sub


    Public Sub Accumulate(ByVal value As SqlString)
        Dim stringChar As String
        Dim indexChar As Int32

        ' for each character in the given parameter
        For indexChar = 0 To Len(value.ToString()) - 1

            stringChar = value.ToString().Substring(indexChar, 1)

            If stringChar.ToLower() Like "[aeiou]" Then

                ' it is a vowel, increment the count
                countOfVowels = countOfVowels + 1
            End If
        Next
    End Sub


    Public Sub Merge(ByVal value As CountVowels)

        Accumulate(value.Terminate())
    End Sub


    Public Function Terminate() As SqlString

        Return countOfVowels.ToString()
    End Function
End Structure

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountVowels
{
    // count only the vowels in the passed-in strings
    private SqlInt32 countOfVowels;


    public void Init()
    {
        countOfVowels = 0;
    }


    public void Accumulate(SqlString value)
    {
        // list of vowels to look for
        string vowels = "aeiou";

        // for each character in the given parameter
        for (int i=0; i < value.ToString().Length; i++)
        {
            // for each character in the vowels string
            for (int j=0; j < vowels.Length; j++)
            {
                // convert parameter character to lowercase and compare to vowel
                if (value.Value.Substring(i,1).ToLower() == vowels.Substring(j,1))
                {
                    // it is a vowel, increment the count
                    countOfVowels+=1;
                }
            }
        }
    }


    public void Merge(CountVowels value)
    {
        Accumulate(value.Terminate());
    }


    public SqlString Terminate()
    {
        return countOfVowels.ToString();
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlTypes;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Aggregate, add the following to your debug.sql file:
//
// SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
// FROM Person.Contact
// GROUP BY LastName
// ORDER BY LastName
//

[Serializable]
[SqlUserDefinedAggregate(Format::Native)]
public value struct CountVowels
{
public:
    void Init()
    {
        countOfVowels = 0;
    }

    void Accumulate(SqlString value)
    {
        // list of vowels to look for
        String ^vowels = "aeiou";

        // for each character in the given parameter
        for (int i=0; i < value.ToString()->Length; i++)
        {
            // for each character in the vowels string
            for (int j=0; j < vowels->Length; j++)
            {
                // convert parameter character to lowercase and compare to vowel
                if (value.Value->Substring(i, 1)->ToLower() == vowels->Substring(j, 1))
                {
                    // it is a vowel, increment the count
                    countOfVowels+=1;
                    break;
                }
            }
        }
    }

    void Merge(CountVowels value)
    {
        Accumulate(value.Terminate());
    }

    SqlTypes::SqlString Terminate()
    {
        return countOfVowels.ToString();
    }

private:
    // count only the vowels in the passed-in strings
    SqlInt32 countOfVowels;
};

Dopo aver distribuito l'aggregato, verificarlo eseguendolo su SQL Server e assicurarsi che vengano restituiti i dati corretti. Questa query restituisce un gruppo di risultati ottenuto dal conteggio delle vocali di tutti i valori presenti nella colonna LastNames della tabella Contact.

SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
FROM Person.Contact
GROUP BY LastName
ORDER BY LastName

Vedere anche

Condividi tramite

Procedura: creare ed eseguire un aggregato CLR di SQL Server

Creazione di un aggregato di SQL Server

Per creare un aggregato di SQL Server

Esempio

Vedere anche

Attività

Concetti

Riferimenti

Altre risorse

Risorse aggiuntive