fc2ブログ

記事一覧

SQLCLRを実装する手順 | SQL Server


今回は SQL Server で SQLCLR を実装する手順とその実行方法について覚え書きします。
SQLCLRは SQL Server 2005 から搭載された機能で、VBやC#のコードをストアドプロシージャやスカラー関数として呼び出すことができる大変便利で、さらには .NETのソースと互換性のある機能です。
しかしながら、最近再度使用してみましたところ、少しつまづきましたのでここに覚え書きしたいと思います。
 


前提条件
・Windows 7 professional 以降 / Windows Server 2008 R2 以降
・SQL Server 2008 Developer / Standard / Enterprise 以降
・Visual Studio 2008 Professional 以降



1.VisualStudioの設定

(1)まずはクラスライブラリプロジェクトを作成します。
 プロジェクト名にドットは使用しないほうが無難でしょう。どうしても使用したい場合は、後述の SQL Server Management Studio (以下 SSMS と呼ぶ)でアセンブリ登録する際の名前でアンダースコアに置き換えて登録しましょう。

sqlserver_sqlcle_01.png


(2)ルート名前空間は空欄にします。

sqlserver_sqlcle_02.png


(3)VBでソースを記述します。SQLServer の属性を必ずつけてください。呼び出しポイントは Shared (C# の場合は static )で記述します。呼び出しポイントのクラスではプライベート変数は使用できません。

sqlserver_sqlcle_03.png


FileUtility.vb
Imports System.IO
Imports System.Text
Imports System.Data.SqlTypes

Public Class FileUtility

    'このように静的変数を作成してはいけません
    'Private Shared _test As String = String.Empty

    <Microsoft.SqlServer.Server.SqlProcedure>
    Public Shared Sub DeleteFile(ByVal filePath As SqlString)

        Try
            File.Delete(filePath)
        Catch ex As Exception
        End Try

    End Sub


    <Microsoft.SqlServer.Server.SqlFunction>
    Public Shared Function ReadToEnd(ByVal filePath As SqlString,
                                     ByVal encode As SqlString) As SqlString

        Dim ret As String = String.Empty

        Try
            Using sr As New StreamReader(filePath,
                                Encoding.GetEncoding(encode.ToString()))

                ret = sr.ReadToEnd()
            End Using

        Catch ex As Exception
        End Try

        Return New SqlString(ret)

    End Function

End Class

引数や戻り値に使用する型は SQLServer ネイティブ互換型を使用すると良いです。
例えば NVARCHAR を返す関数を作成したい場合は VB で SqlString 型を返すようにします。

※SQLServer ネイティブ互換型についての参考URL
http://www.atmarkit.co.jp/fdotnet/special/sqlclr02/sqlclr02_01.html



2.SSMSで設定

SSMS にて以下のスクリプトを流します。
DB 名や VB で作成した DLL のファイルパスや関数名などは適宜置き換えてください。
権限のデフォルトは SAFE です。今回はファイル操作を行う為、EXTERNAL_ACCESS に設定します。

USE testDB
GO

--EXTERNAL_ACCESSで必要
ALTER DATABASE testDB SET TRUSTWORTHY ON
GO

--.NET Framework でのユーザー コードの実行を有効にする
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

--SQLCLRで使用している全ての関数を削除します。
DROP PROCEDURE IF EXISTS usp_DeleteFile;
DROP FUNCTION IF EXISTS fnc_ReadToEnd;
--アセンブリを削除します。
DROP ASSEMBLY IF EXISTS SQLCLR;

--DBへアセンブリの登録をします
CREATE ASSEMBLY SQLCLR
FROM 'D:\SQLCLR\SQLCLR\bin\Debug\SQLCLR.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS --SAFE/EXTERNAL_ACCESS/UNSAFE
GO

--アセンブリ内のプロシージャまたは関数を呼び出せるように定義します。
CREATE PROCEDURE usp_DeleteFile(
@filePath NVARCHAR(MAX)
)
AS EXTERNAL NAME SQLCLR.FileUtility.DeleteFile
--EXTERNAL NAME = プロジェクト名.クラス名.関数名
GO

CREATE FUNCTION fnc_ReadToEnd(
@filePath NVARCHAR(MAX)
,@encode NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SQLCLR.FileUtility.ReadToEnd
--EXTERNAL NAME = プロジェクト名.クラス名.関数名
GO


sqlserver_sqlcle_04.png



3.使用方法

他のストアドプロシージャまたは VB などのプログラムから呼び出します。
通常のプロシージャまたは関数と何ら変わりなく使用が可能です。

USE testDB
GO

SELECT dbo.fnc_ReadToEnd('D:\test.txt','Shift_JIS');

EXEC dbo.usp_DeleteFile N'D:\test.txt';




4.ポイントまとめ

.NET側
・C# で SQL Server プロジェクトを作成したほうが簡単。
・VB で作成する場合はクラスライブラリプロジェクトで作成する。
・プログラムは Shared で記述する。
・Private 変数は使用しない。(最初に Call するクラスに対して)
・ルート名前空間は設定しない。
・プロシージャを作成する場合は SqlProcedure 属性が必要。
・スカラー関数を作成する場合は SqlFunction 属性が必要。
・戻り値や引数に SQLServer ネイティブ互換型を使用する。
・vbc.exe や csc.exe でのコンパイルは必要なし。
・sqlaccess.dll の参照設定も必要なし。

SQL Server側
・プロジェクト名にドット(.)が付いている場合は EXTERNAL_NAME でハイフンに置き換えて設定する。
・ファイル関連の操作など、アセンブリ自体に権限が必要な場合があります。
・実行するには DB 自体に設定変更が必要。
・アセンブリを登録しないと使用できない。



最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。


関連記事

コメント

コメントの投稿

※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。

 入力されていないコメントには返信しませんのであらかじめご了承くださいませ。

※ニックネームでも良いので必ずご入力ください。

    

※必ずご入力ください。

    
    

※必ずご入力ください。

※技術的な質問には環境やエラーについて正確かつ詳細にお教えください。

・正確なエラーの内容

・Windowsのバージョン番号

・Visual Studioのバージョン

・機器の型番

・アプリやソフトのバージョン

    

カテゴリ別記事一覧

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Catalina 10.15 / Mojave 10.14 / High Sierra 10.13 / Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
【データベース】
Oracle 10g/9i
SQLServer 2016/2008R2/2005/2000
SQLAnywhere 16/11/8
【BI/レポートツール】
Cognos ReportNet (IBM)
Microsoft PowerBI
ActiveReport (GrapeCity)
CrystalReport
【OCX関連】
GrapeCity InputMan SPREAD MultiRow GridView
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS jQuery IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。