Fonctions scalaires CLR

Article
05/07/2013

Une fonction scalaire renvoie une valeur unique, telle qu'une chaîne, un entier ou une valeur binaire. À compter de SQL Server 2005, vous pouvez créer des fonctions scalaires définies par l'utilisateur dans du code managé à l'aide de tout langage de programmation .NET Framework. Ces fonctions sont accessibles au code Transact-SQL ou autre code managé. Pour plus d'informations sur les avantages offerts par l'intégration du CLR et le choix entre le code managé et Transact-SQL, consultez Vue d'ensemble de l'intégration du 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.

[!REMARQUE]

Ne marquez pas une fonction comme étant déterministe si elle ne produit pas toujours les mêmes valeurs de sortie à partir des mêmes valeurs d'entrée et du même état de base de données. Le marquage d'une fonction comme étant déterministe alors que cette dernière ne l'est pas vraiment peut provoquer une altération des vues indexées et des colonnes calculées. Pour marquer une fonction comme déterministe, vous devez affecter la valeur « true » à la propriété IsDeterministic.

Paramètres table

Les paramètres table, des types de tables définis par l'utilisateur qui sont passés dans une procédure ou une fonction, offrent un moyen efficace pour passer plusieurs lignes de données au serveur. Ils procurent une fonctionnalité semblable aux tableaux de paramètres, mais offrent une meilleure souplesse et une intégration plus étroite à Transact-SQL. Ils sont également susceptibles de générer de meilleures performances. Les paramètres table aident également à réduire le nombre d'allers-retours au serveur. Au lieu d'envoyer plusieurs demandes au serveur, comme avec une liste de paramètres scalaires, les données peuvent être envoyées au serveur en tant que paramètres table. Un type de table défini par l'utilisateur ne peut pas être passé en tant que paramètre table à, ou retourné à partir de, une procédure stockée managée ou une fonction qui s'exécute dans le processus SQL Server. Pour plus d'informations sur les paramètres table, consultez Utiliser les paramètres table (Moteur de base de données).

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

[!REMARQUE]

/t:library indique qu'une bibliothèque, plutôt qu'un fichier exécutable, doit être produite. Les fichiers exécutables ne peuvent pas être inscrits dans SQL Server.

[!REMARQUE]

Les objets de base de données Visual C++ compilés avec /clr:pure ne sont pas pris en charge pour l'exécution sur SQL Server. Il s'agit par exemple d'objets de base de données tels que des fonctions scalaires.

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.