In-Process Data Access in SQL Server 2005

• By Bob Beauchemin and Dan Sullivan
• Jun 16, 2006
• Sample Chapter is provided courtesy of Addison-Wesley Professional

This chapter is from the book
Developer's Guide to SQL Server 2005, A

Calling a Web Service from SQLCLR

You most likely would be using impersonation from within a procedure in an assembly that is catalogued as PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. This is because when accessing external Windows resources, you’ll need to impersonate the login that executed the procedure. One such case is calling a Web Service.

If you call a Web Service from a .NET Framework function or procedure by using the low-level APIs in System.Web, no special considerations other than impersonation apply. If you are using the Web Service client proxy classes generated by the .NET Framework WSDL.exe utility (or Add Web Reference from within Visual Studio 2005), however, you need to perform a few extra steps. The Web Service proxy classes generated will attempt to use dynamic assembly generation at procedure runtime to build and load the XmlSerializer classes that it uses to communicate with the Web Service. This is not allowed in SQLCLR, regardless of the PERMISSION_SET of the assembly. The way around this limitation is to build your XmlSerializer classes in advance, using a command-line utility, SGEN.exe. Here’s how you would accomplish this.

I’d like to call a Web Service named StockService and have a URL, http://stockbroker/StockService?WSDL, where I can obtain Web Service Description Language—metadata that is needed to build the client proxy. To build the proxy, issue the following command:

 WSDL.exe http://stockbroker/StockService?WSDL

Additional parameters available for the WSDL.exe utility are outside the scope of this discussion. Now that you have the proxy class (in the default case, it will be named StockService.cs), you compile it into an assembly and reference it in your program. This class consists of an object that represents your Web Service call, so calling out to the Web Service is no more complex than calling a method on an object. In your SQLCLR procedure, the code to call the Web Service looks like Listing 4-19.

Listing 4-19: Calling a Web Service from a SQLCLR user-defined function

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(Precision = 9, Scale = 2)]
public static decimal GetStockWS(string symbol)
{
Decimal price;
using (WindowsIdentity id = SqlContext.WindowsIdentity)
 {
  WindowsImpersonationContext c = id.Impersonate();
  StockService s = new StockService();
  // use the current credentials
  s.Credentials =    
   System.Net.CredentialCache.DefaultNetworkCredentials;
  price = s.GetStockPrice(symbol);
  c.Undo();
 }
return price;
}

For this code to work, you must pregenerate the required serializer for the proxy with the SGEN.exe utility, like this:

SGEN.exe StockService.dll

The utility produces an assembly named StockService.XmlSerializers.dll. You must catalog this assembly to SQL Server so that your Web Service client stored procedure (GetStockWS above) can use it. But there’s another SQLCLR reliability–related issue to address before you do this. Our overall build steps for the proxy so far look as shown in Figure 4-4.

Figure 4-4 Building a Web Service proxy and cataloguing it to SQL Server

Use the WSDL (1) utility to build a proxy from the URL for the Web Service. It will build a C# file whose name is based on the name of the service—in this case, StockService.cs. StockService.cs will include both a synchronous and asynchronous implementation of the proxy. You must remove the asynchronous one by removing it. You need keep only the constructor and the methods (2) and remove everything else (3). Build a library out of StockService.cs using the C# compiler (4). You must also build an assembly that contains the XML serialization code using the SGEN utility (5). This utility produces a DLL (6) that contains the XML serialization implementation.

The only problem with this is that when WSDL.exe produces an "automatic" proxy class, two types of invocation methods are produced: a method to execute the Web Service synchronously (GetStockPrice, in this case) and a pair of methods to execute the Web Service asynchronously (BeginGetStockPrice and EndGetStockPrice). The asynchronous methods must be edited out before compiling StockService.cs, or the resulting assembly will need to be catalogued with PERMISSION_SET = UNSAFE.³ If your DBA doesn’t mind PERMISSION_SET = UNSAFE, this is fine, but you won’t be using the methods, so let’s go in and remove them. In the generated code StockService.cs, remove or comment out the following (a subset of the four changes you have to make is shown in Figure 4-5):

Figure 4-5 Commenting out the asynchronous methods in a WSDL.exe–generated proxy

1. The BeginGetStockPrice and EndGetStockPrice methods
2. The GetStockPriceCompletedEventHandler variable
3. The GetStockPriceCompletedEventHandler method
4. The GetStockPriceCompletedEventHandlerEventArgs method

Now our proxy class assembly, StockService.dll, will be able to use PERMISSION_SET = SAFE. We’ll return to the subject of XmlSerializers in Chapter 5 and see how they are used by user-defined types.