Mim

Web Service. SQL Managed Stored Procedure

Aujourd'hui petite article pour pouvoir appeller, depuis une procédure stockée managée, un web service.

Pour l'exemple, on va être plus élégant, on va faire une fonction managée qui renverra pour une ville donnée, la température (un SqlInt32)

Quelle est la finalité de l'exercice ?

Eh bien par exemple, appeller depuis du code T-SQL un web service qui me renverrait le temps qu'il fait à Toulouse !

Un exemple de ce que l'on veut à la fin :

IF (dbo.GetWeatherManagedFunction('Toulouse') > 0)
    Select * from TableLoisirs_Exterieur
Else
    Select * from TableLoisirs_Interieur

Le principe n'est pas le développement du Web Service lui même. Pour l'exemple j'en ai créé un qui renvoit des "données exemples".

Première étape : Création du projet Sql Clr.

1. Depuis Votre IDE préféré (dans mon cas VS 2008), je rajoute un projet de type Sql Server.
2. Je crée ensuite une classe de type fonction managée que j'appelle GetWeatherManagedFunction
3. Je rajoute une web référence vers mon Web Service

On arrive à quelque chose ressemblant à ça :

Note : Vous avez remarqué la présence de mon projet web service qui est chargé de me renvoyer l'information. Pour votre culture personnelle, voici le code de mon super web service :)

[WebMethod]
public int GetTemperature(String city)
{
    Random r = new Random();
    return r.Next(0, 35);
}

Ok, ça va pas chercher loin, mais qu'importe...

Deuxième étape : Le code de ma fonction sql clr managed.

Le code non plus n'est pas des plus complexe. On prend en entrée une donnée de type chaine de caractères (attention, pas String hein, SqlString ) et on renvoit un entier (SqlInt32)

Le code de ma fonction sql clr :

public static SqlInt32 GetWeatherManagedFunction(SqlString city)
{

    try
    {
        if (city.IsNull) return SqlInt32.Null;

        WSWeathersDatas ws = new WSWeathersDatas();

        int res = ws.GetTemperature(city.Value);

        return new SqlInt32(res);

        sqlCon.Close();
    }
    catch (Exception ex)
    {
        SqlContext.Pipe.Send("Erreur : " + ex.Message);
        return SqlInt32.Null;
    }
}

Notez l'utilisation de SqlContext pour écrire, via le Pipe, dans la fenêtre Output si une erreur est survenue.

Troisième étape : Déployer

Bien c'est là que ça se corse un tantinet.

Il faut savoir que Sql Serveur ne permet pas de charger à la volée les assemblys externes (ça parait evident comme ça, mais fallait le préciser). Hors notre Web Service a la bonne idée de sérializer, à la volée, tous les types requis au web service.

C'est balot. Du coup il va nous falloir générer la classe de sérialisation de notre assembly.

Comment faire cette petite manipulation. Et bien tout simplement dans la configuration de notre projet ! Onglet Build, Option Generate Serialization Assembly (c'est beau)

Première chose, pour voir ce que ça donne, nous allons faire une première Build.
Attention, j'ai pas dis "Déploiement", j'ai dis "juste une Build".

Alors justement pourquoi pas une Deploy ? Et bien tout simplement parce que votre assembly n'a pas les droits suffisants pour accéder à des ressources externes (notre web service au pif) voir faire du Call Back.

Il nous faut donc la marquer, non pas Safe, mais External Access. Toujour dans les propriétés de notre projet, rayon Database :

C'est presque terminé :)

Note : Il se peut que lors du déploiement que nous allons effectuer plus tard, vous ayez une erreur, sur le passage en external access. Il existe plusieurs contournements, le plus simple étant d'autoriser la base de données, sur ce genre d'action, via le script :

ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY ON

Bien, lors du déploiement, VS 2008 (ou 2005) ne va déployer QUE notre projet. Hors il nous faut AUSSI l'assembly Serializer de notre classe. Nous allons donc ajouter deux fichiers, correctement nommés postdeployscript.sql et predeployscript.sql. Le premier script s'éxécute AVANT le déploiement, le deuxième APRES.

La solution ressemble alors à ceci :

Que contiennent ces deux scripts :

Dans le premier, le drop de l'assembly de sérialisation :

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'AdvSqlClrXml') 
    DROP ASSEMBLY AdvSqlClrXml with NO DEPENDENTS;

Dans le deuxième, l'enregistrement de cette même assembly dans SQL Server 2005

CREATE ASSEMBLY [AdvSqlClrXml] from
'C:\Users\spertus\Desktop\TestWSInSql\AdvSqlClr\bin\Debug\AdvSqlClr.XmlSerializers.dll'
WITH permission_set = SAFE

Quatrième étape : Vérification et test