Fonctions scalaires CLR

S’applique à : SQL Server

Une fonction scalaire (SVF) retourne une valeur unique, telle qu’une chaîne, un entier ou une valeur de bits. Vous pouvez créer des fonctions scalaires définies par l’utilisateur dans du code managé à l’aide de n’importe quel langage de programmation .NET Framework. Ces fonctions sont accessibles à Transact-SQL ou à d’autres codes managés. Pour plus d’informations sur les avantages de l’intégration clR et le choix entre le code managé et Transact-SQL, consultez Vue d’ensemble de l’intégration 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és à partir d’une fonction SVF peuvent être l’un 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 fichiers SVFs 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 types, consultez Mappage des données de paramètre 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

Les paramètres table (types de tables définis par l'utilisateur et passés dans une procédure ou une fonction) offrent un moyen efficace pour passer plusieurs lignes de données au serveur. Les tvps offrent des fonctionnalités similaires aux tableaux de paramètres, mais offrent une plus grande flexibilité et une intégration plus étroite avec 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 transmis en tant que paramètre table à une procédure stockée managée ou à une fonction exécutée dans le processus SQL Server. Pour plus d’informations sur les tvps, consultez Utiliser des 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 .NET Framework Fournisseur de données 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 UDF qui n’utilise pas le fournisseur in-process. Cela est indiqué en affectant la valeur DataAccessKind à DataAccessKind.None. 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  

La requête Transact-SQL et un exemple d’appel pour inscrire l’assembly et la fonction UDF sont les suivants :

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';  
GO  
  
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT   
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;   
GO  
  
SELECT dbo.CountSalesOrderHeader();  
GO  
  

Notez que le nom de la fonction tel qu’exposé dans Transact-SQL n’a pas besoin de correspondre au nom de la méthode statique publique cible.

Voir aussi

Mappage des données de paramètres CLR
Vue d’ensemble des attributs personnalisés de l’intégration du CLR
Fonctions définies par l'utilisateur
Accès aux données à partir d'objets de base de données CLR