SQL Server CLR : how to call WCF Service In CLR SQL stored procedure in database project

Question

I have Visual Studio 2013.

I created a database project.

I added a CLR stored procedure to that project, and I want to call a WCF service from it.

Can anyone help me?

I want be able to change service address without changing my assembly, similar to what I can do in web.config with the endpoint section of my WCF client config.

Answer 1

I found this after a lot of searching and spend very much time on this in VS 2014

1. Create Database Project Called "CLR_Test"
2. Create Library For WCF Client "CLR_Service_Client"

3. Add Serivce Refrence of wcf service to "CLR_Test" then add refrence of "CLR_Service_Client" into "CLR_Test"
   4.You must change DB Option to able run unsafe assemblyes with the below code

   ALTER DATABASE SaleAutomation SET TRUSTWORTHY ON RECONFIGURE

4. In the "CLR_Test" Project Properties in the SQLCLR tab set Permission level to Unsafe (another way is exist that after publish project you change its level from sql server management and another way is you add permission level to script of publish you can use each of them,
   but you must noticed that if you use from project properties only "CLR_Test" project automaticly create Unsafe and you must use other ways to set "CLR_Service_Client" Unsafe )
   
   6.Run this Scripts to add Sqlserver be able to run wcf service

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\Framework64\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 (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'
with permission_set = UNSAFE
GO

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

CREATE ASSEMBLY -- this will add service modal
[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 -- this will add service modal
[System.Runtime.Serialization] from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY -- this will add service modal
[System.ServiceModel] from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.ServiceModel.dll'
with permission_set = UNSAFE
GO

7. now you publish your project and run stored procedure and enjoy.