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

- SharePoint : Bug sur la gestion des permissions et la synchronisation Office par Blog Technique de Romelard Fabrice le 07-10-2014, 11:35

- SharePoint 2007 : La gestion des permissions pour les Workflows par Blog Technique de Romelard Fabrice le 07-08-2014, 11:27

- TypeMock: mock everything! par Fathi Bellahcene le 07-07-2014, 17:06

- Coding is like Read par Aurélien GALTIER le 07-01-2014, 15:30

- Mes vidéos autour des nouveautés VS 2013 par Fathi Bellahcene le 06-30-2014, 20:52

- Recherche un passionné .NET par Tkfé le 06-16-2014, 12:22

- [CodePlex] Projet KISS Workflow Foundation lancé par Blog de Jérémy Jeanson le 06-08-2014, 22:25

- Etes-vous yOS compatible ? (3/3) : la feuille de route par Le blog de Patrick [MVP SharePoint] le 06-06-2014, 00:30

- [MSDN] Utiliser l'approche Contract First avec Workflow Foundation 4.5 par Blog de Jérémy Jeanson le 06-05-2014, 21:19

- [ #ESPC14 ] TH10 Moving mountains with SharePoint ! par Le blog de Patrick [MVP SharePoint] le 06-01-2014, 11:30