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_InterieurLe 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.
- Depuis Votre IDE préféré (dans mon cas VS 2008), je rajoute un projet de type Sql Server.
- Je crée ensuite une classe de type fonction managée que j'appelle GetWeatherManagedFunction
- 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
Et voilou !
Allez, on envoit notre Deploy !
Quatrième étape : Vérification et test
Comment vérifier et tester tout ça ?
Tout d'abord une vérification des assemblys de ma base de données AdventureWorks :
On voit bien mes deux assemblys déployées, ainsi que ma fonction managée.
Le test :
Deux façons de faire : Directement depuis SQL Serveur, via une bonne vieille requête :
(je vous jure que j'ai eu ce résultat au premier essai ;) comme quoi le hasard donne des résultats trés cohérent !!)
Soit avec notre Visual Studio, qui en plus va nous permettre de .. débugguer !!!
Hop, petit point d'arrét dans le fichier SQL test.sql que j'ai préparé, et lancement (F5) :
Allez, un peu de pas à pas dans le code de ma fonction CLR embarquée, qui est elle embarquée dans SQL Serveur 2005 (relisez lentement cette dernière phrase et imaginez ce qui doit tourner derrière pour rendre cela possible ;))
Voilà, tout est réglé et fonctionne à merveille :)
Razzor Rulez ! (Private Joke ;))
Tiens, quelques DMV (Data Manipulations Views) utiles à vos développements avec SQL Serveur 2005 :
Récupérer la liste des tables d'une base de données :
Récupérer les clés étrangères d'une base de données :
Select * from sys.foreign_keys
Récupérer l'ensemble des index d'une base de données :
select * from sys.indexes
Récupérer les contraintes d'une base de données :
Select * from sys.check_constraints
Récupérer les colonnes d'une base de données :
Select * from sys.all_columns
Faisons un peu de liaisons entre tout ça :
En passant en paramètre le nom de la table :
Declare @TableName NVarchar(255)
Set @TableName = 'order_details'
SELECT *
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
Si on veut avoir quelque chose de plus "lisible" maintenant, on peut faire quelque chose dans le style :
Récupérer les clés primaires d'une table :
Declare @TableName NVarchar(255)
Set @TableName = 'order_details'
-- Clé primaires (Index CLUSTERED)
SELECT
i.name AS [Primary Key Name],
serverproperty(N'Servername') as ServerName,
db_name() as DataBaseName ,
tbl.name as TableName,
SCHEMA_NAME(tbl.schema_id) as SchemaName
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
WHERE i.is_primary_key=1 and tbl.name= @TableName
Récupérer les index d'une base (qui ne soient pas une clé primaire):
-- Index non clé primaire
SELECT
i.name AS [Index Name],
serverproperty(N'Servername') as ServerName,
db_name() as DataBaseName ,
tbl.name as TableName,
SCHEMA_NAME(tbl.schema_id) as SchemaName
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
WHERE i.is_primary_key=0 and tbl.name= @TableName
Récupérer les foreign keys :
-- Foreign keys
SELECT
cstr.name AS [Foreign key Name],
serverproperty(N'Servername') as ServerName,
db_name()as DataBaseName,
tbl.name as TableName,
SCHEMA_NAME(tbl.schema_id) as SchemaName ,
cstr.create_date AS [CreateDate]
FROM
sys.tables AS tbl
INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
WHERE tbl.name= @TableName
Si vous avez lu mon précédent post, vous avez peut être envie de faire fonctionner WCfSvcHost avec vos Services WCF existant.
Voici la manipulation à effectuer pour inclure cette fonctionnalité trés utilse dans VS 2008.
Vous devez pour cela éditer votre fichier .CSPROJ (qui rappellons le est un fichier MSBUILD)
| Première étape : Décharger le projet de votre solution |
 |
Deuxième étape :
Editer le fichier de configuration : |
 |
Vous devez avoir maintenant dans votre fenêtre principale, le fichier XML MSBUILD de votre projet, comme ceci :
Troisième étape : Il vous suffit alors de rajouter ces deux lignes dans le propertygroup Debug (le premier) :
<ProjectTypeGuids>
{3D9AD99F-2412-4246-B90B-4EAA41C64699};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}
</ProjectTypeGuids>
<StartArguments>/client:"WcfTestClient.exe"</StartArguments>
Et c'est fini :)
Rechargez votre Projet, et lancez (F5)
Je viens de découvrir un truc tout simplement ... énorme !
Dans Visual Studio 2008, vous avez la possibilité de créer un projet de type WCF Service library (C'est pas ça le truc énorme, je vous rassure :) )
Outre le fait de vous créer un template de projet de type service WCF, vous allez avoir la possibilité de Hoster votre service et ce, des plus simplement !
Mais remontons un peu le temps.. Comment c'est qu'on faisait avant hein ?
Et bien on avait le choix de faire une application hôte chargée de monter notre service en mémoire. Hôte de type :
- Windows Service
- Site Web hosté sous IIS
- Application console / Winforms
Vous le voyez le truc énorme poindre le bout de son nez ? Et oui VS 2008 va vous permettre d'hoster vos services sans adjoindre un nouveau projet de type Hôte !
Tiens, prenons notre service créé via "nouveau projet - WCF Service Library".
qui est de type Class Library hein !
Cliquez sur "Run" (oui oui, Run sur une assembly de type DLL) et là oh surprise, vous voyez apparaître une nouvelle application, qui va hoster votre service !
Je vous présente WCfSvcHost !
Vous avez même maintenant 2 application Windows Forms qui, pour l'une, va vous montrer l'ensemble des services hostés :
Et pour l'autre, l'ensemble des EndPoints présents.
Et là où ça devient encore plus fendard, c'est la possibilité de "tester" les services exposés :
Voir même, de consulter le message envoyé et reçu !
C'est pas Beautiful tout ça hein !
Bon WCF ;)
Nouvel article !
MAJ du 05/12/2007 : Rajout du lien vers l'article au format word
Je vous propose de retrouver un nouvel article sur un sujet que j'ai commencé à étudier sérieusement :) Microsoft Synchronization Framework.
Il existe plusieurs "fournisseurs" de synchro, et je vous propose une introduction à Sync for Ado.Net
Vous allez décrouvrir comment il simple et rapide de mettre en oeuvre une synchronisation entre un poste nomade déconnecté et une base de données distance.
Retrouver l'article complet sur le site TechHeadBrothers :
Microsoft Synchronization Services for Ado.Net
Vous pouvez aussi retrouver cet article au format Word sur le site Bewise :
Microsoft Synchronization Services for Ado.Net (Word)
Bonne lecture !