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/   （可能需要翻*墙才能打开，英文的）