SQL 2008: Calling WCF service from procedure (SQLCLR)

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

Getting computers' name from AD in c#

I've been doing a job that add new some functionaries on my program.
One of them enable ours to run the following through SCDPM 2010.

• Run backup & restore (I've already posted about restore, click here)
• Create and Modify schedule.
• Getting a bakup list
• Install DPM agent

Today, I'd like to explain how to get computer's name from AD(active directory). 
When we try to add new server for protection into DPM, we should install agent first. We usually do that work through DPM console. the below picture is the first step while adding new protection server. As we can see, we have to choose servers what we want to.

When Adding DPM functionality to the program, It is effective way to convert powershell cmdlet to programming language, In my case, I use C#. All System Center products support powershell for improving manageability. 


I intended to implement to deploy DPM Agent on the desired host and I had to get computer's list from active directory as the first step. However, DPM doesn't provide any powershell cmdlet about this.


So I wrote c# code of it manually.



line 22, type the server name which installed domain controller.
line 27, narrow the target.(here, I only want to get computers)
line 28, add "Name" property to get computer's name only.


Below is web sites I had referenced.

http://www.dreamincode.net/code/snippet1847.htm
http://www.powershellpro.com/computernames-activedirectory/149/

Tip: Duplex Communication WCF and Silverlight

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

If you not add this on multi host, you encounter an error related crossdoamin in your Silverlight client. you may be confused to meet the error again despite have been added clientaccesspolicy.xml on the root domain.


Following Links are helpful in programming:
http://msdn.microsoft.com/en-us/library/cc645028(VS.95).aspx
http://weblogs.asp.net/dwahlin/archive/2008/06/16/pushing-data-to-a-silverlight-client-with-wcf-duplex-service-part-i.aspx
Message class:  http://msdn.microsoft.com/en-us/library/system.servicemodel.channels.message.aspx

Lamda Expression in C#

Lamda expression is an anonymous function that can contain expression and statement. Lamda expression can be used to create deletegates and tree types.

Lamda Operator

 "=>" : read as 'goes to'
" e.g. x => x * x " , which is read as x goes to x times x.


Lamda Expression
An expression lamda basic form:
" (input parameters) =>  expression "

If the lamda has one parameter, the parenthese is optional; Otherwise, If parameters are more than two, the parenthese is required.
" (x, y) => x == y "

The compliler sometimes doesn't understand input types. When this occur, specify the types explictly.
" (int x, string s) => s.Length > x "


Statement Lamda
basic form:
" (input parameters) => {statement;} "


The body of a statement lambda can consist of any number of statements; however, in practice there are typically no more than two or three.


Three examples are expression comparison between delegate and lamda. The first is an example for delegate and other thngs for lamda.

Example 1 ( deleage )
public delegate void IntDelegate(int a,int b);
static void Main(string[] args)
{
   IntDelegate d;
   d = ADD;
   d += SUB;
   d(10, 20);
}
public static void ADD(int a, int b)
{
   Console.WriteLine("ADD = {0}", a+b);
}
public static void SUB(int a, int b)
{
   Console.WriteLine("SUB = {0}", a - b);
}


Example 2 ( lamda in C# 2.0 )
public delegate void IntDelegate(int a,int b);
static void Main(string[] args)
{
    IntDelegate d;
    d = delegate(int a, int b) { Console.WriteLine("ADD = {0}", a + b); };
    d += delegate(int a, int b) { Console.WriteLine("SUB = {0}", a - b); };
    d(10, 20);
}
 

Example 3 ( lamda in C# 3.0)
public delegate void IntDelegate(int a, int b);   
static void Main(string[] args)
{
IntDelegate d;
d = (a, b) => { Console.WriteLine("ADD = {0}", a + b); };
d += (a, b) => { Console.WriteLine("SUB = {0}", a + b); };
d(10, 20);
}


This contribution is based on MSDN reference:
http://msdn.microsoft.com/en-us/library/bb397687.aspx