翔星 Be ランド日記

• どの SQLite ライブラリを使うか？
• ADO.NET とは？
• System.Data.SQLite のインストール
• System.Data.SQLite の基本
• テーブル構造定義クラスの導入
• レコードの挿入
• レコードの検索
• レコードの削除
• サンプルコード
• サンプルコードアプリの使い方
• 参考資料
• 更新履歴

どの SQLite ライブラリを使うか？

C# で SQLite を使うための方法はいくつかあり、自分が把握している範囲では以下のようになる。

• C 言語用 DLL を P/Invoke で使う……本家本元の DLL を使い、従来型の文字列によるクエリでプログラミング。最新のライブラリを使える（本稿執筆時点で 3.9.2）。しかし、C 言語レベルの生産性しかなく、労多くして功少なしなので、どうしても最新のバージョンを使いたい時以外はお薦めしない。
• System.Data.SQLite……SQLite 本家による .NET 用ライブラリ。データベースを便利に使う ADO.NET に対応しており、生産性が向上。C 言語用 DLL よりは多少バージョンが古い（3.8.11.1 ベース）ものの、総合的に一番お薦め。
• Mono.Data.SqliteClient…….NET のオープン・クロスプラットフォーム実装である Mono プロジェクトが作成したライブラリ。ADO.NET 対応。文字コードが Unicode（恐らく UTF-16）と記載があるのが気になる（本家は UTF-8）。別途本家 C 言語用 DLL が必要な模様。
• csharp-sqlite……マネージドコードのみで作成されたライブラリ。ADO.NET 対応。2011 年で開発停止の模様。バージョンは 3.7.7.1。マネージドコードしか使えない制約がある場合に重宝しそう。
• sqlite-net……コンパクトなライブラリ。ADO.NET 非対応。できる限り実行ファイルサイズを小さくしたい場合に活躍しそう。2012 年あたりで開発が停止しているようだ。

以降では、System.Data.SQLite を使う。

ADO.NET とは？

System.Data.SQLite は ADO.NET に対応しているが、そもそも ADO.NET とは何か。ADO.NET の詳しい解説は @IT の記事などに記載があるが、プログラマーから見たメリットを三行で書くとすれば、

• エディタのコード補完で楽ちんかつ安全にクエリを書ける（LINQ）
• 設計時からデータ構造をビジュアル化できる（Entity Framework）
• 使うデータベース（SQLite/Oracle……）によらず同じコードを使い回せる

あたりになるのではと理解している。

これにより、C 言語で SQLite をいじるよりもはるかに効率的にデータベースプログラミングが行える。

なお、本稿では、LINQ は使うが Entity Framework は使わない。

System.Data.SQLite のインストール

インストールは非常に簡単である。ここでは、Visual Studio 2015 Community / .NET Framework 4.5 の環境で実行しているものとする。

1. Visual Studio を起動し、SQLite を使いたいプロジェクトを開く
2. メニューの［ツール｜NuGet パッケージマネージャー｜ソリューションの NuGet パッケージの管理］をクリック
3. 検索窓に「SQLite」と入力して SQLite パッケージを検索
4. 検索結果の System.Data.SQLite を選択して、インストールボタンをクリック。関連するライブラリも含めて自動でインストールが終わる

たったこれだけ。

以前は、NuGet をコマンドラインで使う必要があった時代もあったようだが、現在は GUI で使える。

NuGet の欠点は、プロジェクトごとにインストールの必要があるため（プロジェクトに最適なバージョンを選んでインストールしてくれる）、SQLite を使うプロジェクトが複数ある場合は、ディスクをどんどん消費していく。その場合は、ダミープロジェクトでインストールしたバイナリを使い回したり、手動でバイナリをダウンロードして共用するといいかもしれない。

System.Data.SQLite の基本

データベースファイル（*.db とか *.sqlite3 とか）を開くには、SQLiteConnection クラスを使う。

SQLiteConnection を new する際にパラメーターを文字列で渡すのだが、SQLiteConnectionStringBuilder というお助けクラスを使うと、パラメーター文字列をミスなく生成できる。

SQLiteConnectionStringBuilder aConnectionString = new SQLiteConnectionStringBuilder
{
    DataSource = @"R:\Test.db"
};
using (SQLiteConnection aConnection = new SQLiteConnection(aConnectionString.ToString()))
{
    aConnection.Open();

    // ここにデータベース処理コードを書く
}

従来型の文字列によるコマンドを発行したい場合は、SQLiteCommand クラスを使う。

using (SQLiteCommand aCmd = new SQLiteCommand(aConnection))
{
    aCmd.CommandText = "CREATE TABLE IF NOT EXISTS t_test (test_id INTEGER NOT NULL PRIMARY KEY, test_name NVARCHAR NOT NULL, test_height REAL);";
    aCmd.ExecuteNonQuery();
}

テーブル構造定義クラスの導入

データへのアクセスを簡単・分かりやすくするために、テーブル構造を定義するクラスを作成する。

簡易名簿テーブル「t_test」が以下のような構造になっているとする。

フィールド名	型	NULL	備考
test_id	INTEGER	不可	連番
test_name	NVARCHAR	不可	氏名
test_height	REAL	可	身長

この場合、テーブル構造定義クラスは以下のようになる。

[Table(Name = "t_test")]
public class TTestData
{
    // ID
    [Column(Name = "test_id", DbType = "INT", CanBeNull = false, IsPrimaryKey = true)]
    public Int32 Id { get; set; }

    // 氏名
    [Column(Name = "test_name", DbType = "NVARCHAR", CanBeNull = false, UpdateCheck = UpdateCheck.Never)]
    public String Name { get; set; }

    // 身長
    [Column(Name = "test_height", DbType = "REAL", CanBeNull = true)]
    public Double? Height { get; set; }
}

フィールドをプロパティーとして定義しているシンプルなクラスである。

CREATE TABLE で作成した際のテーブル情報に合わせて、クラスメンバーの属性を記述している。例えば ID フィールドであれば、Name = "test_id" でデータベース上のフィールド名、DbType = "INT" でデータ型、CanBeNull = false で NULL 不可であることを表している。

DbType の型について注意点が 2 つ。

• 整数型は DbType = "INT" とする。INTEGER ではエラーになる。
• 文字列型は DbType = "NVARCHAR" とする。TEXT ではエラーになる。
  

特に、文字列型を TEXT としてしまうと、原因が分かりづらいエラーが発生し、ハマることになる。

身長フィールドは、NULL を許可している。CanBeNull = true にすると共に、プロパティーの型を「Double?」というように「?」を付けて nullable にしている。

レコードの挿入

テーブル構造定義クラスを導入したことにより、データベースの取扱が非常に簡単になる。

レコードの挿入を行うコードは、以下のようになる。SQLiteCommand クラスを使わなくて済むので全然 SQL っぽくなく、普通のコードのように書ける。

using (DataContext aConText = new DataContext(aConnection))
{
    Table<TTestData> aTableTest = aConText.GetTable<TTestData>();
    aTableTest.InsertOnSubmit(new TTestData { Id = 1, Name = "Fukada Kyoko" });
    aTableTest.InsertOnSubmit(new TTestData { Id = 2, Name = "Eda Ha", Height = 180.0 });
    aTableTest.InsertOnSubmit(new TTestData { Id = 3, Name = "Dan Gerou", Height = 150.5 });
    aTableTest.InsertOnSubmit(new TTestData { Id = 4, Name = "Baba Takashi" });
    aTableTest.InsertOnSubmit(new TTestData { Id = 5, Name = "Aikawa Ai", Height = 145.6 });
    aConText.SubmitChanges();
}

ID 1 や 4 では身長を設定していないので、データベース上 NULL として格納される。

レコードの検索

レコードの検索は LINQ to SQLite と呼ばれる手法で行う。ちょっと変わった書き方だが、エディタのコード補完が効くのでミスが減る。

using (DataContext aConText = new DataContext(aConnection))
{

    Table<TTestData> aTableTest = aConText.GetTable<TTestData>();
    IQueryable<TTestData> aQueryResult =
            from x in aTableTest
            where x.Name == "Eda Ha" || x.Height < 150.0
            orderby x.Height
            select x;
    foreach(TTestData aData in aQueryResult)
    {
        Debug.WriteLine(aData.Name);
    }
}

from のところが SQL の発行に相当する部分。なんとなく SQL に似ているので、読めば理解はできると思う。

where x.Name.Contains("hoge")

のようにすれば良い。

レコードの削除

レコードを削除する場合、削除したいレコードを検索し、それを削除する、という流れになる。

検索については前節と同じで、その結果を、DeleteAllOnSubmit() メソッドにぶち込むだけ、とこれまた簡単。

using (DataContext aConText = new DataContext(aConnection))
{
    Table<TTestData> aTableTest = aConText.GetTable<TTestData>();
    IQueryable<TTestData> aDelTargets =
            from x in aTableTest
            where 140 < x.Height && x.Height < 160
            select x;
    aTableTest.DeleteAllOnSubmit(aDelTargets);
    aConText.SubmitChanges();
}

サンプルコード

以上をまとめたものを、サンプルコードとして公開する。

• 公開場所……GitHub
• ライセンス……クリエイティブ・コモンズ・ライセンス（表示 2.1 日本）（第三者部分を除く）
• ビルド方法……別途 LinqUtils.cs が必要。詳細はリポジトリ内の TestLinqToSqlite_ReadMe_JPN.html 参照

サンプルコードアプリの使い方

なお、サンプルコードアプリにはジェネリックの欄もあるが、こちらについては「LINQ to SQLite で共通カラム部分をジェネリックで運用する」を参照。

参考資料

• ADO.NET基礎講座
• C#とEntity FrameworkでSQLiteを使う（Entity Framework実践編）
• 【C#】System.Data.SQLiteを使ってみる
• MonoBook：csharp-sqlite、sqlite-net
• C# で SQLite を便利に使うサンプルコード（LINQ to SQLite）【改訂版】
  

更新履歴

※改訂版を公開しましたので、改訂版をご覧ください。