C#でSQLiteにアクセス（ADO.Net入門）

コンテンツ

業務でADO.NetとSQLiteを使うようになって、ようやくテスト工程に入れました。
実際動かしてみると、結構間違えて認識していた事もボロボロでてきます。
で、自分的に大事だと思うことのメモをまとめておきます。

SQLiteも暗号化できる

SQLiteといえば面倒なログイン作業もないからお手軽！という紹介が多いですが、これは「パスワードをかけることはできない」という意味ではないことが罠。
アカウントは確かに必要ないですが、パスワードだけなら設定できます。
意外と解説サイトがないことにビビる。暗号化、大事じゃないですか。

SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=…(中略)…\\ファイル名.db"
// まず初期状態ならパスワードがないのでOpenできるはず
connection.Open();
// パスワードを設定して暗号化
connection.ChangePassword("設定したいパスワード");
connection.Close();

ちなみに、暗号化したSQLiteの接続方法はいくつかあるけど、一番しっくりきたのはこの方法。

SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=…(中略)…\\ファイル名.db"

// パスワードを設定(このパスワードでOpenを試みます)
// ちなみに、このSetPasswordはOpen前に呼ばないと例外をthrowします
connection.SetPassword("パスワード");

// 接続開始。パスワードを設定しない or 間違えていると例外をthrow
connection.Open();

今は非推奨

ダウンロードしてきたv1.0.97.0でなら動くけど、NuGetパッケージ管理からインストールした1.0.113.7ではコンパイルエラーになる。
どうやら暗号化は非推奨になったもよう。
Microsoft.Data.SQLiteなら対応しているけど、後述するDataAdapter.Fillによる取り出しが未実装です。
それに古いSystem.Data.SQLiteを使っても、他ツール（例えばPupSQLite.exe）を使っても開けなくなっちゃう。

DataAdapter.Fillによるデータ取り出し

Integer編

もうC++とXMLでゴリゴリWinアプリ作っていた身としては、
SQLiteのデータ型の適当っプリが神のようにありがたいし、5歳児のように始末悪いです。
テーブル内容をDataSet型に取り出す方法は簡単ですが、一応コード。

// connectionはOpen済みのSQLiteConnectionオブジェクト(前述)ってことで
SQLiteDataAdapter adapter = new SQLiteDataAdapter(connection, "SELECT * FROM [テーブル名]");

DataSet data = new DataSet();
adapter.Fill(data);

で、Integer型って実際何者よって調べてみました。

INTEGER:符号付整数。1, 2, 3, 4, 6, or 8 バイトで格納

格納する時はint(4byte)変数値を使っていても、テーブル定義時にINTEGER(4)と指定しても、いつの間にか簡単に拡張なさりやがる……。
ってわけで取り出し方は簡単ですが、ついつい(int)data.Table[0].Rows[0][“カラム名”]とかやると吹っ飛びますよって話です。

// 1. 文字列に変換して取り出したカラム値をint.Parseでintに変換する。
int ret = int.Pause(data.Table[0].Rows[0]["カラム名"].ToString());

// 2. 8byteのlong型でキャストする
long ret = (long)(data.Table[0].Rows[0]["カラム名"]);

個人的には1を採用したいです。
結局DBにいくら入れられても、その情報を使う側がint型ならint型に取った方が無駄がない気がするし、使う側の意図に反した情報が入るとエラーにできるから。

BLOB編

なんでもいれられるBLOB型、すばらしいじゃないですか。
じゃあ、このクラス情報をぜーんぶ入れちゃえ！って先輩から言われたのはいいんですが、簡単に言うならコードを見せろと思うくらいチマチマチマチマ調べ事が多かった……。
ということでサンプルソース置いておきます。
ところでDataAdapter.FillSchemaを利用してスキーマを設定すれば型の不一致防げるかと思ったんですが、このBLOB型をbyte[]でキャストできなくなっちゃうんですよね……。そのへんは未調査。

まずサンプルでしたいこと。
TestClassクラスをバイト配列にシリアライズして、DB内のTestTableテーブル内のTestColumnカラムにBLOB型で入れるサンプルです。

まずSQLiteのテーブル定義
CREATE TABLE TestTable (
    id TEXT PRIMARY KEY, // レコードを特定するためのキーです
    TestColumn BLOBL // ここにシリアライズ結果を格納します
);

DBに入れちゃいたいTestClassクラスの宣言

// シリアライズしたいクラスには[Serializable]属性をつけます
// 派生クラスを実装した場合にも、派生クラスに[Serializable]属性が必要です。
[Serializable]
public class TestClass
{
    // 保持しておきたいメンバ
    private int _data1 = 0;

    // シリアライズした時、保持しておきたくないものには[NonSerialized]属性をつけます
    // 私はconstメンバによくつけます。
    [NonSerialized]
    private int _data2 = 0;

    // コンストラクタ
    public TestClass(int data1){
        this._data1 = data1;
    }
}

DBに入れちゃいます。

// DBに入れちゃうオブジェクト
TestClass object = new TestClass(2);

BinaryFormatter formatter = new BinaryFormatter();
MemoryStream stream = new MemoryStream();
formatter.Serialize(stream, object);

// DBに入れるために、まずDB内容を読み込みます
// connectionはOpen済みのSQLiteConnectionオブジェクト(前述)ってことで
SQLiteDataAdapter adapter = new SQLiteDataAdapter(connection, "SELECT * FROM TestTable");
DataSet data = new DataSet();
adapter.Fill(data);

// 追加レコードを作ります
DataRow row = data.Tables[0].NewRow();
row["id"] = "ID001"; // これは適当にユニークな値
row["TestColumn"] = stream.ToArray(); // Streamをbyte[]に変換して格納します

// テーブルへ作ったレコードを追加します
data.Table[0].Rows.Add(row);
adapter.Update(data);

DBから取っちゃいます。

// DB内容を読み込みます
// connectionはOpen済みのSQLiteConnectionオブジェクト(前述)ってことで
SQLiteDataAdapter adapter = new SQLiteDataAdapter(connection, "SELECT * FROM TestTable WHERE id='ID001'");
DataSet data = new DataSet();
adapter.Fill(data); // data.Table[0].Rows[0]に追加したレコードが取れるはずです

// byte[]でシリアライズ結果を取り出します
byte[] blob = (byte[])data.Table[0].Rows[0]["TestColumn"];

// 結果をデシリアライズします。
MemoryStream stream = new MemoryStream(blob)
stream.Seek(0, SeekOrigin.Begin);
BinaryFormatter formatter = new BinaryFormatter();
TestClass object = (TestClass)formatter.Deserialize(stream);