C#でデータベースSQLiteによる設定ファイル読み込みとバージョンアップ

2021.05.22

本記事は、[C#での設定ファイル管理とパラメータのバージョンアップ]の続きとして、データベースのSQLiteによるよる設定情報やパラメータの扱いについて記載します。設定ファイルを扱う方法として、データベースのなかでSQLiteのようなRDB（リレーショナルデータベース）を使うことは、コストのかかる方法になります。C#の知識だけではなく、データベースを使うためのデータベース設計やSQLなどの知識を必要です。さらに、複雑な構造をデータベースで構築するには、それにあった複雑な設計が必要になります。同時に、C#などのプログラミング言語でそのデータベースにアクセスし、設定情報を扱うためのコーディングも複雑になります。そのため、普通のアプリケーションなら、あまり勧めはしません。アプリケーションの事情により、どうしてもデータベースを使う必要がある場合を想定し、データベースのSQLiteを使った方法をまとめます。

SQLiteとは

一般的にデータベースは、一つのシステムになっていて、専用のサーバを必要とします。例えば、OracleやSql server、Mysql,PostgreSqlなどは、専用のサーバを起動し、そのサーバとのやり取りでデータベースを扱います。そのためには、データベースサーバの管理が必要になります。構築アプリケーションやシステムの規模・安定性・セキュリティ面で、上記のようなサーバ型を使わなければならない場合も多いでしょう。しかし、そこまで要求しない一般的アプリケーションでデータベースを使うのであれば、データベースサーバを必要としないサーバレースのSQLiteは力を発揮します。

SQLiteは、サーバが必要ないので、開発のアプリケーションの中から、SQLiteのDLLを読み込み、APIライブラリ（関数）を呼び出すだけでデータベースを構築できます。さらに、必要な時だけAPI経由で使うので、軽量でメモリ消費量も少なく、処理速度面で高速である長所を持っています。サーバがないので、複雑なデータベース設定も必要ありません。

なお、SQLiteは、オープンソース（ライセンスはPublic Domain）で公開されているので、商用利用も可能です。

SQLiteを使うためには

Visual Studioで開発するなら、以下のようにNugetから追加します。下の図のようにNugetからSystem.Dta.SQLiteを検索し、インストールしてください。

もし、Nugetから設置しない場合は、必要なDLLを参照追加すると使えます。

SQLiteを使ってみる

SQLiteをインストール(インストールと書きますが、実際は、DLLを参照に追加するだけです）したら、SQLiteは使うことができます。いつもの通り、サンプルを使って、説明します。

サンプルで使うデータクラス

データクラスは、他の設定ファイルのサンプルで使っているクラスと類似のクラスです。

src,1.1 data class
C#
1
2
3
4
5
6
7
8
9
10
public class ParamUserList
{
    public List<ParamUserInfo> Users { get; set; } = new List<ParamUserInfo>();
}
public class ParamUserInfo
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
}

SQLiteを使うためのAPI関数

SQLiteにアクセスするのは、SQLiteが提供するSQLiteConnectionクラスです。このクラスを使って、データベースファイルにアクセスし、読み書きができるようになります。SQLiteConnectionを扱う方法として２つの方法を書いてみました。基本は同じで、どちらの方法が良いかは、アプリケーションの設計によって変わるでしょう。

方法１.クラス内にで接続を維持する方法

設計ファイルを管理するので、設定情報（本記載ではデータクラスのこと）とデータベースを結ぶづけるための専用のクラスを作って、そのクラス内でSQLite接続を維持する方法です。

src.1.2.1 class
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
using System.Data.SQLite;
 
public class UserInfoSqlLIteControl : IDisposable
{
    private SQLiteConnection _conn = null;
 
    private bool _disposed = false;
 
    public UserInfoSqlLIteControl()
    {
        DbConnectionOpen();
        //DbCreateTable(); 初期生成が必要な時
    }
    ~UserInfoSqlLIteControl()
    {
        Dispose();
    }
 
    public void Dispose()
    {
        Dispose(true);
    }
    protected virtual void Dispose(bool disposing)
    {
        if (!_disposed)
        {
            if (disposing)
            {
                ConnectionClose();
            }
 
            _disposed = true;
        }
    }
 
    /// <summary>
    /// データベースに接続
    /// </summary>
    private  void DbConnectionOpen()
    {
        _conn = new SQLiteConnection();
        var dbfile = @"c:\temp\para_db_sqlite.db"; //データベースファイル名
        _conn.ConnectionString = $"Data Source={dbfile};Version=3;";
        _conn.Open();
    }
 
 
    /// <summary>
    /// Userテーブルの作成
    /// </summary>
    public void DbCreateTable()
    {
        SQLiteCommand command = _conn.CreateCommand();
        command.CommandText = "CREATE TABLE UserTable (idx integer primary key AUTOINCREMENT, ID INTEGER, Name varchar(100), Address TEXT NOT NULL)";
        command.ExecuteNonQuery();
    }
 
    /// <summary>
    /// Userレコードを挿入
    /// </summary>
    public void InsertUserData(ParamUserInfo user)
    {
        SQLiteCommand command = _conn.CreateCommand();
        var value = $"{user.ID},'{user.Name}','{user.Address}' ";
        command.CommandText = $"INSERT INTO UserTable (ID, Name, Address) VALUES ({value})";
        command.ExecuteNonQuery();
    }
 
    /// <summary>
    /// Userレコード更新:IDは変更しない
    /// </summary>
    public void UpdateUserData(int id, ParamUserInfo user)
    {
        SQLiteCommand command = _conn.CreateCommand();
        var value = $"Name = '{user.Name}', Address = '{user.Address}' ";
        command.CommandText = $"UPDATE UserTable set {value} WHERE ID = {id}";
        command.ExecuteNonQuery();
    }
    /// <summary>
    /// Userレコード更新:IDまで更新（全データ更新）
    /// </summary>
    public void UpdateUserDataIncludeID(int originalID, ParamUserInfo user)
    {
        var userOriginal = SelectUserDataIncludeIdx(originalID);
        var idx = userOriginal.Item1;
        SQLiteCommand command = _conn.CreateCommand();
        var value = $"ID ={user.ID}, Name = '{user.Name}', Address = '{user.Address}' ";
        command.CommandText = $"UPDATE UserTable set {value} WHERE idx = {idx}";
        command.ExecuteNonQuery();
    }
 
    /// <summary>
    /// Userレコードを削除
    /// </summary>
    public void DeleteUserData(int id)
    {
        SQLiteCommand command = _conn.CreateCommand();
        command.CommandText = $"DELETE FROM UserTable WHERE ID = {id}";
        command.ExecuteNonQuery();
    }
 
    /// <summary>
    /// Userレコードを取得
    /// </summary>
    public ParamUserInfo SelectUserData(int id)
    {
        var user = SelectUserDataIncludeIdx(id);
        return user.Item2;
    }
    public ValueTuple<int,ParamUserInfo> SelectUserDataIncludeIdx(int id)
    {
        ValueTuple<int, ParamUserInfo> retsult = new ValueTuple<int, ParamUserInfo>();
        var user = new ParamUserInfo();
        SQLiteCommand command = _conn.CreateCommand();
        command.CommandText = $"SELECT * FROM UserTable WHERE ID = {id}";
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
 
            retsult.Item1 = reader.GetInt32(0);    //idxの場合
            user.ID = reader.GetInt32(1);
            user.Name = reader.GetString(2);
            user.Address = reader.GetString(3);
        }
        retsult.Item2 = user;
 
        return retsult;
    }
 
    /// <summary>
    /// データベース接続を閉じる
    ///  Disposeによって自動Closeしていますが、このクラスを呼び出し側でusing使わないなら、
    ///  Dispose()作りではなく明示的にこの関数を呼び出してCloseしても良いです
    /// </summary>
    private void ConnectionClose()
    {
        if(_conn != null)
            _conn?.Close();
    }
}

この方法だと、データベースのアクセスするための接続（Connection）のインスタンス（サンプルでは、_connメンバー）がすでに生成されており、その接続を経由してSQLiteを扱いますので、毎回、接続しなおす必要がないです。

サンプルでは、データクラスの情報を丸ごと変更するための方法として、SelectUserDataIncludeIdx()を作成しました。この方法により、データクラスの値がどのように変わったとして、柔軟性を持つことができます。たとえば、ユーザのIDが変わることはあまりありえませんが、もし、変わったとしても、変更可能にするための関数の例です。

方法2.毎回インスタンスを生成する方法

この方法は、クラスの中に接続を維持するのではなく、必要な時、その都度、SQLiteとの接続を作成し、データベースにアクセスする方法です。

src.1.2.2 each class
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
using System.Data.SQLite;
 
public class UserInfoSqlLIteControl2
{
    const string _dbfile = @"c:\temp\para_db_sqlite.db"; //データベースファイル名
 
    public ParamUserList SelectUserData()
    {
        ParamUserList ret = new ParamUserList();
 
        using (SQLiteConnection con = new SQLiteConnection($"Data Source={_dbfile};Version=3;"))
        {
            con.Open();
            var sql = $"SELECT * FROM UserTable";
 
            SQLiteCommand com = new SQLiteCommand(sql, con);
            using (SQLiteDataReader reader = com.ExecuteReader())
            {
                while (reader.Read() == true)
                {
                    var user = new ParamUserInfo();
                    //reader.GetInt32(0);    //idxの場合
                    user.ID = reader.GetInt32(1);
                    user.Name = reader.GetString(2);
                    user.Address = reader.GetString(3);
                    ret.Users.Add(user);
                }
            }
        }
        return ret;
    }
}

呼び出し側のサンプル

src.1.2.3 call
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
private static void TestUserSqLite()
{
    //var userList = Param_Db_SqLite.MakeDummy();
    var userEach = new UserInfoSqlLIteControl2();
    var userList = userEach.SelectUserData();
 
    using (var sqlite = new UserInfoSqlLIteControl())
    {
        foreach (var item in userList.Users)
        {
            Console.WriteLine($"Select {item.ID},{item.Name},{item.Address}");
 
            //データを削除
            sqlite.DeleteUserData(item.ID);
 
            //データを追加
            sqlite.InsertUserData(item);
            var user = sqlite.SelectUserData(item.ID);
            Console.WriteLine($"Insert {user.ID},{user.Name},{user.Address}");
 
            //データを更新
            item.Name = item.Name + " Naruhodo";
            sqlite.UpdateUserData(item.ID, item);
            user = sqlite.SelectUserData(item.ID);
            Console.WriteLine($"Update {user.ID},{user.Name},{user.Address}");
 
            //IDを含めすべて更新
            int origianlID = item.ID;
            item.ID += 1000;
            item.Name = item.Name + " 2";
            sqlite.UpdateUserDataIncludeID(origianlID, item);
            user = sqlite.SelectUserData(item.ID);
            Console.WriteLine($"Update2 {user.ID},{user.Name},{user.Address}");
            Console.WriteLine("");
 
        }
    }
}

 

実行した結果

上記のサンプルを実行した結果は以下のようになります。

SQLiteデータベースの中身を見たい場合はSQLite Browser

SQLiteのデータベースはファイルでデータを保存（サンプルでは、 para_db_sqlite.db）します。その保存したファイルを見たり、直接編集したい場合は、SQLite Browserからダウンロードして使います。

ダウンロードした後SQLite Browserをインストールし、データファイル(para_db_sqlite.db)を開くと、下のような画面になります。このツールを使って、データベースを直接作成したり、削除したり、編集することもできます。

SQLiteを使った場合の設定ファイルのバージョンアップ

SQLiteを使った場合の設定ファイルのバージョンアップは、一言では言えません。結局、データベースの設計内容によって、次のバージョンアップに影響するからです。上記のようにクラスと一致する形のデータベース設計にした場合、何らかの形で影響を受けるでしょう。クラスメンバが増えたら、データベースも変更しないといけなくなります。ただ、データクラスをそのままデータベースに書き込んでいるわけでなく、変換クラス（Conveterクラス）を利用するので、バージョンアップによってメンバが増えたとか変更されても、ある程度、柔軟に対応できます。

バージョンアップによる影響を少なくするための対策

上記で書いた通り、設定クラスとデータベースの各要素（カラムと言います）を１：１でマッチングした設計をすると、バージョンアップによる影響が大きいことから、かなり、柔軟な設計をする方法があります。要するに、設定クラス（データクラス）に依存しない、汎用データベーステーブルを設計し、汎用テープるを使う方法です。例えば、ブログやサイト構築で人気のあるWordpressの場合、このような汎用テーブル(例:wp_options)を作成し、設定情報は、なんでも、このテーブルに詰め込んでいます。Wordpress標準機能はもちろんのこと、ユーザが作成したプラグインの設定情報も、この汎用テーブルに保存します。以下、実際のWordpressのOptionsテーブルの構造です。

  • option_name：データクラスのメンバー名を保存します。
  • option_value：データクラスのメンバに該当する値です。

このように作っておくと、INIファイルのようにkey=value型で値を書き込み、読みだすこともできます。利用するクラスで、データをクラスメンバーに詰め替える必要はありますが、自由度はかなり高くなります。どんな構造のクラスであっても詰め込むことができます。

汎用テーブルを利用する場合の注意点

option_nameが重複することもありえるので、そのための対策を入れると良いです。例えば、このテーブルにカテゴリ（データクラス名のような値）を入れるとかの方法もあります。すると、メンバー名が重複するトラブル防止につながります。

また、汎用テーブルであることは、それをデータクラスに詰め込むための実装、実行時間に影響することです。設定クラスとデータベースの各要素（カラムと言います）を１：１でマッチングしているわけでないので、変換クラスが必ず必要になります。

まとめ

データベースSQLiteを使って設定ファイルを使うことは、勧めはしませんが、どうしても必要であれば、データベース中でもSQLiteは高速、APIによって楽に使えるなどの長所があるので、SQLiteを勧めします。

また、データベースは、上の「バージョンアップによる影響を少なくするための対策」に書いたことを参考に、注意点に書いた内容で問題なければ利用することで、バージョンアップによる影響を減らすことができます。