C#のSQLiteでバイナリデータを扱う

SQLiteはバイナリデータも扱うことができます。

バイナリデータを格納できるので画像ファイルなどをデータベースに格納するといった使い方も可能ですね。

バイナリデータを格納できるテーブルを作成する

バイナリデータを格納したい場合にはテーブルのフィールド型にBLOB(Binary Large OBject)を使用します。 

var connectionString = "Data Source=test.db";
using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();

    var sql = "CREATE TABLE test (ID INTEGER, Data1 BLOB)";
    using (var command = new SQLiteCommand(sql, connection))
    {
        command.ExecuteNonQuery();
    }

    connection.Close();
}

バイナリデータを追加する

テーブルにデータを挿入するためにはSQL文のINSERTを使いますが、バイナリデータをどうやってSQL文に？と思ってしまいますね。パラメータという仕組みを使ってバイナリデータをテーブルに格納する事ができます。

var connectionString = "Data Source=test.db";
using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();

    var data1 = new byte[4];
    data1[0] = 0x00;
    data1[1] = 0x01;
    data1[2] = 0x02;
    data1[3] = 0x03;

    var sql = "INSERT INTO test (ID,Data1) VALUES(1,@p1)";
    using (var command = new SQLiteCommand(sql, connection))
    {
        var parameter = new SQLiteParameter();
        parameter.ParameterName = "@p1";
        parameter.DbType = System.Data.DbType.Binary;
        parameter.Value = data1;

        command.Parameters.Add(parameter);
        command.ExecuteNonQuery();
    }

    connection.Close();
}

上記の例では、SQL文のバイナリデータの値としたい部分に「@p1」という指定がされています。

さらに、15～18行目でSQLiteParameterというオブジェクトを作成していていますね。

SQL文の「@p1」の部分をSQLiteParameterで指定した値に置き換えるという仕組みです。

（名前は@で始める必要があります）

SQLiteParameterの「ParameterName」にSQL文の置き換える名前を指定し、

SQLiteParameterの「DBType」にはデータの型を示すBinaryを指定し、

SQLiteParameterの「Value」に格納したいデータを指定します。

そしてこのSQLiteParameterをSQLiteCommandのParametersへAddするという使い方になります。

バイナリデータを取得する

データベースに格納したバイナリデータを取り出したい場合はSQLiteDataReaderクラスを使って取得できます。

var connectionString = "Data Source=test.db";
using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();

    var sql = "SELECT * FROM test";
    using (var command = new SQLiteCommand(sql, connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var data1 = (byte[])reader["Data1"];



            }
            reader.Close();
        }
    }

    connection.Close();
}

SQLiteDataReaderオブジェクトから該当のフィールドの値をbyte配列にキャストして取り出します。