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

问题描述

我有 Visual Studio 2013.

I have Visual Studio 2013.

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

I created a database project.

我向该项目添加了一个 CLR 存储过程，我想从中调用 WCF 服务.

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

有人可以帮我吗?

我希望能够在不更改程序集的情况下更改服务地址，类似于我在 web.config 中使用 WCF 客户端配置的 endpoint 部分可以执行的操作.

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.

推荐答案

我在 VS 2014 中经过大量搜索并花了很多时间来解决这个问题

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

1. 创建数据库项目称为CLR_Test"
2. 为WCF客户端CLR_Service_Client"创建库

3. 将 wcf 服务的 Serivce Refrence 添加到CLR_Test"，然后将CLR_Service_Client"的引用添加到CLR_Test"
   4.您必须更改数据库选项才能使用以下代码运行不安全的程序集

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重新配置

在SQLCLR选项卡的CLR_Test"Project Properties中，将Permission level设置为Unsafe(另一种方法是，在发布项目后，您可以从 sql server 管理更改其级别，另一种方法是向发布脚本添加权限级别，您可以使用它们中的每一个，
但是你必须注意到，如果你使用来自project properties的只有CLR_Test"项目会自动创建Unsafe，你必须使用其他方式设置CLR_Service_Client"Unsafe )

6.运行此脚本添加Sqlserver可以运行wcf服务

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

1. 现在您发布项目并运行存储过程并享受.

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