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.

Answer 2

I found new way for my problem If you call your wcf service in service refrence as Web Refrence you must not have to do any thing

1. First add service refrence
   
2. Second add Service as web reference
3. All is Done and continue to call service with method

Answer 3

This is all moot since .Net v4.0, the System.ServiceModel is a mixed MSIL, meaning it using native code which will generate:

CREATE ASSEMBLY for assembly 'System.ServiceModel' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub. This would have been a wonderful elegant solution for what we wanted to do, but Microsoft again leaves us hanging on cliff