Yeonki's Cloud: WCF

SQLCLR(Common Language Runtime) makes us use .NET framework in SQL Server. Today, I'm going to talk about this.

I was developing a module(DLL) which installed on a database server to connect WCF service on different hosts. It's a kind of invoker which means this module is starting point of a specific service.

After completed to write this, Users would call this module from their web pages or scripts. To do this, the module was registered as a assembly in the database and I also created a CLR stored procedure that points to my module.

A advantage of using CLR in SQL is that T-SQL itself doesn't provide the richer experienced programming environment in comparison with .net framework. We can't use arrays, collection, foreach statement and classes. By integrating CLR, makes it possible to have extensible features in stored procedure, trigger and user defined function.

My module uses .net framework 3.5 and SQL Server 2008 (sp1).
Originally, it built in .net 4.0. but 4.0 hasn't been supported SQL 2008.
So, I changed to 3.5.

Steps.
1. Go to the management studio of SQL Server, first makes sure we are CLR enabled.
user master
go
sp_configure 'clr enabled', 1
go
reconfigure
go

2. Set the database trushworthy
alter database [database name]
set trustworthy on;
go

3. Create assemblies. When I created relate assemblies, I got numerous errors. I' going to explain how I made them worked.

problem 1)
Msg 33009, Level 16, State 2, Procedure usp_xxxxx, Line 0
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YourDatabase’. You should correct this situation by resetting the owner of database ‘YourDatabase’ using the ALTER AUTHORIZATION statement.

=> I executed the below command.
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

problem 2)
Msg 10300, Level 16, State 2, Line 1
Assembly 'System.Web' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request

=> I changed 'system.web.dll' path to where based on 64bit. Some example on the web showed path as 'C:\Windows\Microsoft.NET\Framework\...'. When you're deploying your assemblies on 64bit, Its path has to be changed.

create assembly [System.Web]
from 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
with permission_set = unsafe

problem 3)
Msg 6522, Level 16, State 1, Procedure 'Procedure Name', Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'Procedure Name':

System.IO.FileLoadException: Could not load file or assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies.

Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

System.IO.FileLoadException:

=> I got this error just I executed my procedure (I'd done creation all assemblies). I dropped the assembly named 'System.ServiceModel'. This time, I created sub assemblies which have dependency with 'System.ServiceModel' again.

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

CREATE ASSEMBLY [System.ServiceModel]
from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'
with permission_set = unsafe
go

problem 4)

Msg 6522, Level 16, State 1, Procedure 'Procedure Name', Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'Procedure Name':

System.Security.SecurityException:

at: System.Diagnostics.EventLog.FindSourceRegistration(String source, String machineName, Boolean readOnly)

at: System.Diagnostics.EventLog.SourceExists(String source, String machineName)

at: System.Diagnostics.EventLog.VerifyAndCreateSource(String sourceName, String currentMachineName)

at: System.Diagnostics.EventLog.WriteEntry(String message, EventLogEntryType type, Int32 eventID, Int16 category, Byte[] rawData)

at: System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type, Int32 eventID, Int16 category, Byte[] rawData)

at: System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type, Int32 eventID)

=> Let's look at below .NET code. I added a try.. catch statement in regard to raise an error using EventLog.WriteEntry(...). But this isn't allowed to use in SQLCLR. Instead, I use SqlContext.Pipe.Sendf(...). So, I could get the actual error message in SQL management studio when my procedure did it wrong.

[My module]
using Microsoft.SqlServer.Server; // add this
......

[SqlProcedure] //add this
public static void DoService

{
......

try {
some coding...

} catch (Exception ex) {
// Change to SqlContext.Pipe.send
SqlContext.Pipe.Send(serviceName + " [DoService]" + ex.Message);

//EventLog.WriteEntry(serviceName, "[DoService]" + ex.Message, EventLogEntryType.Warning, 1000);
}
}

After change and execute the procedure, I realized that 'System.Drawing' registered wrong in SQL server. I fixed it with the following command.

alter assembly [System.Drawing]
from 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll'
with permission_set = unsafe

Sum up

all created assemblies and their paths are the following.
1) System.Runtime.Serialization
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'

2) SMDiagnostics
'c:\Windows\Microsoft.net\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'

3) System.Web
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'

4) System.Messaging
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Messaging.dll'

5) System.IdentityModel
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'

6) System.IdentityModel.Selectors
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'

7) Microsoft.Transactions.Bridge
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'

8) System.ServiceModel
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'

9) System.Drawing
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll'

Finally, Don't forget that SQLCLR's data types are different to T-SQL.
For example, we should use NVARCHAR in SQLCRL instead of varchar as parameter for SQLCLR procedure.

Reference sites:
1) http://nielsb.wordpress.com/sqlclrwcf/
2) http://sql-server-stuff.blogspot.com/2008/11/changing-db-owner.html
3) http://msdn.microsoft.com/en-us/library/ms131092.aspx

When I Find the solution which enables duplex communication beween a server (WCF service) and a client (Silverlight 3.0), I've noticed that it is possible by using PollingDuplexHttpBiding.

My WCF service is that collect the server's information and send it to the client. The client has a functionality of visualizing the infomation on the dashboard. Consierations of this solution are as the follow:

The Server can push data to the client at any time.
The Client can invoke methods of the server.

WCF service provides some duplex communications. If your WCF service connect to any client other than Silverlight, you could think alternatives like NetTCPDuplex or wsHttpDuplexBinding.
PollingDuplexHpptBinding has been introduced since Silverlight 2 Beta. ( I've promgrammed in Silverlight 3.)

The Silverlight SDK states the following about how communication works between a Silverlight client and a duplex service:

"The Silverlight client periodically polls the service on the network layer, and checks for any new messages that the service wants to send on the callback channel. The service queues all messages sent on the client callback channel and delivers them to the client when the client polls the service."

PollingDulexHttpBinding's remkarable features is that communicate with sliverlight client through WCF Message type.

1. You should convert your data or class into Message type
public void send (object data)
{
   Message msg = Message.CreateMessage(MessageVersion.Soap11, "Silverlight/IService/Receive", data);

//Method that send data to the client
}

2. It keeps reference of the client on the server.

3. Place crossdomain.xml, clientaccesspolicy.xml on your root domain in IIS server.
Please add a binding on IIS server if you want to run WCF service on multi-host environment which have same IP but different domain name.

e.g.
Suporse there are some hosts run on the same IIS.
Host 1: name: http://www.testserver1.com/  123.123.123.111 80 port
Host 2: name: http://www.testserver2.com/  123.123.123.111 80 port
Host 3: name: http://www.alltest.com/      123.123.123.111 80 port
You want to add WCF service on the port no. 40005