Geeks With Blogs
Path Notes of a Kodefu Master blog

I don’t approve of calling a WCF server from SQL Server, but there was a business requirement that had to be met. It concerned regulations regarding the safeguarding of certain data elements. Due to performance issues and the application’s infrastructure, calling the service from the application itself wasn’t an option.

Coding the CLR functions were the easy part. To do this, reference the Microsoft.SqlServer.Server and System.Data.SqlTypes namespaces (contained in System.Data.dll) and set the appropriate attributes on static methods contained within a static class. Also, it is probably best if you use the SqlTypes (SqlString, SqlDateTime, etc) for parameters and return values rather than rely on Sql Server to convert the values for you. Be sure to enable CLR on your SQL Server.

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public static class SqlFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)]
    public static SqlString HelloWorld()
    {
        return new SqlString("Hello World!");
    }

}

I wanted to avoid pulling too many assemblies into SQL Server, so I limited my references to the bare essentials for working with WCF and Sql Server: System, System.Data, System.Runtime.Serialization, System.ServiceModel, and System.XML.

Here’s the script to drop the assemblies if they exist and add them back (double slashes are intentional to allow copy/paste with formatting to work). Because of dependencies, it is necessary to add more assemblies than directly referenced by the project. If you created this within a database project, you can deploy by right-clicking the project. I have previously posted instructions on how to convert a class library to a database project.

IF  EXISTS (SELECT * FROM sys.assemblies asms 
WHERE asms.name = N'Microsoft.Transactions.Bridge') 
    DROP ASSEMBLY [Microsoft.Transactions.Bridge]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms 
WHERE asms.name = N'System.IdentityModel.Selectors') 
    DROP ASSEMBLY [System.IdentityModel.Selectors]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms 
WHERE asms.name = N'System.IdentityModel') 
    DROP ASSEMBLY [System.IdentityModel]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms 
WHERE asms.name = N'System.Messaging') 
    DROP ASSEMBLY [System.Messaging]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms 
WHERE asms.name = N'System.Web') 
    DROP ASSEMBLY [System.Web]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms 
WHERE asms.name = N'SMDiagnostics')
    DROP ASSEMBLY [SMDiagnostics]
GO

CREATE ASSEMBLY 
SMDiagnostics from
'C:\Windows\Microsoft.NET\Framework\v3.0\
\Windows Communication Foundation\SMDiagnostics.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY 
[System.Web] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\
\System.Web.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY 
[System.Messaging] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\
\System.Messaging.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY  
[System.IdentityModel] from
'C:\Program Files\Reference Assemblies\Microsoft\
\Framework\v3.0\System.IdentityModel.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY  
[System.IdentityModel.Selectors] from
'C:\Program Files\Reference Assemblies\Microsoft\
\Framework\v3.0\System.IdentityModel.Selectors.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY  
[Microsoft.Transactions.Bridge] from
'C:\Windows\Microsoft.NET\Framework\v3.0\
\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'
with permission_set = UNSAFE
GO

You will need to add your own assembly to the list in a similar manner as the .NET assemblies. Notice that they are marked UNSAFE. This is a necessary evil if you wish to use WCF with SQL Server. Because of this, if you have not done so you will need to reconfigure your database to allow it.

ALTER DATABASE KodefuGuru SET TRUSTWORTHY ON
reconfigure

The interesting part about this is that more assemblies than those specified are pulled in. When you remove the ones you specifically added, these are removed as well. Here is the list of assemblies:

Accessibility
Microsoft.Transactions.Bridge
SMDiagnostics
System.Configuration.Install
System.Design
System.DirectoryServices
System.DirectoryServices.Protocols
System.Drawing
System.Drawing.Design
System.EnterpriseServices
System.IdentityModel
System.IdentityModel.Selectors
System.Messaging
System.Runtime.Remoting
System.Runtime.Serialization
System.Runtime.Serialization.Formatters.Soap
System.ServiceModel
System.ServiceProcess
System.Web
System.Web.RegularExpressions
System.Windows.Forms

System.Windows.Forms? What?! Some of the dependencies are truly mind boggling.

This should be just enough to allow you to put your service calls inside of CLR functions. I attempted to be as lightweight as possible and avoided pulling in extra libraries, though you certainly could pull in a massive programmatic infrastructure with this.

I only agreed to this because the service call was directly related to the integrity of the data and further restrictions prevented the use of a separate physical tier to handle it. If you’re in the same situation, I hope this helps.

Note: Cross posted from KodefuGuru.
Permalink
Posted on Monday, July 13, 2009 5:16 PM | Back to top


Comments on this post: WCF from CLR Functions

# re: WCF from CLR Functions
Requesting Gravatar...
Humm... interesting,



Thanks for writing, most people don't bother.
Left by web development company on Aug 14, 2009 3:30 AM

# re: WCF from CLR Functions
Requesting Gravatar...
Some of the suggestions you have given have enabled me to apply my own thought process to afford a greater understanding of the issue. Some info that is provided on the Web is not very useful but yours has been worthwhile.
Left by казино интернет on Mar 10, 2011 3:33 AM

# re: WCF from CLR Functions
Requesting Gravatar...
great work it help me to work
Left by PSD TO XHTML on Apr 07, 2011 1:33 AM

# Team in Training
Requesting Gravatar...
Few words of appreciation get results where criticism and ridicule fail. You have given an honest and sincere appreciation and encouragement to this work. Share it with the whole world. Do it! I'm sure you'll enjoy it!
Left by Training on Sep 03, 2011 4:39 AM

# re: WCF from CLR Functions
Requesting Gravatar...
Dude, you rock the fckn' cazbah! thanks so much for this......
You saved me a sh!tload of time today.
Left by Moby Brown on Oct 10, 2012 3:54 AM

# re: WCF from CLR Functions
Requesting Gravatar...
It's like that it doesn't work under SQL Server 2012...
Left by Pavel on Jan 09, 2013 6:39 AM

Your comment:
 (will show your gravatar)


Copyright © Chris Eargle | Powered by: GeeksWithBlogs.net