C#开发CLR存储过程,并调用WCF。
1、默认情况下,SQL中没有WCF的运行环境,所以要引入相关的程序集。脚本如下:
/* copy %SystemRoot%\Microsoft.net\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll to %ProgramFiles%\Reference Assemblies\Microsoft\Framework\v3.0\Microsoft.Transactions.Bridge.dll */ ------------------------------------------------------------------------------ use dsctest -- Turn advanced options on EXEC sp_configure 'show advanced options' , '1'; go reconfigure with override; go EXEC sp_configure 'clr enabled' , '1' go reconfigure with override; -- Turn advanced options back off EXEC sp_configure 'show advanced options' , '0'; Go -------------------------------------------------------------------- ALTER DATABASE dsctest SET TRUSTWORTHY ON; reconfigure with override; GO -------------------------------------------------------------------- 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\Framework\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.ServiceModel] from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' with permission_set = UNSAFE GO ------------------------------------- /* 问题: 如果调用存储过程时在machine.config line xxx报异常:ConfigurationErrorsException那么就把报错的这行删除。 另外,根据资料显示用关闭WCF调试的方法也可以解决这个问题,我没有尝试成功。用直接删除的方法成功了。 卸载顺序 dbo.QueryPseudoCode StoreProc System.ServiceModel System.Web System.Messageing SMDiagnostics (refresh) */
2、编写存储过程
这,其实就是一个普通的类库项目。
public class WCF_Query { public static readonly IServiceTest proxy = ChannelFactory<IServiceTest>.CreateChannel (new BasicHttpBinding(), new EndpointAddress(new Uri("http://localhost:3368/ServiceTest.svc"))); [SqlProcedure] public static void QueryPseudoCode(string code) { string result = ""; result = proxy.GetData(code); SqlMetaData col1 = new SqlMetaData("code", System.Data.SqlDbType.VarChar, 255); SqlDataRecord dr = new SqlDataRecord(col1); dr.SetString(0, result); SqlContext.Pipe.Send(dr); } }
注意:
1)、无法使用配置文件来进行服务的配置。所以这里使用ChannelFactory硬编码实际。是否采取可以读取INI文件的方式来实现参数的可配置我没有实验,有需要的可以试一下。
2)、此处的Binding类型要与服务端的匹配。
3、如何部署上一步编译出来的类库
当然,在执行以下脚本前要先把DLL文件放到服务器上的某个路径下。
USE [DscTest] GO --------------------------------------------------- CREATE ASSEMBLY StoreProc from 'D:\山东\山东联通\存储过程\StoreProc.dll' with permission_set = UNSAFE GO -------------------------------------------------------------- CREATE PROCEDURE [dbo].[QueryPseudoCode] ( @code nvarchar(255) ) WITH EXECUTE AS CALLER AS EXTERNAL NAME [StoreProc].[StoreProc.WCF_Query].[QueryPseudoCode] GO /* 卸载顺序 dbo.QueryPseudoCode StoreProc */
4、在调用存储过程进行测试前,还要有一个WCF,以下是一个Demo:
using System; using System.Collections.Generic; using System.Linq; using System.Runtime.Serialization; using System.ServiceModel; using System.ServiceModel.Web; using System.Text; using IService; namespace WcfServiceTest { public class ServiceTest : IServiceTest { public string GetData(string value) { return string.Format("You entered: {0}", value); } } }
5、好了,服务运行起来,现在就可以在SQL中调用了。
exec [QueryPseudoCode] '你好,人渣'
结果如下:
参考资料:http://nielsb.wordpress.com/sqlclrwcf/ (可能需要翻*墙才能打开,英文的)