Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Créer une Fonction de type table (TVF) via .Net (SQLCLR) – Exemple d’un Split de chaîne

A chaque fois que je me retrouve à devoir écrire une fonction de type table sous SQL Server avec .Net, la seule chose dont je me souviens c'est combien c'est galère de trouver les explications avec mon moteur de recherche favoris. C'est pourquoi j'ai décidé de me lancer dans un exemple simple histoire de retrouver plus facilement, la manière de concevoir ce type de code.

L'exemple est simple et s'appuie sur l'un des domaines où .Net se révèle bien plus efficace que SQL Server : le traitement de chaîne de caractères.

Plus particulièrement je m'intéresse à la méthode Split de la classe String, qui me permet de découper une chaine de caractère en tableau grâce à un caractère qui fera office de séparateur.

Ce qui suit est écrit en C#... Désolé par avance pour les VBistes :o(

Pour commencer il faut créer un Projet de type SQL Server, soit en C#, soit en Vb.Net (ou autre). Notez que ce type de projet n'est pas vraiment obligatoire, il permet juste de fournir des modèles de développement et permet de grandement faciliter le déploiement du code sur un serveur SQL Server.


Une fois le projet créée, on ajoute une nouvelle « User-Defined Function » à notre projet. Visual Studio considère que vous voulez créer une fonction scalaire, il n'y a pas de problème à partir la dessus, on changement le type de retour.

On commence par indiquer que notre fonction renvoie une « table », on substituant le type de retour par un IEnumerable, et on aura préalablement pris soin de mettre les bonnes références pour cela (using System.Collections).

J'ajoute les paramètres d'appel de ma fonction. Attention les types ne sont pas les types standard .Net ils s'appellent SqlXXXXX, par exemple dans notre cas je passerai 2 SqlString.

public static IEnumerable Split(SqlString input, SqlString separator)
{
return;
}

Le reste du code est loin d'être complexe, les types SqlString se convertissant en String et réciproquement très facilement. Attention seule le type Unicode existe, ce sont des nvarchar uniquement !

public static IEnumerable Split(SqlString input, SqlString separator)
{
return
input.ToString().Split(separator.ToString().ToCharArray());
}

1 ligne de code ;o) Faites la même chose en SQL et on en reparle. Bon ok, on n'a pas fini.

Il fait maintenant fournir une 2ème méthode qui va être appelé à chaque itération sur notre IEnumerable de retour. Il faut savoir que les TVF (Table Value Function) faites en SQLCLR ont un comportement très différent de celle SQL. En SQL on met le résultat dans l'équivalent d'une variable de type table et on renvoie cette pseudo table une fois terminé. Via SQLCLR, c'est ligne à ligne, ce qui peut se révéler intéressant car les premiers résultats sont fournis assez vite, mais coté code cela nécessite une seconde méthode charger de renvoyer les lignes de la table.

Il faut fournir 2 attributs obligatoires à notre TVF :

FillRowMethodName à Qui fournit le nom de le méthode d'iteration sur le IEnumerable

TableDefinition à Qui fournit la structure la table renvoyée au moteur de base de données

Ce qui donne :

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow", TableDefinition="col nvarchar(max)")]
public
static IEnumerable Split(SqlString input, SqlString separator)
{
return
input.ToString().Split(separator.ToString().ToCharArray());
}

La définition de la table est celle que l'on fournirait à un CREATE TABLE, par contre il n'est pas possible d'y adjoindre de contrainte ou d'index !

On ajoute maintenant la méthode FillRow. Celle-ci prend obligatoirement en premier argument un type « object » qui est la ligne du IEnumerable. Les arguments suivant, il y en a un par colonne de retour. Tous de type « out » et doivent exactement correspondre au type déclaré dans TableDefinition. A nouveau pas de String mais un SqlString dans le cas présent.

public static void FillRow(object Row, out SqlString col)
{
col = new SqlString((string)Row);
}

Le code est très simple, le but est uniquement de découper la ligne sous forme de colonnes à renvoyer au moteur.

Le code complet :

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

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow", TableDefinition="col nvarchar(max)")]
public
static IEnumerable Split(SqlString input, SqlString separator)
{
return
input.ToString().Split(separator.ToString().ToCharArray());
}

public static void FillRow(object Row, out SqlString col)
{
col = new SqlString((string)Row);
}

};

Maintenant, un déploiement va créer directement l'assembly dans la base de données cible. La fonction est elle aussi créer automatiquement au moment du déploiement. Il est tout a fait possible de juste compiler l'assembly, de la déployer manuellement sur le serveur et de créer la fonction, mais là n'est pas le but de ce billet.

Côté SQL, on teste (attention de vérifier que l'option 'CLR enabled' est active au niveau de l'instance) :

select *
from
split('qskldhqs jkhdjkqsdh qhdsjkq', ' ')

Ce qui renvoie une petite table :

  • qskldhqs
  • jkhdjkqsdh
  • qhdsjkq

Bon développement…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié jeudi 17 décembre 2009 16:00 par christian
Classé sous : ,

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Technofolies, votre évènement numérique de l'année par Le Blog (Vert) d'Arnaud JUND le 09-26-2014, 18:40

- Xamarin : From Zero to Hero par Fathi Bellahcene le 09-24-2014, 17:35

- Conférences d’Automne 2014 par Le blog de Patrick [MVP SharePoint] le 09-24-2014, 14:53

- [TFS] Supprimer un projet de Visual Studio Online par Blog de Jérémy Jeanson le 09-22-2014, 20:42

- Nouveau blog en anglais / New blog in english ! par Le blog de Patrick [MVP SharePoint] le 09-18-2014, 18:42

- [ #Yammer ] From Mailbox to Yammer and back / De votre messagerie vers Yammer et retour ! par Le blog de Patrick [MVP SharePoint] le 09-15-2014, 11:31

- [ #Office 365 ] New service settings panel / Nouveau panneau de paramétrage des services par Le blog de Patrick [MVP SharePoint] le 09-11-2014, 08:50

- Problème de déploiement pour une démo SharePoint/TFS? par Blog de Jérémy Jeanson le 09-10-2014, 21:52

- [ #Office365 ] Delve first impressions / Premières impressions sur Delve par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 16:57

- [ #Office365 ] How to change Administration console language ? / Comment changer la langue de la console d’administration ? par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 08:25