記事一覧

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)まずはクラスライブラリプロジェクトを作成します。
 プロジェクト名にドットは使用しないほうが無難でしょう。どうしても使用したい場合は、後述のSSMSでアセンブリ登録する際の名前でアンダースコアに置き換えて登録しましょう。

sqlserver_sqlcle_01.png


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

sqlserver_sqlcle_02.png


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

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で設定

SQL Server Management Studio にて以下のスクリプトを流します。
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 に関する記事をご紹介しておりますのでご参考頂ければ幸いです。


関連記事

コメント

コメントの投稿

カテゴリ別記事一覧

広告

プロフィール

石河 純


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

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語・データベース】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
Oracle10g SQLServer2008R2 SQLAnywhere8/11/16
ActiveReport CrystalReport ReportNet(IBM)
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS IIS6/7 apache2

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