【C#】SQLiteで今すぐデータベースを使う方法とサンプルを紹介

突然ですが、こんな悩み持ってませんか？？

[chat face=”icon.png” name=”名無し君” align=”left” border=”blue” bg=”none” style=”maru”] すぐにデータベースへデータを登録して集計をしたい[/chat]

[chat face=”icon2.png” name=”名無しちゃん” align=”right” border=”blue” bg=”none” style=”maru”] サーバーではなく、データベースをアプリケーションに組み込んでつくりたい [/chat]

こんな時に使えるのが「SQLite」です。

データベースというと、Xserverのデータベースに使われているMySQLをはじめ、PostgreSqlやSQLServerなど数多くの種類があります。

これらの中でも、SQLiteはローカル環境で使う場合、これほど手軽に扱えて高機能なデータベースは他にないと思います。インストールも簡単な上に非常にコンパクト(350KB程度)なため、アプリケーションと一緒に配布することも可能です。

ただし、ローカル環境のみで使うことができるので、一般的なデータベースのように複数のパソコンから同時アクセスすることはできないので注意しましょう。

この記事では、C#でSQLiteをすぐに使うための方法やサンプルを交えて詳しく説明をしています。SQLiteを使ってデータベースやテーブルの作成方法、そしてデータの追加や取得する方法について記載をしていますので、是非最後まで読んで参考にしてみてください。

パッケージのインストール

SQLiteを使う為にNuGetからパッケージのインストールをしましょう。

SQLiteをインストールしたいプロジェクトをVisual Studioで開きます。メニューバーから「ツール」＞「NuGetパッケージマネージャ」＞「ソリューションのNuGetパッケージの管理」の順に選択をします。

NuGetパッケージの管理画面で参照タグをクリックし、検索欄に「sqlite」と入力して検索をします。

検索ワードに関連するパッケージ一覧の中から、「System.Data.Sqlite.Core」をクリックし、「インストール」ボタンをクリックしてインストールを行います。

インストールが終わったら、コンパイルしてデバックをしてみましょう。

プロジェクトフォルダからデバックフォルダを開いて、アプリケーションと一緒にSQLiteのライブラリも一緒に作成されてれば、問題なくパッケージのインストールができていることになります。

また、Visual Studioの「ソリューション エクスプローラー」の参照に「System.Data.SQLite」が追加されます。

データベースの構造について

実際に扱う前にデータベースの構造を確認しておきましょう。

SQLiteの場合、データベースの本体は通常のファイルになります。このファイルの中にテーブルを作成し、データを登録します。このテーブルは他のデータベースと同様に複数作成可能です。

内部テーブルについて

SQLiteにはスキーマ、データベースファイル、テーブルのメタ情報を管理するための内部テーブルが用意されています。

よく利用する内部テーブルを以下に記載します。

少し詳しくそれぞれについて確認してみましょう。

① sqlite_masterテーブル

このテーブルは、データベースファイルに一つだけ生成されます。データベースファイルの中に生成された4種類のオブジェクトの情報が保存されます。

上記で記述したオブジェクトを扱うプロパティ的なものが以下です。これを使えば、テーブルの情報などを取得することができます。

これらを使うことで、例えばデータベース内に格納されているテーブル情報を取得することができます。

また、どのようなSQL文でテーブルが生成されたかを確認することもできます。

このようにして内部テーブルの情報を取得しましょう。

② sqlite_temp_master

このテーブルは、データベースファイルに一つだけ生成されます。一時テーブルの情報が格納されています。

sqlite_masterと同じオブジェクトです。

③ sqlite_sequence

このテーブルは、データベースファイルに一つだけ生成されます。

データベース内に存在しているテーブルのINTEGER PRIMARY KEYの最大値を保持します。この最大値はAUTOINCREMENTの動作で利用されます。

SQLiteのデータ型

データベースは数値や文字列などいろいろなデータを格納することができます。利用できるデータ型は5種類あります。

一般的なデータベースでは、カラムを設定するときにデータ型を指定しますが、SQLiteはデータ型を指定しなくてもいいという特徴があります。

カラムにデータ型が指定されていない場合、入力されたデータの記述から型を判定します。

たとえば、”NASDAQ”のようにダブルクォーテーションやシングルクォーテーションで囲まれていればTEXT型になります。1や100といった数字ならINTEGER型、1.1や100.1など小数点ならREAL型に分類されます。

SQLiteの使い方

それではSQLiteを使ってテーブルの作成やデータの登録・削除などを紹介します。

まずは、SQLiteを扱うために以下の名前空間を定義します。

//参照先を追加
using System.Data.Linq;
using System.Data.SQLite;

これでソースコード内で名前空間を省略して記述ができるようになります。

SQLiteの基本構文

SQLiteはデータベースの接続先を指定、データベースに接続、コマンド登録、コマンド実行の順番に処理を記述します。

この手順はSQLite以外のMySQLやSQLServerなどでも共通です。

クエリー実行

テーブルの作成やデータの登録や削除などのクエリーを実行するExecuteNonQuery()メソッドを作成します。

[chat face=”icon.png” name=”名無し君” align=”left” border=”blue” bg=”none” style=”maru”]先ほど記載したSQLiteの基本構文を順番通りに書いています。この関数の引数にクエリーを渡すことで実行されます。[/chat]

クエリー実行時の注意点は、リソースの解放し忘れをしないことです。リソースの解放忘れはリソースリークの発生原因になります。

このサンプルではUsingステートメントを使用して宣言されたオブジェクトは、Usingブロックを抜けるタイミングで自動的にリソースが破棄されます。

private void ExecuteNonQuery(string query)
{
    try
    {
        // 接続先を指定
        using (var conn = new SQLiteConnection("Data Source=DataBase.sqlite"))
        using (var command = conn.CreateCommand())
        {
            // 接続
            conn.Open();

            // コマンドの実行処理
            command.CommandText = query;
            command.ExecuteNonQuery();
            //var value = command.ExecuteNonQuery();
            //MessageBox.Show($"更新されたレコード数は {value} です。");
        }
    }
    catch (Exception ex)
    {
        //例外が発生した時はメッセージボックスを表示
        MessageBox.Show(ex.Message);
    }
}

コマンドを実行するExecuteNonQuery()は、INSERT・UPDATE・DELETEのような結果を返さないSQL文を実行する場合には、ExecuteNonQueryメソッドを使用します。

ExecuteNonQueryメソッドは戻り値として、INSERT・UPDATE・DELETEのSQL文を実行して影響を受けたレコード数を返します。

テーブルの作成

CREATE文を使ってテーブルを作成します。クエリーのルールは以下です。

[jin-fusen3 text=”SQL文の書き方”]

同じテーブル名を作成することはできないので、作成する前に存在しないかどうかをチェックするIF NOT EXISTSを追加しています。

private void CreateTable()
{
    // テーブル名が存在しなければ作成する
    StringBuilder query = new StringBuilder();
    query.Clear();
    query.Append("CREATE TABLE IF NOT EXISTS PURCHASELIST (");
    query.Append(" NO INTEGER NOT NULL");
    query.Append(" ,DATETIME TEXT NOT NULL");
    query.Append(" ,NAME TEXT NOT NULL");
    query.Append(" ,PRICE INTEGER NOT NULL");
    query.Append(" ,primary key (NO)");
    query.Append(")");

    // クエリー実行
    ExecuteNonQuery(query.ToString());
}

データの登録

INSERT文を使ってデータを登録します。データを登録するクエリーのルールは以下です。

[jin-fusen3 text=”SQL文の書き方”]

private void InsertRecord(int no, string datetime, string name, int price)
{
    // レコードの登録
    var query = "INSERT INTO PURCHASELIST (NO,DATETIME,NAME,PRICE) VALUES (" +
        $"{no},'{datetime}','{name}',{price})";

    // クエリー実行
    ExecuteNonQuery(query.ToString());
}

InsertRecord(1, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "coffee", 100);
InsertRecord(2, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "meet", 350);
InsertRecord(3, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "snacks", 200);
InsertRecord(4, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "juice", 150);

SQLiteを管理するツールとして「DB Browser for SQLite」があります。

このツールをインストールして起動します。このツール上でDataBase.sqliteを開いて登録されているデータを確認します。

データの検索

SELECT文を使って登録されているデータを検索します。データを検索するクエリーのルールは以下です。

[jin-fusen3 text=”SQL文の書き方”]

private List<string> SerachRecordData(string column, string word)
{
    // 検索条件
    var query = "SELECT * FROM PURCHASELIST WHERE " +
        $"{column} = '{word}' ORDER BY NO ASC";

    var result = new List<string>();
    // 接続先を指定
    using (var conn = new SQLiteConnection("Data Source=DataBase.sqlite"))
    using (var command = conn.CreateCommand())
    {
        // 接続
        conn.Open();

        // コマンドの実行処理
        command.CommandText = query;
        using (SQLiteDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var array = new string[]
                {
                    reader.GetValue(0).ToString(),
                    reader.GetValue(1).ToString(),
                    reader.GetValue(2).ToString(),
                    reader.GetValue(3).ToString()
                };
                result.AddRange(array);
            }
        }
    }

    return result;
}

var result = SerachRecordData("NAME", "coffee");

コマンドを実行するExecuteReader()は、SELECTのような結果を返すSQL文を実行する場合に使用します。

レコード（データ）の更新

UPDATE文を使って、登録されているデータを新しいデータに更新します。データを更新するクエリーのルールは以下です。

「 カラム１ = 値１, カラム２ = 値２, …」は、更新するカラムと新しいデータの組み合わせで記述します。

また条件式には、更新するデータを特定するための条件式になります。例えば、primary key（固有値）である「NO」が１であるレコードのみ更新を行う場合は、「WHERE NO = 1」とします。

[jin-fusen3 text=”SQL文の書き方”]

private void UpdateRecord(int no, string datetime, string name, int price)
{
    // レコードの登録
    var query = $"UPDATE PURCHASELIST SET DATETIME = '{datetime}', NAME = '{name}',PRICE = '{price}' " +
        $"WHERE NO = {no};";

    // クエリー実行
    ExecuteNonQuery(query.ToString());
}

UpdateRecord(1, DateTime.Now.ToString(), "tea", 100);

レコードの削除

DELETE文を使ってテーブルに登録されているレコードを削除します。レコードを削除するクエリーのルールは以下です。

[jin-fusen3 text=”SQL文の書き方”]

private void DeleteRecord(string column, string word)
{
    // レコードの削除
    var query = "DELETE FROM PURCHASELIST WHERE " +
        $"{column} = '{word}'";

    // クエリー実行
    ExecuteNonQuery(query.ToString());
}

DeleteRecord("NAME", "coffee");

テーブルの削除

DROP文を使ってテーブルを削除します。テーブルを削除するクエリーのルールは以下です。

[jin-fusen3 text=”SQL文の書き方”]

private void DropTable()
{
    // テーブルの削除
    var query = "DROP TABLE PURCHASELIST";

    // クエリー実行
    ExecuteNonQuery(query.ToString());
}

バージョン確認

private string VersionCheck()
{
    var result = "";

    // データベースの接続先を指定
    using (var conn = new SQLiteConnection("Data Source=DataBase.sqlite"))
    {
        // データベースに接続
        conn.Open();

        // コマンドの実行処理
        using (var command = conn.CreateCommand())
        {
            //バージョン確認のコマンド実行
            command.CommandText = "select sqlite_version()";
            result = command.ExecuteScalar().ToString();
        }
    }

    return result;
}

サンプルはバージョン確認するクエリーをCommandTextに代入し、ExecuteScalar()でコマンドを実行しています。ExecuteScalar()はクエリーが返す結果の、先頭行にある最初の列を返してくれます。

まとめ

テーブルの作成やデータの登録、検索などデータベースを扱う上でよく使う機能をSQLiteで実装する方法について説明をしました。サンプルも載せているので、即実装可能です。

SQLiteを非同期で実装する方法についても紹介しています。興味ある方は以下のリンクから確認してみてください。

あわせて読みたい

【C#】SQLiteを非同期（async / await）で実行する SQLiteは簡易的なデータベースとして扱うので、データ容量は小さく軽量で処理が早いという特徴があります。 アプリケーションに組み込んで使用することが多いデータベー...

SQLiteを使う機会があれば、この記事を参考にしてもらえたら幸いです。

以上、最後まで読んでいただきありがとうございました。