SQL Server的CLR：如何调用WCF服务在CLR SQL存储过程在Visual Studio 2013数据库项目 [英] SQL Server CLR : how to call WCF Service In CLR SQL stored procedure in Visual Studio 2013 database project

问题描述

我的Visual Studio 2013

我创建了一个数据库项目。

我添加存储在CLR程序到该项目，我想打电话给从它的WCF服务。

谁能帮我？

我希望能够在不改变我的组装，类似于我可以做的web.config 与终端我的WCF客户端配置的部分。

解决方案

我发现这个有很多搜索和花太多时间在这之后，在VS 2014年

1. 创建数据库项目名为CLR_Test


2. 创建图书馆有关WCF客户端CLR_Service_Client



3. 添加 Serivce Refrence WCF服务，以CLR_Test，然后添加CLR_Service_Client的refrence为CLR_Test结果
   4.You必须更改数据库选件能够运行不安全assemblyes与下面的​​代码

   
   
   

   ALTER DATABASE SaleAutomation SET TRUSTWORTHY ON
   RECONFIGURE




4. 在CLR_Test项目属性在 SQLCLR 选项卡集权限级别到不安全（另一种方法是存在的发布项目从SQL Server管理改变其水平后的另一种方式是你的权限级别添加到发布，您可以使用他们每个人的剧本，结果
   ，而必须注意到，如果您从项目属性使用只有CLR_Test项目全自动创建不安全，你必须用其他的方式来设置CLR_Service_Client不安全）结果
   结果
   6.Run这个脚本添加sqlserver的能够运行WCF服务

CREATE ASSEMBLY $从
'C b $ b SMDiagnostics：\Windows\Microsoft.NET\Framework\v3.0\Windows通信Foundation\SMDiagnostics.dll '
与PERMISSION_SET =不安全
GO

  CREATE ASSEMBLY 
从[System.Web程序] 
'C：\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
与PERMISSION_SET =不安全
 GO 
 
创建ASSEMBLY 
 [System.Messaging]从
'C：\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
与PERMISSION_SET =不安全
 GO 
 
 CREATE ASSEMBLY 
 [System.IdentityModel]从
'C：\Program文件（x86）\Reference Assemblies\Microsoft\\ \\Framework\v3.0\System.IdentityModel.dll'
与PERMISSION_SET =不安全
 GO 
 
 CREATE ASSEMBLY 
从[System.IdentityModel.Selectors] 
'C：\Program文件（x86）\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'
与PERMISSION_SET =不安全
移动到
 
创建大会 - 这将增加服务模式
 [Microsoft.Transactions.Bridge]从
'C：\Windows\Microsoft.NET\Framework\\ \\v3.0\Windows通信Foundation\Microsoft.Transactions.Bridge.dll'
与PERMISSION_SET =不安全
 GO 
 
创建大会 - 这将增加服务模式
〔System.Runtime.Serialization]从
'C：\Windows\Microsoft.NET\Framework\v3.0\Windows通信Foundation\System.Runtime.Serialization.dll'
相PERMISSION_SET =不安全
 GO 
创建大会 - 这将增加服务模式
 [System.ServiceModel]从
'C：\Windows\Microsoft.NET \Framework\v3.0\Windows通信Foundation\System.ServiceModel.dll'
与PERMISSION_SET =不安全
 GO 
  

现在，您发布项目并运行存储过程和享受。

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.

解决方案

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.

这篇关于SQL Server的CLR：如何调用WCF服务在CLR SQL存储过程在Visual Studio 2013数据库项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！