.NET で SQLite を使う まとめ

.NET で SQLite を使ってみます。
過去に書いたい着くかの記事のまとめです。

今回の流れ

• NuGet からパッケージインストール
• データベースファイルを作って
• テーブルを作って
• テーブルの定義を読んで
• データを Insert して
• データを Select します

NuGet からパッケージインストール

今回使うライブラリを NuGet からインストールしてください。

• Microsoft.Data.Sqlite

データベースファイルを作る

データベースファイルはカレントディレクトリに Data Source で指定したファイル名で作成されます。
カレントディレクトリを変更することで、ファイルが作成される場所を変更できます。

例えば次のようにすることで、カレントディレクトリの下の data というディレクトリの中に database.db というファイル名で作成できます。

using (var connection = new SqliteConnection("Data Source=data/database.db"))

テーブルを作る

データベースとして使うためにはまずはテーブルを作らなければなりません。
まあ特別なことはなく、次の一文を実行するだけです。

CREATE TABLE IF NOT EXISTS saitama ( no INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL );

C# 上で実行するコードは次のような感じです。

// テーブルを作成する
command.CommandText = "CREATE TABLE IF NOT EXISTS saitama ( no INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL);";
command.ExecuteNonQuery();

テーブルの定義を読む

アプリの中でテーブルの定義を確認したいことは実は良くあります。
次のような一文を実行します。

PRAGMA TABLE_INFO ('saitama')

C# 上で実行するコードは次のような感じです。

// テーブルの情報を取得/表示してみる
Console.WriteLine("テーブルの情報");
command.CommandText = "PRAGMA TABLE_INFO ('saitama')";
using (var reader = command.ExecuteReader())
{
    var dt = new DataTable();
    dt.Load(reader);
    // 結果を表示
    foreach (var row in dt.AsEnumerable())
    {
        foreach (DataColumn col in dt.Columns)
            Console.Write($"{col.ColumnName}:{row[col.ColumnName]} ");
        Console.WriteLine();
    }
}

データを Insert する

今回、とりあえずデータの Select までをやりたいので、Select するためのデータを Insert します。
Insert ですから特別なことは何もないです。

insert into saitama values (1, 'せんべい');

C# 上で実行するコードは次のような感じです。

// テーブルにデータを Insert する
command.CommandText = "insert into saitama values (1, 'せんべい');";
command.ExecuteNonQuery();

データを Select する

データを Select します。やはり特別なことは何もないです。

select * from saitama where no = $no;

C# 上で実行するコードは次のような感じです。

// テーブルを検索する
command.CommandText = "select * from saitama where no = $no;";
command.Parameters.AddWithValue("$no", 1);
using (var reader = command.ExecuteReader())
{
    var dt = new DataTable();
    dt.Load(reader);
    // 結果を表示
    foreach (var row in dt.AsEnumerable())
        Console.WriteLine($"Saitama, {row["name"]}");
}

全体をつなげて実行できるコードにする

前述の要素たちをつなげて動作する一つのコードにまとめてみます。
もし試す際はこのコードをコピー&ペーストして試してください。
※要素間のつなぎや、複数回実行するときのためのコードで少し書き足しがあります。

using Microsoft.Data.Sqlite;
using System;
using System.Data;
using System.IO;
using System.Linq;

// カレントディレクトリに data というディレクトリを作って database.db という名前でデータベースのファイルを作る
// まずは data ディレクトリをなければ作成
var directory = new DirectoryInfo(Path.Combine(Environment.CurrentDirectory, "data"));
if (!directory.Exists) directory.Create();
// データ Insert 用のサンプルなので、毎回初期化したい。雑にデータベースファイルを毎回作り直してしまうために一旦削除する
var file = new FileInfo(Path.Combine(Environment.CurrentDirectory, "data", "database.db"));
if (file.Exists) file.Delete();

// データベースファイルは data/database.db
using (var connection = new SqliteConnection("Data Source=data/database.db"))
{
    connection.Open();
    var command = connection.CreateCommand();

    // テーブルを作成する
    command.CommandText = "CREATE TABLE IF NOT EXISTS saitama ( no INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL);";
    command.ExecuteNonQuery();

    // 一旦テーブルの情報を取得/表示してみる
    Console.WriteLine("テーブルの情報");
    command.CommandText = "PRAGMA TABLE_INFO ('saitama')";
    using (var reader = command.ExecuteReader())
    {
        var dt = new DataTable();
        dt.Load(reader);

        // 結果を表示
        foreach (var row in dt.AsEnumerable())
        {
            foreach (DataColumn col in dt.Columns)
                Console.Write($"{col.ColumnName}:{row[col.ColumnName]} ");
            Console.WriteLine();
        }
    }

    Console.WriteLine();

    // テーブルにデータを Insert する
    command.CommandText = "insert into saitama values (1, 'せんべい');";
    command.ExecuteNonQuery();

    // テーブルを検索する
    Console.WriteLine("データの検索結果");
    command.CommandText = "select * from saitama where no = $no;";
    command.Parameters.AddWithValue("$no", 1);
    using (var reader = command.ExecuteReader())
    {
        var dt = new DataTable();
        dt.Load(reader);

        // 結果を表示
        foreach (var row in dt.AsEnumerable())
            Console.WriteLine($"Saitama, {row["name"]}");
    }
}

実行結果

テーブルの情報
cid:0 name:no type:INTEGER notnull:1 dflt_value: pk:1
cid:1 name:name type:TEXT notnull:1 dflt_value: pk:0

データの検索結果
Saitama, せんべい

簡単ですね。

以前に書いた個々の要素

今回よりもう少し詳しく書いています。