U2U Article: "Consuming WebServices in SQL
Server 2005"Copyright © 2004 by U2U nv/sa, Belgium. All rights reserved.
Please address any questions or suggestions to U2U.
Nico Jacobs
U2U nv/sa
Sql Server 2005 offers the functionality of
writing functions, stored procedures, aggregates, triggers and types in .NET. In
this way, we can ‘wrap’ .NET functionality in a database function, procedure or
type. In an earlier article, we demonstrated this principle by wrapping regular
expression functionality in a CLR function. In this article, we illustrate the
same principle, but this time by consuming a web service. We will build a
function in the AdventureWorks demo database, which converts an amount in Euros
to an amount in US Dollars, fetching the conversion ratio from a web service.
For this, we will use the webservice hosted at http://www.webservicex.net/CurrencyConvertor.asmx.
We want to point out that we at U2U did not create nor maintain this
webservice, and as such cannot guarantee the availability nor the correctness
of this service; it is just used for illustration purpose.
This code is
based on Visual Studio 2005 Beta 2 and SQL Server 2005 CTP (build June 2005,
IDW 15). In the final release, some of the special operations which are needed
to add the webservices in the beta releases, should be solved.
This article
does not describe all steps in great detail, it assumes from the reader already
a basic knowledge of creating CLR functions for SQL Server 2005.
The first step in this process consists of
creating a C# or VB.NET Sql Server project, which is one of the installed
templates in Visual Studio 2005 (notice that the beta releases of both products
are still a bit picky on the installation order: you need to install Sql Server
2005 before you install Visual Studio 2005 in order for the templates to show
up in Visual Studio).
Unfortunately, if we would continue working
in the same way as we are used to in Visual Studio for making regular web
service client, we will fail in using them in SQL Server 2005. The reason is
that SQL Server 2005 does not allow CLR code to load dynamically generated
assemblies, which is exactly what the traditional webservices are doing. The
solution to that is make sure these assemblies are created at compile time, and
load the additional assemblies manually. Yet another problem is that the proxy
class generated by Visual Studio performs operations which are not allowed by
SQL Server. This latter problem will probably be solved by the final release,
but in the mean time we have to work around this problem as well, by generating
the proxy class with an setting generating old asynchronous code, as explained
in a post from Vineet Rao on blogs.msdn.com.
So first, we generate the proxy file for our webservice with
the wsdl.exe tool in C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. We
have to convince this tool however, to generate code using the ‘old’
asynchronous way (without the <MethodName>Async methods). Only proxy
classes generated that way can be loaded with external access privileges. If we
would use the ‘new’ proxy classes, we have to use unsafe as security level,
which can better be avoided. We can do this by using a configuration file.
First create a configuration file containing the following xml:
<wsdlParameters xmlns='http://microsoft.com/webReference/'>
<language>c#</language>
<protocol>Soap</protocol>
<nologo>true</nologo>
<sharetypes>false</sharetypes>
<webReferenceOptions>
<codeGenerationOptions>properties
oldAsync</codeGenerationOptions>
</webReferenceOptions>
</wsdlParameters>
Save this file as c:\oldwsdlconfig.xml (change the language
element to VB if you are creating a VB.NET project: <language>vb</language>). Now, call
wsdl.exe to generate the proxy class:
C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\Bin\wsdl.exe /par:c:\ oldwsdlconfig.xml http://www.webservicex.net/CurrencyConvertor.asmx
This will generate a CurrencyConvertor.cs
(or .vb) file. We will now add this file to our SQL Server project in Visual Studio by right clicking our project, selecting Add -> Existing items, and then
browse to the location where the CurrencyConvertor.cs (or .vb) file was
created. We will see an error if we build our code this way: the generated
proxy class uses the webservice dll, which is not yet referenced, so right
click the project in the Solution Explorer, choose add reference, and select
System.Web.Services from the list.
Next, we have to avoid that our code will
do runtime assembly creation for the serialization and deserialization of the
objects which travel across this webservice. In order to do that, we right
click in our Solution Explorer on the project and select Properties. The exact
setting is slightly different in VB.NET and C#.
C#
Select the Build tab, and at the bottom
select for Generate serialization assembly the value On
VB.NET
Select the Compile tab, click Advanced
Compile Options… and select at the bottom of the dialog in the Generate
serialization assemblies combobox the value on.
While we are in the project property
window, we can set the permission level property as well: select the database
tab, and set the value for permission level to external. In this way, we mark
this assembly as an assembly which is ‘safe’ in the sence that it will not do
dangerous thread manipulations, platform invokes, …, and hence it is not marked
as unsafe. But it is less safe than an assembly marked ‘safe’, because it makes
calls over the network, or reads/writes to the hard disk.
Finally, we can start the main programming
step: writing our conversion function. This function takes as input a sql money
type, and returns something of Sql money type as well: the result of
multiplying the input parameter with the conversion rate of Euro versus US
Dollars. To add this code, right click the project in the solution explorer,
select Add -> User-Defined Function, and choose a database in which we want
to deploy this CLR function (e.g. AdventureWorks). Alter the code so that it
becomes as follows:
C#
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney EUR2USD(SqlMoney
amount)
{
CurrencyConvertor cc = new
CurrencyConvertor();
return new
SqlMoney(cc.ConversionRate(Currency.EUR,Currency.USD)
* amount.ToDouble());
}
};
VB.NET
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function EUR2USD(ByVal amount
As SqlMoney) As SqlMoney
Dim cc As New CurrencyConvertor()
Return New SqlMoney(amount.ToDouble() * _
cc.ConversionRate(Currency.EUR,
Currency.USD))
End Function
End Class
Next, we can build and deploy this project.
We can now try out our code by starting the
SQL Server 2005 Management Studio. If we browse with our Object Explorer to the
database in which we deployed our code (e.g. Adventureworks), we see in the
programmability folder, under assemblies, that one assembly is loaded, and that
one scalar function is created:
However, if we try to use our function, we
run into a problem. Open a Query window by right clicking the in the object
explorer the database in which the function is deployed, and select New Query.
In that query window, type the following T-SQL statement:
SELECT dbo.EUR2USD(1)
When we execute this statement, instead of
obtaining the conversion rate of euros to US dollars, we get an error message:
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during
execution of user defined routine or aggregate 'EUR2USD':
System.InvalidOperationException: Cannot load
dynamically generated serialization assembly. In some hosting environments
assembly load functionality is restricted, consider using pre-generated
serializer. Please see inner exception for more information. --->
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and
LoadModule() have been disabled by the host.
The reason for this is a missing dll. When
we look at the bin directory of our Visual Studio project, we see that two dlls
have been created: WebServiceConsume.dll and WebServiceConsume.XmlSerializers.dll.
This latter dll is needed to do the Xml serialization and deserialization on
the webservice calls, but this dll is not loaded automatically in the SQL
Server database. Hence, we have to load this dll manually. To do so, right
click the assemblies folder in the object explorer of the SQL management
studio, and select new assembly…
Next, specify the path to the WebServiceConsume.XmlSerializers.dll
file after clicking the Browse button. You can keep the permission set on Safe,
this assembly will not make any external calls. After you click OK, we can
refresh our assemblies folder, and we see the new assembly appearing.
If we now try our T-SQL query with our
EUR2USD function again, we see that it succeeds: the conversion web service can
now be called directly from within the database by wrapping it in a CLR
function.
Web services and efficiency
One last word of caution: be careful when
using such webservice calls. Since these calls make network calls, they are
slow. So be very careful if you apply these functions on large tables. To
illustrate this, run this query to get all the prices in euros and US dollars (504 rows, AdventureWorks database):
select StandardCost, dbo.EUR2USD(StandardCost)
from Production.Product
This query took 4 minutes and 44 seconds to
complete, because 504 calls where made to the webservice. However, we can
rewrite the query as follows:
declare @rate float
select @rate = dbo.EUR2USD(1)
select StandardCost, StandardCost * @rate from
AdventureWorks.Production.Product
Now, we only make one single call to the
web service, and store the conversion rate in a local variable. Unless the
result of the webservice call changes during the execution of our function,
both batches return the same result, only the latter batch returns in less than
one second, more than 300 times as fast as our first query.
So be careful when using web services: if
the result of a single web service call is used frequently, try to cache this
result in a SQL variable or temporary table, in order to avoid unnecessary web
service calls. Also notice that this caching cannot happen in the CLR function,
since assemblies which are loaded into SQL Server, are not allowed to store
static or shared state. The only exception to this is when you mark your
assembly as unsafe.
Dr. Nico Jacobs
is trainer and consultant at U2U
(www.u2u.net). His focus is on Microsoft SQL Server and ADO.NET. You can reach Nico at nico@u2u.be.