[Sql Server] Utiliser .Net dans SQL 2005 ou l’intégration du CLR dans SQL
Avec SQL 2005 une nouvelle fonctionnalité existe qui permet d’intégrer et d’exécuter du code .net directement sur le serveur SQL. On bénéficie ainsi de la puissance d’un langage de développement et de la puissance d’un serveur avec un accès distant correspondant à celui de l’appel d’une procédure stockée.
Configuration
La première chose à faire est d’autoriser l’exécution du Common Langage Runtime sur notre serveur pour ça 2 options :
- Par script transac SQL :
« sp_configure 'clr enabled', 1;<?xml:namespace prefix = o />
GO
RECONFIGURE;
Go »
- Par outil graphique : on ouvre « Démarrer/ tout les programmes/Microsoft SQL server 2005/outils de configuration/configuration de la surface d’exposition SQL server / »
L’interface suivante s’ouvre :
On sélectionne « configuration de la surface d’exposition pour les fonctionnalités ».
Dans la liste de gauche on sélectionne « intégration du CLR » et on coche la case.
L’intégration du CLR est activée sur notre serveur SQL.
Développement
Maintenant on va créer notre projet Visual studio qui sera du type « Bibliothèque de classes ».
Pour pouvoir utiliser la fonctionnalité du CLR dans SQL il est important de faire les références suivantes :
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
Au sein de la classe on fait 2 méthodes comme suit :
La méthode « prixvente » prend 2 paramètres en entrée et retourne une valeur.
La méthode « UpdateListPrice » prend 2 paramètres et met à jour une table de la base de données.
namespace monclrNamespace
{
public class Class1
{
public static void prixvente(SqlMoney prixht, SqlInt32 tva, out SqlMoney prixvente)
{
// Validate the salesTaxPercent parameter.
if (tva < 0 || tva > 100)
{
throw new ArgumentException("Error: tva is out of range.");
}
// Calculate the sale price.
prixvente = prixht + (prixht * tva / 100.0m);
}
public static int UpdateListPrice(SqlInt32 productID, SqlMoney newListPrice)
{
// Validation du parametre newListPrice.
if (newListPrice < 0.0m)
{
throw new ArgumentException("Error: list price is negative.");
}
else if (newListPrice > 10000.0m)
{
throw new ArgumentException("Error: list price is too large.");
}
int rowsAffected = 0;
// Connect to the context connection.
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
// Declare a SQL string to update the list price for the specified product.
string sql =
"UPDATE Production.Product " +
"SET ListPrice = @ListPrice " +
"WHERE ProductID = @ProductID";
// Run the command.
using (SqlCommand cmd = new SqlCommand(sql, conn))
{ cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ListPrice", SqlDbType.Money).Value = newListPrice;
cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
rowsAffected = cmd.ExecuteNonQuery();
}
}
// Return the result to the client.
return rowsAffected;
}
}
}
L’important ici c’est que la dll générée par ce code sera exécutée au sein de SQL serveur donc la chaine de connexion se résume à :
SqlConnection conn = new SqlConnection("context connection=true")
On est déjà connecté donc on utilise la connexion contextuelle.
Déploiement
On va dans le SQL Management Studio de SQL serveur, on se connecte et on se place comme suit :
On fait un clic droit « Nouvel assembly » . Dans la fenêtre qui s’ouvre on va aller chercher notre dll, le propriétaire de la dll sera « dbo ».
Enfin pour pouvoir utiliser la dll ainsi intégrée on crée 2 procédures stockées en spécifiant le chemin jusqu’à la méthode en spécifiant « External Name ».
--création des procédures stockées qui vont lancer ma dll
--une pour chaque méthode de ma dll
CREATE PROCEDURE ProcStock_UpdateListPrice(@ProductID int, @NewListPrice money)
AS
EXTERNAL NAME monclr.[monclrNamespace.Class1].UpdateListPrice
GO
CREATE PROCEDURE ProcStock_prixvente(@ListPrice money, @SalesTaxPercent int, @SalePrice money output)
AS
EXTERNAL NAME monclr.[monclrNamespace.Class1].prixvente
GO
Utilisation
Il ne reste plus qu’à appeler les procédures stockées précédemment crées en leur passant les paramètres attendus.
--Utilisation des méthodes de ma dll
DECLARE @salePrice money
EXEC ProcStock_prixvente 200.00, 6, @salePrice output
PRINT 'List price: 200.00, Sales tax: 6%, Sale price: ' + CONVERT(nvarchar, @salePrice)
GO
EXEC ProcStock_UpdateListPrice 1, 9.99
SELECT ProductID, ListPrice FROM Production.Product WHERE ProductID = 1
GO
/* les résultats affichés en reponse aux procédures
Messages
List price: 200.00, Sales tax: 6%, Sale price: 212.00
(1 ligne(s) affectée(s))
Résultats
ProductId | ListPrice
1 9,99
*/