SQL Serverのアセンブリとは、Microsoft .NET Frameworkで記述されたクラスライブラリや共通言語ランタイム (CLR) のアセンブリをSQL Serverのインスタンスに登録することで、T-SQLからCLR言語 (C#やVB.NETなど) で書かれたストアドプロシージャやユーザー定義関数を作成できるようにする機能です。

2.C#でDLLを作成

C#でHttpリクエストによってHTML情報を取得するDLLを作成するため以下のコードでプロジェクトを作成する

Microsoft Visual Studio Community 2019を起動して新規プロジェクトを作成するクラスライブラリを選択(※.NET Framework 4.7.2)

using System.Data.SqlTypes;
using System.IO;
using System.Net;

namespace SQLCLR
{
    public class SQLCLRHttpWebRequest
    {

        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString HttpGet(SqlString url, SqlString contentType, int time)
        {
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;

            var r = (HttpWebRequest)WebRequest.Create(url.Value);
            r.Method = @"GET";
            r.Timeout = time;
            r.ContentType = contentType.Value;

            var wrep = r.GetResponse();
            var stream = wrep.GetResponseStream();
            StreamReader reader = new StreamReader(stream, System.Text.Encoding.UTF8);
            var status = ((int)((HttpWebResponse)wrep).StatusCode).ToString();
            var str = reader.ReadToEnd();
            stream.Close();
            reader.Close();
            return new SqlString(str);
        }
    }
}

以下のような例外メッセージが表示されるので.NET Framework で TLS1.1 および 1.2 を有効化して回避した。

.NET Framework エラーが発生しました: System.Net.WebException: 要求は中止されました: SSL/TLS のセキュリティで保護されているチャネルを作成できませんでした System.Net.WebException: 場所 System.Net.HttpWebRequest.GetResponse()

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;

3.SQL ServerへのDLL登録

SQL ServerでCLRアセンブリを使用するため、CLRを有効にします。 ※今回は[Sample]というDBを作成 ※SQL Server 2022 Express Edition

USE [Sample]
GO

--SQL Server Management StudioでCLRを有効にする方法

EXEC sp_configure 'clr enabled';        --設定値表示
EXEC sp_configure 'clr enabled' , '1';  --設定の変更
RECONFIGURE;
EXEC sp_configure 'clr enabled';        --設定値表示

今回はアセンブリがデータベース内でHTTPにて外部リソースにアクセスするためSET TRUSTWORTHY ONを設定

USE [Sample]
GO

SELECT name, is_trustworthy_on
FROM sys.databases
WHERE name = 'Sample';
ALTER DATABASE [Sample] SET TRUSTWORTHY ON;
SELECT name, is_trustworthy_on
FROM sys.databases
WHERE name = 'Sample';

CREATE ASSEMBLYにてアセンブリを作成し、 SQL Serverで実行できる.NET Frameworkのコードのセキュリティレベル（SAFE、EXTERNAL_ACCESS、UNSAFE）で制限の少ないセキュリティレベルのUNSAFEを指定して.NET Frameworkコードに許可される範囲を変更しています。 ※UNSAFEコードの使用は、信頼できるソースなど注意が必要です。

USE [Sample]
GO

--CREATE ASSEMBLY [アセンブリ名]
--FROM 'アセンブリのファイルパス'
--WITH PERMISSION_SET = [権限セット];

CREATE ASSEMBLY SQLCLRTest  
FROM 'C:\Work\SQLCLR\SQLCLR.dll'  
WITH PERMISSION_SET = UNSAFE;

;"> アセンブリの登録情報の一覧を取得

SELECT * FROM sys.assemblies;

4.SQL ServerからDLL呼出

SQL Serverでアセンブリを呼出す関数を作成

USE [Sample]
GO

--アセンブリを呼出す関数を作成
CREATE FUNCTION HttpGet(
    @Url NVARCHAR(MAX),
    @ContentType NVARCHAR(MAX),
    @Time int
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SQLCLRTest.[SQLCLR.SQLCLRHttpWebRequest].HttpGet;
GO

関数を呼出しを行って確認する。

USE [Sample]
GO

--作成した関数の呼出

declare @Url varchar(max);
declare @ContentType varchar(max);
declare @TimeOut int;

set @Url = N'https://任意のドメイン/'; --Url
set @ContentType = N'text/html; charset=UTF-8'; --ContentType
set @TimeOut = 100000; --HTTP呼出しタイムアウト

SELECT dbo.HttpGet(@Url,@ContentType ,@TimeOut);

リクエスト可能なURLを指定して実施結果を確認する

感想

WebスクレイピングをSQLの関数を利用して実行してそのままDB格納できそう。

用語：共通言語ランタイム (CLR) :Microsoft .NET Framework のコードの実行環境

本コンテンツはプロモーションが含まれます。

目次

1.SQL Serverのアセンブリについて

2.C#でDLLを作成

3.SQL ServerへのDLL登録

4.SQL ServerからDLL呼出

感想