Last time we saw an example of using a powerful functionality, Regular Expressions, from a supported .NET Framework library and how you can leverage it in your database applications.

Today we will talk about another such functionality that is enabled by CLR Integration. CLR Integration allows you to consume results from webservice calls. As you might already know, there are three permission buckets in CLR Integration – SAFE, EXTERNAL ACCESS and UNSAFE. Calling webservices is a typical EXTERNAL ACCESS scenario and requires you to register your assembly in the EXTERNAL ACCESS permission bucket. 

Xml Serialization

Calling webservice methods requires serializing all the types being used to xml. Outside SQL Server, this serialization code is generated at runtime, compiled and loaded in the application. However, for security and reliability reasons, SQL Server does not allow you to dynamically load assemblies. Hence, to make this scenario work, you need to pre-generate the serialization assembly and register it in the database. Visual Studio 2005 beta2 allows you to generate the serialization assembly from your project. You need to turn the Generate serialization assembly option on in project properties à Build. You can also generate the serialization assembly using a tool called ‘sgen’ that is shipped with the .NET Framework SDK as follows:

>sgen.exe myAsm.dll

Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.

Once you have these two assemblies available. You need to register them in SQL Server as follows:

CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’

with permission_set = EXTERNAL ACCESS

CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’

with permission_set = SAFE

Visual Studio 2005 allows you to register the first assembly in SQL Server using the deploy feature in SQL Server projects. However, it does not support deploying the serialization assembly and you would need to manually register it. If you are using Visual Studio on the same machine as your SQL Server, you can use the pre / post deploy script feature in Visual Studio to automate this in your project. This can be done as follows:

1. Add the following to a file called predeployscript.sql:

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'myAsmXML')

DROP ASSEMBLY myAsmXML with NO DEPENDENTS;

2. Add the following to a file called postdeployscript.sql:

CREATE ASSEMBLY myAsmXML from '<path>\myAsm.XmlSerializers.dll'

3. Add these two files to your project by right clicking your project in Solution Explorer and Add à Existing Item. 

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run these scripts before and after the deployment of your project. However since the scripts are run on the SQL Server, the path must be relative to the machine on which you have SQL Server. If SQL Server and VS are on separate machines then you must make sure the path is a network path and accessible from SQL Server machine.

Webservice consumption is an example of where xml serialization is required. You might require xml serialization for other purposes as well. Following are two examples where you might require xml serialization:

  1. Converting a User Defined Type (UDT) to XML in SQL Server
  2. Serializing / Deserializing an object to xml in your CLR code. Suppose you are getting an xml blob from somewhere and you would like to deserialize it into the appropriate object.

Both of these cases also require you to generate the appropriate serializer assembly and add it to the database.

Generating Webservice Proxy Classes

Before calling webservice methods in your code, you need to generate the proxy classes for the webservice you are calling. Visual Studio 2005 does this automatically when you add a webreference to your SQL Server project.  However, the auto generated proxy classes by Visual Studio 2005 beta2 contains synchronization code that prevents you from using it in EXTERNAL ACCESS due to reliability restrictions in SQL Server 2005. To make it work in EXTERNAL ACCESS you need to manually generate the proxy classes using wsdl which is another tool shipped with the .NET Framework SDK in the oldAsync mode.  You can do this as following:

>wsdl /par:par.txt /n:<namespace> <url>

Where par.txt is a parameters file passed to wsdl that specifies various options. An e.g. of parameters file that you can use to specify the oldAsync option is as follows:

<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>

Once you generate the proxy class using wsdl you can add it to your SQL Server Project in Visual Studio 2005 beta2 by right clicking your project in Solutions Explorer and Add à Existing Item. If you do this, make sure you do not add a webreference to this url in your project.  Visual Studio 2005 RTM will autogenerate the proxy classes in oldAsync mode for SQL Server projects and you would not require to manually generate them as above.

Summary

Let me summarize all the steps that are required to call webservice methods in SQL Server CLR Integration using Visual Studio 2005 beta2 SQL Server project:

  1. Generate the proxy classes using wsdl in oldAsync mode. Add the generated class to your project. 
  2. Develop your code.
  3. Generate the serialization assembly using Visual Studio build option or using sgen.
  4. Deploy your project as External Access.
  5. Register the serialization assembly in the database by adding a predeployscript and a postdeployscript in your VS project or using CREATE ASSEMBLY directly from SQL Server Management Studio.

Note: Step 1 would not be required in Visual Studio 2005 RTM. You would be able to add a web reference to your project and that would automatically generate SQL Server External Access compliant proxy classes.

- Vineet Rao

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.