Fonctions scalaires CLR

Spécifications relatives aux fonctions scalaires CLR

Les fonctions scalaires .NET Framework sont implémentées en tant que méthodes d'une classe dans un assembly .NET Framework. Les paramètres d'entrée et le type retourné par une fonction scalaire peuvent être n'importe lequel des types de données scalaires pris en charge par SQL Server, à l'exception de varchar, char, rowversion, text, ntext, image, timestamp, table ou cursor. Les fonctions scalaires doivent garantir une correspondance entre le type de données SQL Server et le type de données de retour de la méthode d'implémentation. Pour plus d'informations sur les conversions de type, consultez Mappage des données de paramètres CLR.

Lors de l'implémentation d'une fonction scalaire .NET Framework dans un langage .NET Framework, l'attribut personnalisé SqlFunction peut être spécifié de façon à inclure des informations supplémentaires à propos de la fonction. L'attribut SqlFunction indique si la fonction accède ou modifie des données, si elle est déterministe et si elle implique des opérations de virgule flottante.

Les fonctions scalaires définies par l'utilisateur peuvent être déterministes ou non déterministes. Une fonction déterministe retourne toujours le même résultat lorsqu'elle est appelée avec un jeu de paramètres d'entrée spécifique. Une fonction non déterministe peut retourner des résultats différents lorsqu'elle est appelée avec un jeu de paramètres d'entrée spécifique.

Paramètres table

Exemple de fonction scalaire CLR

Voici une fonction scalaire simple qui accède à des données et renvoie une valeur entière :

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

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount()
    {
        using (SqlConnection conn 
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
            return (int)cmd.ExecuteScalar();
        }
    }
}

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class T
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReturnOrderCount() As Integer
        Using conn As New SqlConnection("context connection=true")
            conn.Open()
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
            Return CType(cmd.ExecuteScalar(), Integer)
        End Using
    End Function
End Class

La première ligne de code fait référence à Microsoft.SqlServer.Server afin d'accéder à des attributs et à System.Data.SqlClient afin d'accéder à l'espace de noms ADO.NET. (Cet espace de noms contient SqlClient, le fournisseur de données .NET Framework pour SQL Server.)

Ensuite, la fonction reçoit l'attribut personnalisé SqlFunction, qui se trouve dans l'espace de noms Microsoft.SqlServer.Server. L'attribut personnalisé indique si la fonction définie par l'utilisateur utilise le fournisseur in-process pour lire les données sur le serveur. SQL Server n'autorise pas les fonctions définies par l'utilisateur à mettre à jour, insérer ou supprimer des données. SQL Server peut optimiser l'exécution d'une fonction définie par l'utilisateur qui n'utilise pas le fournisseur in-process. Cela est indiqué en affectant la valeur DataAccessKind.None à DataAccessKind. Sur la ligne suivante, la méthode cible est une méthode statique publique (partagée dans Visual Basic .NET).

La classe SqlContext, située dans l'espace de noms Microsoft.SqlServer.Server, peut ensuite accéder à un objet SqlCommand avec une connexion à l'instance SQL Server déjà configurée. Bien qu'il ne soit pas utilisé ici, le contexte de transaction actuel est également disponible par le biais de l'API System.Transactions.

La plupart des lignes de code dans le corps de la fonction doivent sembler familières aux développeurs ayant écrit des applications clientes qui utilisent les types de l'espace de noms System.Data.SqlClient.

[C#]

using(SqlConnection conn = new SqlConnection("context connection=true")) 
{
   conn.Open();
   SqlCommand cmd = new SqlCommand(
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
   return (int) cmd.ExecuteScalar();
}  

[Visual Basic]

Using conn As New SqlConnection("context connection=true")
   conn.Open()
   Dim cmd As New SqlCommand( _
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
   Return CType(cmd.ExecuteScalar(), Integer)
End Using

Le texte de commande approprié est spécifié en initialisant l'objet SqlCommand. L'exemple précédent compte le nombre de lignes dans la table SalesOrderHeader. Ensuite, la méthode ExecuteScalar de l'objet cmd est appelée. Elle retourne une valeur de type int basée sur la requête. Pour finir, le nombre de commandes (« order count ») est retourné à l'appelant.

Si ce code est enregistré dans un fichier nommé FirstUdf.cs, il peut être compilé dans un assembly comme suit :

[C#]

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs 

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

Voici la requête Transact-SQL et un exemple d'appel pour inscrire l'assembly et la fonction définie par l'utilisateur :

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';
GO

CREATE FUNCTION CountSalesOrderHeader() RETURNS INT 
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount; 
GO

SELECT dbo.CountSalesOrderHeader();
GO

Notez qu'il n'est pas obligatoire que le nom de fonction tel qu'exposé dans Transact-SQL corresponde au nom de la méthode statique publique cible.

Voir aussi

Concepts

Autres ressources