Using Webservices and Xml Serialization in CLR Integration

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='https://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.

Comments

• Anonymous
  July 25, 2005
  Would you recommend calling Web Services from SQL Server 2005 as a programming pattern? For example, if we call this as part of a transaction, it would be disastrous, right? I think it would be useful if you could blog about how someone can reliably call web services from the database through the CLR, since I think this is a feature that can be easily misused.

• Anonymous
  July 25, 2005
  I was also reading about the CLR integration and wanted to know if there is a way to see the number of AppDomains being created. The documentation and several web-casts say that 1 AppDomain is created per identity in a database, but more will be created "as needed". Can you exemplify this through a blog?

• Anonymous
  July 25, 2005
  Comment to Srinivas: There is one AD created per database and owner of an assembly. So, if we have two assemblies created in the db, and the two assemblies are owned by different users, and we are executing methods from both assemblies, then there will be two AD's in the db. SQL then can (supposedly) create more AD's if necessary. I have however never seen that happen. To view what AD's you have you can use the system view: sys.dm_clr_appdomains

• Anonymous
  July 27, 2005
  Is not it better if we call webservice from middle tier. Why need to call from SQL???
  
  What do you think???
  
  Cheers,
  Himadrish
  
  
  
  

• Anonymous
  July 27, 2005
  Niels >> Thanks for the information.
  
  Himadrish >> Precisely the same question that I also asked. Am not sure if there are many use cases that will require a web-service call from a database.

• Anonymous
  November 22, 2005
  The comment has been removed

• Anonymous
  December 09, 2005
  The comment has been removed

• Anonymous
  December 15, 2005
  If you receive "Cannot load dynamically generated serialization assembly", you are likely still using new XmlSerializer(typeof(MyClass)). I believe you must either link to the generated assembly and use new MyClassSerializer(), or use XmlSerializerAssemblyAttribute. The latter approach doesn't appear to work with SQL Server. I hope I am wrong, because this is becoming quite inconvenient.
  
  Hope this helps.

• Anonymous
  January 05, 2006
  I discovered the reason for the exception, "Cannot load dynamically generated serialization assembly," while trying to serialize a class with the following code:
  
  XmlSerializer serializer = new XmlSerializer(typeof(MyClass));
  
  It turns out the class I was trying to serialize had a method called "Serialize". When I renamed the method to something else, such as "MySerialize", the exception went away and the SQL-CLR used the assembly I had pre-generated using sgen.
  
  I'm not sure why a Serialize method would confuse the class loader, except that the class contained in the generated serialization assembly also has a Serialize method, and the CLR uses reflection to invoke the method. This behavior, however, is specific to the SQL-CRL implementation, because the same situation in a Console app does not produce the same result. There the sgen'd serialization assembly is always used if it is present, even if the class has its own Serialize method.

• Anonymous
  January 13, 2006
  Actually, I am wrong. XmlSerializer will locate the assembly generated by sgen without using the specialized serializer.

• Anonymous
  January 22, 2006
  The comment has been removed

• Anonymous
  January 22, 2006
  Hmmmm, I thought I might solve my own problem. As it turns out, all you've got to do to elliminate the "That assembly does not allow partially trusted callers" error when Strongly-typing your assemblies in SQL Server 2005, is add the following to your Assembly.cs file:
  
  [assembly: System.Security.AllowPartiallyTrustedCallers]
  
  duh.

• Anonymous
  March 01, 2006
  I've followed your instructions successfully, but I'm having problems debugging.  It seems as though when VS does a build, it creates the XmlSerializers.dll, but if you try to debug, it first does a deployment without creating the serialization dll. I get this...
  Executing deploy script: PostDeployScript.sql ...
  Error: executing deploy script failed. CREATE ASSEMBLY failed because it could not open the physical file 'C:...binSQLFAX.XmlSerializers.dll': 2(The system cannot find the file specified.).: PostDeployScript.sql
  
  Can you tel me how to debug a clr stored proc that calls a webservice?
  
  Joel
  

• Anonymous
  March 22, 2006
  I'm trying to reproduce the case but i always get the same error message ""Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer." I'm using (succesfully)the following sql code :
  CREATE ASSEMBLY WriteToLogStd
  FROM 'C:devSQLSERVER2005TestLibraryTestLibrarybinDebugTestLibrary.dll'
  WITH permission_set = EXTERNAL_ACCESS ;
  go
  
  CREATE ASSEMBLY WriteToLogXmlSerializers
  FROM 'C:devSQLSERVER2005TestLibraryTestLibrarybinDebugTestLibrary.XmlSerializers.dll'
  WITH permission_set = SAFE;
  go
  
  CREATE PROCEDURE GetTestLibrary
  (
  @fromDB NVarchar(100),
  @fromFacade NVarchar(200) OUTPUT
  )
  AS EXTERNAL NAME WriteToLogStd.[TestLibrary.LogFacade].WriteToLog;
  go
  
  I'm using (succesfully)the following postbuild event:"C:Program FilesMicrosoft Visual Studio 8SDKv2.0Binsgen.exe" /force /compiler:/keyfile:c:key.snk /t:TestLibrary.LogFacade $(TargetDir)$(TargetName).dll
  Where's my error?
  Can you help me?
  Best regards
  MaxAmbrogi.com

• Anonymous
  April 30, 2006
  The comment has been removed

• Anonymous
  May 10, 2006
  Thanks, this is exactly the information I needed.
  
  Being able to consume data from a WebService and return it as a TBV is very useful.

• Anonymous
  May 19, 2006
  i am going to doing a messanger for intranet application so what is the role of webservices and xml in that

• Anonymous
  August 17, 2006
  The comment has been removed

• Anonymous
  October 18, 2006
  Simple question: what security permission level is required to allow dynamic loading of assemblies? Trick

• Anonymous
  January 12, 2007
  I've done a lot of things from the SQLCLR thus far, except for consuming an ASP.NET Web Service. This

• Anonymous
  July 16, 2007
  在SQLSERVER2005中调用WebService 原文：NicoJacobs 出处：U2Unv/sahttp://www.u2u.net/Article.aspx?ART=Web...

• Anonymous
  September 14, 2007
  how to study in college by walter pauk

• Anonymous
  September 25, 2007
  The comment has been removed

• Anonymous
  November 20, 2007
  Today I'll demonstrate a very nice feature that has been added recently in Microsoft SQL Server 2005...

• Anonymous
  May 30, 2008
  In yesterday’s posting the BDC was used to present data from both Project Server and the AdventureWorks