C#

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

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

名無し君
名無し君
すぐにデータベースへデータを登録して集計をしたい
名無しちゃん
名無しちゃん
サーバーではなく、データベースをアプリケーションに組み込んでつくりたい

こんな時に使えるのが「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にはスキーマデータベースファイルテーブルのメタ情報を管理するための内部テーブルが用意されています。

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

  1.  sqlite_master
  2.  sqlite_temp_master
  3.  sqlite_sequence

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

① sqlite_masterテーブル

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

オブジェクト名内容
テーブル(table)データベース内でデータを格納する領域のこと。
インデックス(index)検索速度を高速化させる為に、検索対象のカラムのデータと格納位置の組み合わせで構成されたデータ構造のこと。
ビュー(view)既存テーブルから任意のデータを取り出したり組み合わせたりして作った仮想的なテーブルのこと。
トリガー(trigger)テーブルに対するINSERT/UPDATE/DELETEなどのイベントが発生した際に実行される操作を登録できる機能のこと。

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

オブジェクト名内容
typeオブジェクトのことです。table, index, view,triggerから選択します。
nameオブジェクト名のことです。
tbl_nameオブジェクトが所属するテーブルの名前のことです。
rootpageテーブルとインデックスのためのroot b-treeページのページ番号のことです。
sqlオブジェクトを生成する際に実行されたSQL文がある。

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

select * from sqlite_master where type=’table’;

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

select sql from sqlite_master where type=’table’ and name=’master’;

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

② 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()メソッドを作成します。

名無し君
名無し君
先ほど記載したSQLiteの基本構文を順番通りに書いています。この関数の引数にクエリーを渡すことで実行されます。

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

このサンプルでは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文を使ってテーブルを作成します。クエリーのルールは以下です。

SQL文の書き方

CREATE TABLE IF NOT EXISTS [テーブル名] (カラム1,カラム2,primary key (カラム1))

同じテーブル名を作成することはできないので、作成する前に存在しないかどうかをチェックする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文を使ってデータを登録します。データを登録するクエリーのルールは以下です。

SQL文の書き方

INSERT INTO [テーブル名] (カラム1,カラム2,カラム3) VALUES (データ1,データ2,データ3)

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文を使って登録されているデータを検索します。データを検索するクエリーのルールは以下です。

SQL文の書き方

SELECT * FROM [テーブル名] WHERE 検索条件 ORDER BY カラム ASC or DESC

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文を使って、登録されているデータを新しいデータに更新します。データを更新するクエリーのルールは以下です。

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

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

SQL文の書き方

UPDATE [テーブル名] SET カラム1 = 値1, カラム2 = 値2, … WHERE 条件式;

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文を使ってテーブルに登録されているレコードを削除します。レコードを削除するクエリーのルールは以下です。

SQL文の書き方

DELETE FROM [テーブル名] WHERE 検索条件

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

    // クエリー実行
    ExecuteNonQuery(query.ToString());
}
この関数を使ってテーブルに登録されているレコードを削除する例が以下になります。
DeleteRecord("NAME", "coffee");

 

テーブルの削除

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

SQL文の書き方

DROP TABLE [テーブル名]

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

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

 

バージョン確認

System.Data.SQLiteで使われているSQLiteのバージョンを確認することができます。
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を使う機会があれば、この記事を参考にしてもらえたら幸いです。

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

プログラミングを学習したいなら…

プログラミングスキルを身に付けるなら、プログラミングを効率良く学べる
プログラミングスクール」がオススメです。

特にこんな方にオススメ!!
これからエンジニアを目指したい
プログラミングの専門性を高めたい
プログラミングを学んで副業をしたい
エンジニアに転職して年収をアップさせたい

プログラミングを触ったことがない未経験からでも、プログラミングスクールで学習すれば、エンジニアへ就職・転職することも可能です。

あなたの「行動力」と「やる気」で、あなたの人生を大きく変えるチャンスになることでしょう。

プログラミングスクールに興味がある方は是非チェックしてみてください。

> プログラミングを学ぶ <

COMMENT

メールアドレスが公開されることはありません。

CAPTCHA