[SQL]使用CLR讀取網頁資料

2012-09-05

2235
0
SQL

[SQL]使用CLR讀取網頁資料

上完課練習一下

問題描述

希望使用SQL function從網站讀取網頁內容

解決方式

建立SQL CLR function讀取RSS網頁內容，這裡以點部落的RSS網址為例

http://www.dotblogs.com.tw/MainFeed.aspx?GroupID=701

步驟

1. 建立資料庫類型專案，framework記得選2.0

2. 加入新項目【SQL CLR C#】【SQL CLR C#使用者定義函式】

3. 輸入程式碼


using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString FetchFunction(string url)
    {        
        WebClient wc = new WebClient();
        char[] rcd = Encoding.UTF8.GetChars(wc.DownloadData(url));        
        return new SqlString(new string(rcd));
    }


}

4. 建置完畢後選擇發行

5. 記得先把資料庫名稱清除後再設定

6. 點選「發行」

7. 出現下列問題，原因應該為我的機器上有灌SQL server 2012的SQL Server Data Tools

8. 到編譯的資料夾查看，有下列的檔案

9. 用SQL語句如下來進行註冊：


-- 資料來源:德瑞克老師的上課筆記

USE RssDB
GO
-- 啟用 CLR 整合
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- 檢查是否已經啟用 CLR 整合
EXEC sp_configure 'clr enabled'
GO

-- 查詢 is_trustworthy_on 屬性
SELECT name N'資料庫'
	 , is_trustworthy_on N'是否被標示為可信任'
FROM
	sys.databases
WHERE
	name = 'RssDB'
GO

-- Database option TRUSTWORTHY needs to be ON for EXTERNAL_ACCESS
ALTER DATABASE RssDB
	SET TRUSTWORTHY ON
	WITH ROLLBACK IMMEDIATE
GO

-- 查詢 is_trustworthy_on 屬性是否開啟
SELECT name N'資料庫'
	 , is_trustworthy_on N'是否被標示為可信任'
FROM
	sys.databases
WHERE
	name = 'RssDB'
GO


-- 註冊ASSEMBLY(第二次跑的話要先把與ASSEMBLY相依的物件刪除)
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[uf_FetchHtmlContent]')
	 AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[uf_FetchHtmlContent]
GO

IF EXISTS
(SELECT name
 FROM
	 sys.assemblies
 WHERE
	 name = 'FetchHtmlContent') DROP ASSEMBLY [FetchHtmlContent]
GO

CREATE ASSEMBLY [FetchHtmlContent] 
FROM 'H:\開發區\2010Projects\FetchHtmlContent\FetchHtmlContent\bin\Debug\FetchHtmlContent.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

-- 查詢組件是否建立
SELECT *
FROM
	sys.assemblies;
GO

-- 建立function
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[uf_FetchHtmlContent]')
	 AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[uf_FetchHtmlContent]
GO
	 
CREATE FUNCTION [dbo].[uf_FetchHtmlContent]
	(@url [nvarchar](500))
	RETURNS [nvarchar](max) 
	WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [FetchHtmlContent].[UserDefinedFunctions].[FetchFunction]
GO

-- 測試函式
DECLARE @url nvarchar(500);
SET @url = N'http://www.dotblogs.com.tw/MainFeed.aspx?GroupID=701';
SELECT dbo.uf_FetchHtmlContent(@url) AS [網頁內容];

10. 完成查詢結果如下

注意事項

要查詢組件被那些物件使用可以參照下列畫面

參考資料

德瑞克老師的上課筆記

CREATE ASSEMBLY

回首頁