Folosirea web service-ului
din SQL Server 2005
Pentru a putea folosi web service-ul dintr-o baza de date
SQL Server vom utiliza componenta CLR, introdusa
odata cu versiunea 2005, prin intermediul careia se va crea in
Visual Studio o UDF (User Defined Function)
care v-a consuma un serviciu web si pe care va fi folosita
intr-o Table Valued Function in baza de date SQL
pentru a returna rezultatul sub forma de tabela sql. Suna
complicat dar in realitate nu e.
Partea I – partea de Visual Studio
Se incepe prin crearea unui nou proiect in Visual Studio
2005 de tipul Database \ SQL Server Project. Numele
acestuia va fi CLRWebService
Inainte de a crea functia UDF va trebui sa avem o referinta
adaugata la web service-ul dorit. Pentru a face asta se da right click
pe
numele solutiei si din meniu se alege Add web reference.
In fereastra care apare, in campul URL se adauga adresa: http://www.infovalutar.ro/curs.asmx?wsdl
iar fereastra ar trebui sa arate cam asa:
Se pot observa in partea stanga toate metodele oferite de
serviciul web precum si parametrii pe cere ii necesita.
Il felicit pe autorul acestui webservice, il ofera in mod
gratuit, este actualizat “just in time” si ofera
cam toat ce ar fi nevoie pentru a avea cursul valutar in
orice aplicatie. Great work.
Inapoi la treaba noasta, se pune Web reference name infovalutar
si daca totul arata precum in imagine se apasa butonul Add Reference
Se poate observa in Solution explorer din Visual Studio ca
referinta a fosta adaugata
Urmatorul pas consta in crearea obiectului UDF. Pentru a
face acest lucru, adaugam un nou item la solutie,
Dupa cum am precizat va fi de tipul User-Defined Function si
va avea numele GetCursuriValutare. Scopul acestei
functii este de a apela metoda getall(data) a web
service-ului pentru a returna cotatiile de curs valuar pentru data
transmisa ca si parametru.
Sa incepem in sfarsit sa scrie si ceva cod. Trebuie sa
analizam exact ce vrem de la functie.
-
trebuie sa fie read-only: facem doar o simpla citire de date
-
v-a fi populata folosing o metoda adaugata de noi GetCursuriValutare_FillRow
-
v-a returna o tabela care v-a contine 2 campuri: IDCurrency si Value
Incepem prin a defini atributele functiei folosing SQLFunction
[SqlFunction(
DataAccess = DataAccessKind.Read,
FillRowMethodName = "GetCursuriValutare_FillRow",
TableDefinition =
"IDCurrency
NVARCHAR(10), " +
"Value
float "
)
]
Metoda care v-a returna rezultatul final
public static IEnumerable GetCursuriValutare()
{
DateTime dt
= DateTime.Now.AddDays(-1);
return new Curs().getall(dt).Rows;
}
Aici avem grija si de parametrul de tip DateTime. Acasta
metoda returneaza un obiect
de tipul IEnumerable
din web service-ul Curs continut de namespace-ul infovalutar.
Metoda GetCursuriValutare_FillRow populeaza record setul UDF
din obiectul Curs
returnat din IEnumerable
public static void
GetCursuriValutare_FillRow(
object
CursObj,
out SqlString IDCurrency,
out SqlDouble Value
)
{
DataRow r =
(DataRow)CursObj;
IDCurrency = new
SqlString(r[0].ToString());
Value = new SqlDouble(Convert.ToDouble(r[1]));
}
Rezultatul final ar trebui sa arate cam asa:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using CLRWebService.infovalutar;
public partial class UserDefinedFunctions
{
[SqlFunction(
DataAccess = DataAccessKind.Read,
FillRowMethodName = "GetCursuriValutare_FillRow",
TableDefinition =
"IDCurrency
NVARCHAR(10), " +
"Value float
"
)
]
public static IEnumerable
GetCursuriValutare()
{
DateTime dt
= DateTime.Now.AddDays(-1);
return new Curs().getall(dt).Rows;
}
public static void
GetCursuriValutare_FillRow(
object
CursObj,
out SqlString IDCurrency,
out SqlDouble Value
)
{
DataRow r =
(DataRow)CursObj;
IDCurrency = new
SqlString(r[0].ToString());
Value = new SqlDouble(Convert.ToDouble(r[1]));
}
};
Deoarece am reusit sa finalizam functia acum trebuie sa
obtinem si dll-urile necesare pentru
a o putea folosi direct din serverul SQL. Un lucru foarte
important de retinut este faptul ca
obiectele XML nu sunt serializate automat de aceea v-a
trebui sa facem ceva pentru a obtine
XMLSerialization.dll pe langa dll-ul de baza al functiei.
Acest lucru se face folosing utilitarul sgen
inclus in Visual Studio. Pentru a nu tot face acest lucru
manual o sa automatizam putin, folosin
evenimentele de Build ale solutie.
Right click pe numele solutiei -> Properties ->
Build events iar in command line
pentru Post-build event adaugam:
"C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"
Se alege din meniul Build optinea Build CLRWebService si ne
rugam sa nu avem nici o eroare.
Daca totul a decurs normal in directorul proiectului trebuie
sa fie 3 fisiere generate:
Se pare ca procesul de dezvoltare al functie noastre UDF s-a
finalizat. Mai ramane partea
a doua, integrarea in baza de date SQL pentru a o putea folosi.
Partea II – partea de SQL Server
Pentru a usura treaba pe C:\ se creeaza un director
CLRGetCurs in care se copiaza 2 din cele 3 fisiere generate:
CLRWebService.dll si CLRWebService.XmlSerializers.dll
Se deschide SQL Server Management Studio si se face conectarea
la instanta SQL Server dorita.
Pentru exemplificare voi folosi baza de date AdventureWorks. Deschidem
un nou
Query in care scriem urmatoarele:
use AdventureWorks
go
-- permite crearea accesului extern
ALTER DATABASE
AdventureWorks SET TRUSTWORTHY
ON;
GO
--verificam sa nu existe obiectele pe
care vrem sa le creem
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetCursuriValutareWS')
DROP FUNCTION GetProductWS
go
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'XmlSerializers')
DROP ASSEMBLY [XmlSerializers]
IF EXISTS (SELECT name FROM sys.assemblies WHERE
name = 'GetCusturiValutareCLR')
DROP ASSEMBLY GetProductCLR
GO
-- creem assembly-uri cu cele 2 dll-uri
din directorul CLRGetCurs
Create ASSEMBLY
GetCusturiValutareCLR FROM 'C:\CLRGetCurs\CLRWebService.dll'
WITH PERMISSION_SET = External_Access
CREATE ASSEMBLY
[XmlSerializers] from
'C:\CLRGetCurs\CLRWebService.XmlSerializers.dll'
WITH permission_set = SAFE
GO
Daca totul a mers bine suntem la un pas de a consuma webservice-ul
direct din SQL,
Mai trebuie sa facem o functie tabelara care sa foloseasca
functia UDF
CREATE FUNCTION
GetCursuriValutareWS()
RETURNS TABLE (
IDCurrency NVARCHAR(10),
Value FLOAT
)
AS EXTERNAL NAME GetCusturiValutareCLR.UserDefinedFunctions.[GetCursuriValutare]
GO
Si acum, sa culegem rezultatele stradaniei noastre:
SELECT
* FROM
GetCursuriValutareWS()
.Tutorial creat de alin from http://alinberce.wordpress.com