C#-SQLite データベース データアクセスクラスを作成しました。

この記事では、Visual Studio 2019 を用いてアプリ開発において、データベースに　SQLite を使用する場合に必要になるであろう、アクセス用ラッパークラスを作成しました。

環境

Windows 10 Pro
Visual Studio 2019 Comunity

対象読者

• C# で SQLite を使おうとしている方
• C# を勉強している方

基本的な設計

他のDBにも同じメソッドで接続できるように、ジェネリック型で定義したインタフェースを使用しました。

コード

インタフェース

IDatabaseConnectors.cs

using System;
using System.Collections.Generic;

namespace FormApp1.DatabaseConnectors
{
    interface IDatabaseConnectors<Reader> : IDisposable
    {
        // SQL発行関数（SELECT）
        Reader ExecuteQuery(string query);
        Reader ExecuteQuery(string query, Dictionary<string, object> keyValuePairs);
        // SQL発行関数（INSERT, UPDATE, DELETE）
        void ExecuteNonQuery(string query);
        void ExecuteNonQuery(string query, Dictionary<string, object> keyValuePairs);
        // 初期化
        void Initialize();
        // コネクションストリングの構築（DB毎に異なるので）
        void BuildSqlConnectionString();
        void Disconnect();
        // トランザクション管理
        void BeginTransaction();
        void CommitTransaction();
        void RollBack();
    }
}

IDatabaseConnectorsのポイント

• 他のRDBMSでも使えることに配慮しています。
• BuildSQlConnectionString() でRDBMS毎に異なるコネクションストリングをクラスで実装します。

クラス実装

SqliteDatabaseConnector.cs

using System;
using System.Data.SQLite;
using System.Collections.Generic;

namespace FormApp1.DatabaseConnectors
{
    public class SqliteDatabaseConnector : IDatabaseConnectors<SQLiteDataReader>, IDisposable
    {
        // Nuget で SQLite 関連は読み込み済み
        private SQLiteConnection _SQLiteConnection;
        private SQLiteTransaction _SQLiteTransaction;
        private string _dbConnectionString = "";
        // SQLiteDB ファイルのパス
        private readonly string _path;
        // SQLiteDB ファイルの名称
        private readonly string _fileName;

        // コンストラクタ
        public SqliteDatabaseConnector(string path, string fileName)
        {
            _path = path;
            _fileName = fileName;
            Initialize();
        }
        // 初期化：DB接続を確立させる
        public void Initialize()
        {
            BuildSqlConnectionString();
            _SQLiteConnection = new SQLiteConnection(_dbConnectionString);
            _SQLiteConnection.Open();
        }
        // コネクションストリングを作成
        public void BuildSqlConnectionString()
        {
            // DB ファイルネームが指定されなかった場合は、"default" を定義値にします。
            string fName = (_fileName.Length == 0) ? "default" : _fileName;
            if(_path.Length == 0)
                _dbConnectionString = $"Data Source ={fName}.db";
            else
                _dbConnectionString = $"Data Source ={_path}/{fName}.db";

        }
        public void Dispose()
        {
            this.Disconnect();
            this._SQLiteConnection.Dispose();
            this._SQLiteTransaction.Dispose();
        }
        public void Disconnect()
        {
            _SQLiteConnection.Close();
        }


        public void BeginTransaction()
        {
            this._SQLiteTransaction = this._SQLiteConnection.BeginTransaction();
        }
        public void CommitTransaction()
        {
            if (this._SQLiteTransaction.Connection != null)
            {
                this._SQLiteTransaction.Commit();
                this.Dispose();
            }
        }
        public void RollBack()
        {
            if (this._SQLiteTransaction.Connection != null)
            {
                this._SQLiteTransaction.Rollback();
                this.Dispose();
            }
        }

        // SQL発行
        public void ExecuteNonQuery(string query)
        {
            this.ExecuteNonQuery(query, new Dictionary<string, object>());
        }
        public void ExecuteNonQuery(string query, Dictionary<string, object> keyValuePairs)
        {
            using(var cmd = new SQLiteCommand())
            {
                cmd.Connection = _SQLiteConnection;
                cmd.Transaction = _SQLiteTransaction;
                // パラメータをバインド
                foreach (KeyValuePair<string, object> item in keyValuePairs)
                {
                    if ( query.IndexOf(item.Key) > 0 ) 
                        cmd.Parameters.Add(new SQLiteParameter(item.Key, item.Value));
                }
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }
        }
        public SQLiteDataReader ExecuteQuery(string query)
        {
            return this.ExecuteQuery(query, new Dictionary<string, object>());
        }
        public SQLiteDataReader ExecuteQuery(string query, Dictionary<string, object> keyValuePairs)
        {
            SQLiteDataReader reader;
            using (var cmd = new SQLiteCommand())
            {
                cmd.Connection = this._SQLiteConnection;
                cmd.Transaction = this._SQLiteTransaction;
                // パラメータをバインド
                foreach (KeyValuePair<string, object> item in keyValuePairs)
                {
                    if (query.IndexOf(item.Key) > 0)
                        cmd.Parameters.Add(new SQLiteParameter(item.Key, item.Value));
                }
                cmd.CommandText = query;
                reader = cmd.ExecuteReader();
            }
            return reader;
        }

    }
}

SqliteDatabaseConnectorのポイント

• クエリのバインド文字列を Dictionary 形式で渡して、汎用性を持たせようとしています。

今後

• Entity-Model を作成し、SqliteDatabaseConnectorを経由してDB登録します。
• 別途、Column情報などを定義するクラスを書けば、汎用性が増すと思うので改良します。