[SQL]FILESTREAM/VARBINARY(MAX)の使い方

D&DしたPDFファイルをSQL Serverに登録＋プレビューする方法の記事の一部になります。

#2. SQL Server:FileStream管理の設定とその実装
リンク先を参考にしていますが、リンク先はSQL Serever 2014で、自分の環境はSQL Serever 2012で、若干違いがあるかもしれません。

##1) BLOB (Binary Large Object ) と FILESTREAM
ファイルを保存するには、二通りの方法があります。

• FILESTREAM を使って、varbinary(max) オブジェクトをファイルシステムに保存する方法
• BLOB を通常の varbinary(max) データとして、テーブルに保存する方法

保存するファイルのサイズが 1 MB より大きく、高速な読み込みが必要な場合は FILESTREAM の利用が推奨されているようです。

##2) FILESTREAMを使ってファイルを保存する方法

###FILESTREAMを有効化する
SQL Server構成マネージャーで、有効化したいデータベースエンジンのインスタンスを右クリックしプロパティを表示します。

FILESTREAMタブで、FILESTREAMを有効化する。

データベースエンジンのインスタンスを再起動します。

###FILESTREAMのアクセスレベルを設定する
SQL Server Managment Studioのクエリーエディタを開き以下のクエリーを実行します。

EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;

設定可能なアクセスレベル:

0: FILESTREAM サポートを無効
1: Transact-SQL アクセスに対して FILESTREAM を有効
2: Transact-SQL アクセスおよび Win32 ストリーム アクセスに対して FILESTREAM を有効

SQL Server Managment Studioで確認します。

###FILESTREAMのデータを保存するテーブルを作る
FILESTREAMデータを保存するテーブルを作る例として、以下のスクリプトを実行して、UploadFile という名前のテーブルを作成します。

CREATE TABLE UploadFile (
   FileID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL PRIMARY KEY,
   FileData VARBINARY(MAX) FILESTREAM NULL,
   UploadFileName NVARCHAR(100) NULL
);

テーブルに UNIQUEIDENTIFIER データタイプで ROWGUIDCOL プロパティを設定した ID となるカラムを作り、FILESTREAMデータを保存するカラムは、データタイプは VARBINARY(MAX) にし、FILESTREAM アトリビュートを指定します。

###FILESTREAMを使ってファイルを保存する
C#などで、ファイルをバイト配列に読み込み、そのバイト配列を普通のデータのように Insert します。

DECLARE @FileData VARBINARY(MAX) = 0x504B0304140006000800000021...,
        @UploadFileName NVARCHAR(100) = 'Text.xlsx';

INSERT INTO UploadFile (
   FileID,
   FileData,
   UploadFileName
)
VALUES (
   NEWID(),
   @FileData,
   @UploadFileName
);

###ファイルをバイト型配列に読み込む、バイト型配列をファイルに書き込む
下記のソースを参考に処理を作成しています。
(通常は、usingステートメントやtry...finallyを使って確実にCloseメソッドが呼び出されるようにします)

//ファイルを開く
var fs = new System.IO.FileStream(
  @"C:\test.txt",
  System.IO.FileMode.Open,
  System.IO.FileAccess.Read);
//ファイルを読み込むバイト型配列を作成する
var bs = new byte[fs.Length];
//ファイルの内容をすべて読み込む
fs.Read(bs, 0, bs.Length);
//閉じる
fs.Close();

###バイト配列⇒16進数文字列へ変換
下記のソースを参考に処理を作成しています。

/// <summary>
/// バイト配列⇒16進数文字列へ変換
/// </summary>
class ByteArray01
{
  private static byte[] b = { 0x41, 0x42, 0x43 };
  private static string s = string.Empty;

  static void Main(string[] args)
  {
    // s = "414243"
    s = BitConverter.ToString(b).Replace("-", string.Empty);
  }
}

##参考
SQL Server: FILESTREAM（ファイルストリーム） を使ってファイルを保存する方法
FILESTREAM の有効化と構成
ファイルをバイト型配列に読み込む、バイト型配列をファイルに書き込む
【備忘録】byte配列⇒16進数文字列へ変換

##3) BLOB を通常の varbinary(max) データとして、テーブルに保存する方法
BLOB を通常の varbinary(max) データとして、テーブルに保存する方法になります。
FILESTREAMの利用にはWindows 認証が必要なため、SQL Server 認証 のみの環境では、こちらとなります。

###サンプルテーブル (myTable)

列名	データ型
FileName	tinytext
FileData	varbinary(max)

###OPENROWSET で varbinary(max) を登録する方法
varbinary(max) データとして登録には、OPENROWSETでファイルを指定します。

INSERT INTO myTable(FileName, FileData)
  SELECT
    'file.dat' AS FileName
    ,*
  FROM
    OPENROWSET(BULK N'E:\file.dat', SINGLE_BLOB) AS FileData

成功すれば、下記のように登録されるはず。
(FileDataの中身は架空)

	FileName	FileData
1	file.dat	0x255044462D312E3...

###登録した varbinary(max) をファイル保存する方法
varbinary(max) を取得して、バイト配列としてファイルに保存します。

/// <summary>
/// varbinary(max)をファイルに保存する
/// </summary>
public void WriteVarbinaryToFile()
{
  // 接続文字列作成
  var builder = new SqlConnectionStringBuilder()
  {
    DataSource = @".\Local_SQLEXPRESS",
    InitialCatalog = "myTable",
    IntegratedSecurity = false,
    UserID = "sa",
    Password = "hogehoge"
  };

  using (var connection = new SqlConnection(builder.ToString()))
  {
    connection.Open();

    var command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = "SELECT FileData FROM myTable WHERE FileName = N'file.dat'";

    // varbinary(max)データ取得
    using (var reader = command.ExecuteReader())
    {
      while (reader.Read())
      {
        // ファイルに保存
        File.WriteAllBytes(@"E:\file.dat", (byte[])reader["FileData"]);
      }
    }
  }
}

##参考
OPENROWSET (Transact-SQL)
DB2 10.5にC#から画像データをBLOB列に登録する