Calling WCF services from within Sql Server 2008 (SQLCLR)

SQLCLR(Common Language Runtime) makes us use .NET framework in SQL   Server.

I am developing a component which installed on a database server to connect WCF service on different hosts.
     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.

     USE custDB
     EXEC sp_configure 'clr enabled', '1';
     RECONFIGURE;


2. Set the database trushworthy
ALTER DATABASE custdb SET TRUSTWORTHY ON;


3. Create below listed assemblies.

-- Adding System.Web assembly

CREATE ASSEMBLY [System.Web]
FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
WITH PERMISSION_SET = UNSAFE

-- Adding required assemblieds for the WCF service.
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

4.  Register our SQL CLR component


IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyStoredProcedures')
BEGIN
     DROP ASSEMBLY [MyStoredProcedures]
ALTER ASSEMBLY MyStoredProcedures
FROM '{our SQLCLR project.dll path}'
WITH PERMISSION_SET = UNSAFE ;
END
ELSE
BEGIN
CREATE ASSEMBLY MyStoredProcedures
FROM '{our SQLCLR project.dll path}'
WITH PERMISSION_SET = UNSAFE;
END

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyXmlSerializers')
BEGIN
DROP ASSEMBLY [MyXmlSerializers]
END


CREATE ASSEMBLY MyXmlSerializers
FROM{our SQLCLR project.XmlSerializers.dll path}'
WITH PERMISSION_SET = SAFE;

Once our assemblies are created we have to create Procedure/Function in the below way.


CREATE PROCEDURE WeatherSP
    @zipCode nvarchar(10)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;
GO

CREATE Function TestSP(@name nvarchar(10)) returns nvarchar(150)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.TestSP;




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.

For more info