Accessing an Web Service from a SQL Server CLR Assembly (Procedure or Function)
Sometimes we found some SQL Server procedures doing a lot of work, processing a lot of data, with a lot of business code inside.
And sometimes we have different systems in different languages and platforms and the only point they have in common is the database.
For these cases, some day you will need to access a Web Service from a central endpoint that all the systems share in common.
I had a huge trouble trying to use WCF, because to generate a client do WCF (on .net platform) you need to add some references to you project.
When you are working with a CLR Project, the references that can be used on your project (AFAIK) are only the DLLs that are currently on your DB.
Ok, let´s go to the interesting stuff...
First you have created your CLR project (take care of creating it using .Net Framework 2.0 if you database currently just support this version, in my case I used 3.5 just because the runtime is the same):
Inside my project I need to add the reference to my service, and I do this going on "Add Service Reference..." context menu on project...
Then you will have the default screen to add a service reference, but to access old-style Web Services we need to go on "Advanced..." option.
And then go to "Add Web Reference..." option
Then we enter our service URL (.asmx or ?wsdl usually) and add the reference to the project...
On screen below I've just instantiated a service client, called a method of him and returned the value for my SQL.
Follows the code...
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlFunction(Name = "fn_DocClient_Get")]
public static SqlBytes DocClient_Get(SqlString id, SqlString name, SqlString source, SqlString storageServer)
{
AspNetService asp = new AspNetService();
AspNetDocumentInfo di = asp.GetAll(id.ToString(), name.ToString(), source.ToString(), storageServer.ToString());
return new SqlBytes(di.FileOperation[0].Files[0].Stream);
}
};
Pay attention that I defined my SQL Selver function name in the follow attribute:
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlFunction(Name = "fn_DocClient_Get")]
public static SqlBytes DocClient_Get(SqlString id, SqlString name, SqlString source, SqlString storageServer)
{
Ok, now we gon on and deploy to our server (was selected on at project creation) using the easiest way...
**
**Or going to deploy it manually (I prefer to see what's going on under the hood...
1. We get the output assembly, and we need to extract their bytes..
For doing this I made myself a little utility I called HexIt.exe.
You just drop a file and it returns a string of the HEX representation of file's bytes.
**Unfortunelly I can't attach this utility file here.. (and now I am not able to access a 4Shared like services from my office here)
But I am writing down the main code, then you could make yours...
**
public partial class frmHexIt : Form
{
public frmHexIt()
{
InitializeComponent();
}
private void lblDropBox_DragEnter(object sender, DragEventArgs e)
{
DataObject o = (DataObject) e.Data;
if (o.ContainsFileDropList()) e.Effect = DragDropEffects.All;
}
private void lblDropBox_DragDrop(object sender, DragEventArgs e)
{
DataObject o = (DataObject)e.Data;
if (o.GetFileDropList().Count != 1)
MessageBox.Show("Drop one file at time!");
else
{
List<byte> bytes = File.ReadAllBytes(o.GetFileDropList()[0]).ToList();
StringBuilder sb = new StringBuilder(bytes.Count);
bytes.ForEach(b => sb.AppendFormat("{0:X2}",b));
txtHex.Text = string.Concat("0x", sb.ToString());
}
}
private void txtHex_KeyDown(object sender, KeyEventArgs e)
{
if(e.Control && e.KeyCode == Keys.A)
txtHex.SelectAll();
}
}
- Copy the extracted bytes and register your assembly on database
PS: I Know I can reference directly my file on disk, but we had some problems on old versions of SQL Server that sometimes a SQL Server assembly was not found cause SQL Server removed it from "memory" and could not find the file to put it back again... something like this... I don't remeber exactly.
CREATE ASSEMBLY CLRAccessingWS
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000...
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
- We need to write a SQL Server function pointing to our assembly method.
CREATE FUNCTION [dbo].[fn_DocClient_Get]
(@id NVARCHAR (4000), @name NVARCHAR (4000), @source NVARCHAR (4000), @storageServer NVARCHAR (4000))
RETURNS VARBINARY (MAX)
AS
EXTERNAL NAME [CLRAccessingWS].[StoredProcedures].[DocClient_Get]
But unfortunelly you may got the error below when calling you function...
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_DocClient_Get":
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.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAs
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GetSerializersFromCache(XmlMapping[] mappings, Type type)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at CLRAccessingWS.mtzsvmsrvdv.AspNetService..ctor()
...
Then, let's go to the simple and fast solution...
There is a microsoft utility called XML Serializer Generator Tool (Sgen.exe) (for reference: http://msdn.microsoft.com/en-us/library/bk3w6240%28v=vs.80%29.aspx)
A little bit about it...
*"The XML Serializer Generator creates an XML serialization assembly for types in a specified assembly in order to improve the startup performance of a XmlSerializer when it serializes or deserializes objects of the specified types."
"When the XML Serializer Generator is not used, a XmlSerializer generates serialization code and a serialization assembly for each type every time an application is run. To improve the performance of XML serialization startup, use the Sgen.exe tool to generate those assemblies the assemblies in advance. These assemblies can then be deployed with the application.*
The XML Serializer Generator can also improve the performance of clients that use XML Web service proxies to communicate with servers because the serialization process will not incur a performance hit when the type is loaded the first time.
These generated assemblies cannot be used on the server side of a Web service. This tool is only for Web service clients and manual serialization scenarios.
If the assembly containing the type to serialize is named MyType.dll, then the associated serialization assembly will be named MyType.XmlSerializers.dll."
Let's use it on our output assembly BEFORE put it on DATABASE
1. Lets open a Visual Studio Command Prompt (Be sure to choice the right version of SGEN.EXE compatible with your assembly. If your assembly and database are using .Net Framework 2.0 use this version of SGEN! Is you use for instance a .Net framework 4.0 version of SGEN, your new assembly will be generated using this framework)
**
PS: SGEN.exe is usually found at: C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin**
2. Call SGEN.exe passing you assembly as parameter.
3. Verify that a new "satellite" assembly was created on the side, called ASSEMBLYNAME.SERIALIZERS.dll
Lets try our new assembly at database
**
- Cleanup old stuff**
drop function [dbo].[fn_DocClient_Get]
GO
drop assembly CLRAccessingWS
GO
2. Hex It both assemblies... And Register them at database! Create you SQL Server function back!
CREATE ASSEMBLY CLRAccessingWS
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000004000000000000000...
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE ASSEMBLY [CLRAccessingWS.XmlSerializers]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000004000000000000000...
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION [dbo].[fn_DocClient_Get]
(@id NVARCHAR (4000), @name NVARCHAR (4000), @source NVARCHAR (4000), @storageServer NVARCHAR (4000))
RETURNS VARBINARY (MAX)
AS
EXTERNAL NAME [CLRAccessingWS].[StoredProcedures].[DocClient_Get]
And then it works like a charm for me now!
**
Any question, let me know!
=]**