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 onEXEC sp_configure 'show advanced options' , '1';goreconfigure with override;goEXEC sp_configure 'clr enabled' , '1'goreconfigure with override;-- Turn advanced options back offEXEC sp_configure 'show advanced options' , '0';Go--------------------------------------------------------------------ALTER DATABASE dsctest SET TRUSTWORTHY ON;reconfigure with override;GO--------------------------------------------------------------------CREATE ASSEMBLYSMDiagnostics from'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll'with permission_set = UNSAFEGOCREATE ASSEMBLY[System.Web] from'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'with permission_set = UNSAFEGOCREATE ASSEMBLY[System.Messaging] from'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'with permission_set = UNSAFEGO CREATE ASSEMBLY[System.ServiceModel] from'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'with permission_set = UNSAFEGO -------------------------------------/*問題:如果調用預存程序時在machine.config line xxx報異常:ConfigurationErrorsException那麼就把報錯的這行刪除。另外,根據資料顯示用關閉WCF調試的方法也可以解決這個問題,我沒有嘗試成功。用直接刪除的方法成功了。卸載順序dbo.QueryPseudoCodeStoreProcSystem.ServiceModelSystem.WebSystem.MessageingSMDiagnostics(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 ASSEMBLYStoreProc from'D:\山東\山東聯通\預存程序\StoreProc.dll'with permission_set = UNSAFEGO--------------------------------------------------------------CREATE PROCEDURE [dbo].[QueryPseudoCode](@code nvarchar(255))WITH EXECUTE AS CALLERASEXTERNAL NAME [StoreProc].[StoreProc.WCF_Query].[QueryPseudoCode]GO/*卸載順序dbo.QueryPseudoCodeStoreProc*/
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/ (可能需要翻*牆才能開啟,英文的)