ITブログ時々なんでもブログ

１．VisualStudioの設定

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

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

３．使用方法

USE testDB
GO

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

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