Using Web Services in SQL Server CLR assemblies

SQL Server 2005 introduced a nifty new feature called CLR Integration, which essentially allows you to write triggers, procedures and functions in managed code. So why would you want to do this? To answer that question, I’ll quote from the MSDN documentation:

Managed code is better suited than Transact-SQL for calculations and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Library, you have access to thousands of pre-built classes and routines. These can be easily accessed from any stored procedure, trigger or user defined function.

Ok, so how does one go about using this great functionality? For security reasons, CLR integration is not enabled by default. To enable it, run the following SQL statements on your database:

sp_configure ‘clr enabled’, 1
GO
reconfigure
GO

If your code is going to require external access, like using a Web Service, you will also need to execute this SQL:

ALTER DATABASE MyDb SET TRUSTWORTHY ON
GO

Be sure to check with your database administrator first, as this may introduce an unwanted security risk. See the MSDN documentation for more information.

So how do we write code for SQL Server? Visual Studio 2005 introduced a new project template called ‘SQL Server Project’ for this purpose. In Visual Studio 2005/2008 you click File->New->Project and then select ‘Database’ under the Visual C# node in the Project types tree. In the Templates area on the right, select ‘SQL Server Project’:

In Visual Studio 2010 you follow the same procedure, but the New Project dialog has changed: under Installed Templates on the left, click Database, then SQL Server and finally select ‘Visual C# SQL CLR Database Project’:

Click Ok and you should get a Database Reference dialog where you can enter the details of your SQL Server database. Once you’ve completed that you may get a dialog asking if you’d like to enable debugging – just click yes and you should be presented with a your new project. Before we start coding, we’re going to need a reference to a Web Service so lets go add that now. For this example we’ll use a currency converter from www.webservicex.net. Right click the ‘References’ node in Solution Explorer and select ‘Add Web Reference’ (VS 2005) or ‘Add Service Reference’ (VS 2008/2010). Type the following into the URL combo box: http://www.webservicex.net/CurrencyConvertor.asmx and click ‘Go’. After a short while the service description should appear at which point you can type in the name you’d like to use for the service, or just accept the default. I called mine ‘CurrencyService’. Note that the service has only one method, ConversionRate, which when given 2 currency codes will return the conversion rate between them. Click the ‘Add Reference’ button to finish.

If you are using VS 2008/2010 and are unfamiliar with the new ‘Add Service’ dialog, simply click the ‘Advanced’ button at the bottom of the dialog and then click ‘Add Web Reference’ – this will display the old dialog shown above.

Notice in your Solution Explorer tree that there are no code files, so let’s go add one. Right click the project node, select ‘Add’ and you should get a context menu listing the types of items available:

For this example I’m selecting ‘User-Defined Function’ and calling it ‘ConvertCurrency’. When you click Ok you will be presented with a .cs file defining a partial class called ‘UserDefinedFunctions’ which contains a single method called ‘ConvertCurrency’. Take note of the method attribute which indicates that this is a SQL CLR function, and the default return type, SqlString (defined in System.Data.SqlTypes).

The service exposes a class, ‘CurrencyConverter’, which has the ‘ConversionRate’ method, and an enum called ‘Currency’. The ConversionRate method takes in 2 Currency enum values and returns the conversion rate between the currencies. We’ll have to convert our string parameters to Currency enum values, instantiate the service, pass the enums into the method call, and multiply our amount parameter by the rate returned from the method. Here is the code (remember to add your own error-handling):

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlServerProject1.CurrencyService;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDouble ConvertCurrency(SqlDouble amount, SqlString fromCurrency, SqlString toCurrency)
{
string from = fromCurrency.Value.ToUpper();
string to = toCurrency.Value.ToUpper();
Currency f = (Currency)Enum.Parse(typeof(Currency), from);
Currency t = (Currency)Enum.Parse(typeof(Currency), to);
CurrencyConvertor converter = new CurrencyConvertor();
double rate = converter.ConversionRate(f, t);
return new SqlDouble(amount.Value * rate);
}
};

Ok, with any luck, our code compiles. The next step is to get the dll deployed to SQL Server. Fortunately, Visual Studio make this trivial – simply right click the project in Solution Explorer and click ‘Deploy’. Open your database in SQL Server Management Studio and expand the ‘Programmability’ node, then ‘Functions’ and finally ‘Scalar-valued Functions’:

…and there’s our function. Now expand the ‘Assemblies’ node and you should see the project dll:

To test the function, execute the following SQL:

select dbo.ConvertCurrency(100, ‘USD’, ‘ZAR’)

…and we get this osbscure error:

What this is saying is that SQL Server requires a special dll called a ’serialization assembly’, which will enable it to perform the serialization of objects that is necessary for web services. Once again, Visual Studio comes to the rescue. Right-click the project in Solution Explorer and select Properties. At the very bottom of the ‘Build’ tab is a dropdown labeled ‘Generate serialization assembly’. Switch this to ‘On’ and rebuild the project. If you now browse to the ‘bin\debug’ folder of the project you will see a new dll called ‘SqlServerProject1.XmlSerializers.dll’. Unfortunately, the ‘Deploy’ command will not register this dll with SQL Server, so we have to do that ourselves. Deploy the regular dll as normal, then copy the serialization dll to your SQL Server machine and execute this SQL (change the path in the FROM clause to match the location of the dll):

CREATE ASSEMBLY [SqlServerProject1.XmlSerializers]
FROM ‘C:\SqlServerProject1.XmlSerializers.dll’
WITH PERMISSION_SET=SAFE
GO

Execute the previous SQL to test the function again, and we now get a SecurityException (shortened for brevity):

This is because SQL Server’s security model does not allow external access by default. To enable external access, open the project properties in Visual Studio again, and this time go to the ‘Database’ tab. Set the ‘Permission Level’ dropdown to ‘External’ and rebuild the project. Deploy the dll’s as before (don’t forget the serialization assembly), execute the SQL to test the function, and finally success! At the time of writing this article, the conversion rate between US Dollars and South African Rands was 7.7485, giving me a result of 774.85 which is to say: $100 = R774.85