WPF MVVM L@bo #5 ~ ようこそ Dapper 至上主義の DataAccess へ ~

← 前回記事【WPF MVVM L@bo #4 ~ DB が見えるのは嫌なので 3 階層 に AbstractFactory したいと思います。 ~】

前回は 3 階層アーキテクチャのデータ層から AbstractFactory パターンで DBMS への依存を取り除く方法を紹介したので、今回は Micro-O/RM の Dapper を使用して SQLite からデータを読み書きする方法を紹介します。

尚、この記事は Visual Studio 2019 Community Edition で .NET Core 3.1 以上 と C# + Prism 7.2 以降 + ReactiveProperty + Livet + MahApps.Metro + Material Design In XAML Toolkit + SQLite を使用して 、WPF アプリケーションを MVVM パターンで作成するのが目的で、C# での基本的なコーディング知識を持っている人が対象です。

データ層の DataAccess クラス

前回 はプロトタイプアプリを fig. 1 のような 3 階層 MVVM 構造で作成した場合のアプリケーション層から DBMS を隠蔽する方法を紹介しました。

fig.1 3階層 MVVM

前回 紹介した HalationGhostDbAccessBase はデータ層の裏方的立場のクラスなので、実際にアプリケーション層とやり取りする DataAccess クラスは HalationGhostDbAccessBase から継承して新規に作成します。

今回はその新規で作成した DataAccess クラスから SQLite のデータを読み書きする方法を紹介します。

DataAccess 紹介用サンプルアプリ

DataAccess クラスの紹介用にプロトタイプアプリへ『DapperSample』から始まるサンプル用プロジェクト群を追加しました。(DapperSample ソリューションフォルダにまとめています)
サンプルの実行には DapperSample をスタートアッププロジェクトに設定してください。

併せて DapperSample プロジェクト内に SQLite の DB ファイルも追加しています。
データの中身はこのサイトではお馴染み BLEACH のキャラクターデータを以下のように入力済みです。
(以下は入力データの一部)

IDキャラクター名フリガナ誕生日所属斬魄刀
1黒崎 一護くろさき いちご7月15日151
2朽木 ルキアくちき ルキア1月14日132
3井上 織姫いのうえ おりひめ9月3日15
4石田 雨竜いしだ うりゅう11月6日15
5茶渡 泰虎さど やすとら4月7日15
6阿散井 恋次あばらい れんじ8月31日63
7黒崎 一心くろさき いっしん12月10日154
8浦原 喜助うらはら きすけ12月31日165
9四楓院 夜一しほういん よるいち1月1日16
10京楽 春水きょうらく しゅんすい7月11日16

# BLEACH のキャラクターって漢字だし所属もあるしそれなりに認知度もあるんで
# サンプルデータに使用するには管理人的に使いやすいんですw(独り言)

SQLite から データを取得する

追加したサンプルアプリの実行結果は fig. 2 のようになります。

fig.2 Dapper から dynamic 型で取得

サンプル自体は【Dynamic 型で取得ボタン】Click でデータを取得して簡易コンソールに出力するだけのシンプルなもので、src. 1 は MainWindow の VM です。

src. 1 の 24行目で DI コンテナから Resolve メソッドでアプリケーション層の Service インタフェースを取得して、データの取得・表示処理を全てアプリケーション層へ委譲しています。

今まで Prism の DI コンテナからオブジェクトを取得する方法はコンストラクタインジェクションしか紹介していませんでしたが、src. 1 の 24 行目のように PrismApplication の Container プロパティから任意のタイミングで取得(生成)することもできます

アプリケーション層

src. 2 は VM から呼び出されるアプリケーション層の DapperSampleService で、14 行目では DI コンテナからインジェクションされた IRepositoryFactory から DataAccess クラスを生成しています。
RepositoryFactory は DataAccess クラスを new して返すだけの単純なクラスです。

DataAccess クラスの継承元である HalationGhostDbAccessBase は内部に DB への Connection を持ち、コンストラクタで接続して、Dispose で切断しています。
このようにオブジェクトの生存期間を外部からコントロールする必要があるクラスを『状態を持つクラス』と言い、基本的に DI コンテナへ登録する対象には向いていません。

とは言っても『状態を持つクラス』を DI したい場合もあるため、状態を持たない Factory クラスをインジェクションして Factory からインスタンスを受け取るようにしています。
参考:『単体テストと副作用 – 第5話 オブジェクト指向への反乱 – Qiita

そして IRepositoryFactory で取得する DataAccess クラスは Repository パターンで実装します。

DataAccess クラスを Repository パターンで実装する

Repository パターンとはデータの操作に関連する実装を、抽象化したデータ層のクラスに委譲してアプリケーション層から切り離すことで保守や拡張性を高めるパターンで、Microsoft のインフラストラクチャの永続レイヤーの設計 等で解説されています。

上記 Microsoft の記事『Repoitory は各集約または集約ルートに 1 つの Repoitory クラスを作成する必要がある』と書かれているように、Repository パターンの紹介記事ではまず src. 3 のような Generic 型パラメータを取る汎用 Repository インタフェースを定義して… と言うような内容を目にすることが多い印象です。

src. 3 のような汎用 Repoitory インタフェースを定義したい気持ちは理解できますが、管理人的にはイマイチ納得できません。
納得できない理由として Delete や Update はトランザクション系のデータには不要な場合も多々ありますし、キーを指定してデータを取得する GetById メソッドは文字列のキーにしか対応できない、複合キーにも対応できない等…インタフェースとしてして定義するには不完全だと思うからです。

但し、汎用 Repoitory インタフェースを全否定している訳ではなくマスタ系データのように汎用 Repoitory インタフェースを定義した方が良い場合もあるので、その辺りは臨機応変にすべきだと思っています。

ここでは src. 3 のような汎用 Repoitory インタフェースは作成せず、src. 4 のような BleachCharacter 用の Repository インタフェースを作成します。

言うまでもありませんが、Repository に定義するメソッドの戻り値は DataTable や DataReader ではなくsrc. 4 のようにエンティティ系モデルで返すように定義します。
データ層のインタフェースに ADO.NET 関連のクラスを使用しないことでアプリケーション層からは DBMS がほぼ完全に隠蔽されるようになりますが、その反面 DB から取得した値をエンティティ系モデルに積み替える処理が必要になります。

O/R マッパー

DB から取得した値をオブジェクトに設定することを O/RM(Object-relational mapping)と呼び、O/RM を実現するためのライブラリ等は O/R マッパーと呼ばれます。
.NET Core に対応した O/R マッパーには Microsoft 製の Entity Framework や Java から移植された NHibernate 等があります。

O/R マッパーは以下のような特徴を持ちます。

  • SQL の自動生成
  • オブジェクトへの値マッピング
  • DB操作のラッピング
  • ソースコード自動生成

中でも特に賛否両論が多いのは【SQL の自動生成機能】で、業務系ではよく見かけるホスト由来の DB のように DB 設計がイケてない場合や、そもそもリレーションが張られていないような場合はパフォーマンスが良くない SQL が生成されることも多く、やっぱり SQL は手書きが 1 番!と言う意見も多いようです。

とは言え、オブジェクトへのマッピング処理部分だけは欲しい!と言う意見も同時にあり、 Micro(マイクロ)O/R マッパー(以降 Micro-O/RM)と呼ばれるジャンルのライブラリも出て来ました

Micro-O/RM は上記 O/R マッパーの特徴の内【オブジェクトへの値のマッピング】に特化して『SQL の自動生成』や『ソースコード自動生成』等の機能を省いた(プロダクトによって異なります)ものが多いため、クエリの実行速度は手書きした SQL に依存します。

実際、オブジェクトと DB の値をマッピングするだけなら Reflection でも可能なのでオレオレ O/R マッパーを作成する事も難しいことではありませんが、DB から取得する値は数千~数万件(もっと)になる場合もあるので、オブジェクトマッピングのパフォーマンスが処理全体のパフォーマンスに影響を及ぼす場合もあります。

次項からは 既に何番煎じなのか分からないくらい紹介されている Micro-O/RM の Dapper を紹介します。

Micro-O/R マッパー Dapper

.NET 用の Micro-O/RM では最も有名と言って差し支えない DapperStack Overflow の中の人が Stack Overflow 自体のパフォーマンス改善を目的に作成した Micro-O/RMで、シンプルな構文と高速なオブジェクトへのマッピングが特徴です。
Dapper.NET Core・Framework に両対応していて、現時点(2020 年 3 月現在)も超巨大サイトである Stack Overflow で鍛え続けられている信頼度の高い Micro-O/R マッパーと言えます。
既にオレオレ Micro-O/RM を作成していても 1 度試してみる価値はあると思います。

Dapper のパフォーマンスは GitHub の Readme.md で公表されていて、ADO.NET の SqlCommand を直接呼出した場合(ベンチマーク上から 3 番目の Hand Coded)とほとんど変わらない(ベンチマーク上から 6 番目)実行速度を誇ります。

現時点(2020 年 3 月現在)で Dapper が公式にサポートしている DBMS は以下の製品です。

  • Oracle
  • SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
  • SQL Server Compact Edition
  • Firebird

上記の通り Dapper は商用・非商用を含めてメジャーな DBMS をサポートしているので、.NET Core(Framework)を採用した大半の業務系開発に使用できると言えます。

Dapper のインストール

Dapper は Nuget から入手できるので、fig. 2 のように『ソリューションの Nuget パッケージの管理ウィンドウ』から【dapper】を検索すると見つかります。

fig.2 Dapper Nuget パッケージ

赤枠で囲んだ【Dapper】をデータ層に該当するプロジェクトへインストールすると使用できます。



Dapper で SQLite からデータを Select

Dapper の基本操作は Dapper Tutorial でも書かれていますが、一応このエントリでも紹介します。

以降、Dapper から SQL を発行してクラスへマッピングする例を紹介していきます。
このエントリでは DBMS に SQLite を使用してマッピングしていますが、Dapper がサポートしている DBMS であれば全て同じ方法でマッピングできます。(バインド変数の書き方『:』、『@』が違う程度です)

SQLite しかできない事はほとんど載せていません。(SQL 自体の方言の違いはあります)

Dapper からレコードを dynamic 型で Select する

src. 5 はこのエントリの本題であるキャラクター用の DataAccess(Repository)クラスです。

このサンプル用に作成した SQLite DB は BLEACH のキャラクターデータをキャラクター Table、組織 Table、斬魄刀 Table の 3 つに分けています。
src. 5 の SQL は分割したテーブルを表示用に Join しているだけの単純な Select 文ですが、先頭 n 件のデータのみ取得するための【LIMIT 句】を 24 行目で指定してます。
Oracle では ROWNUM、SQL Server では Top n を指定した場合と同じく、SQLite ではテーブル名の後ろに【LIMIT n】を追加すると取得件数を制限できます。

Dapper で実際にデータを Select しているのは 26 行目の『this.Connection.Query(sql.ToString() …);』の部分です。
Dapper は DB へアクセスするためのメソッドを全て IDbConnection の拡張メソッドとして定義しているので有効な DbConnection オブジェクトさえあれば 26 行目のように呼び出すことができます。

src. 5 の CharacterRepository が継承する DapperSampleDataAccessBase前回の #4 で紹介した HalationGhostDataAccessBase を継承していて、このサンプルアプリ内で作成する DataAccess は全てこの DapperSampleDataAccessBase を継承して作成します

fig.3 DataAccess の継承関係

データ層のクラスを fig. 3 のような継承関係で作成すると HalationGhostDataAccessBase から要求される設定は DapperSampleDataAccessBase が一手に引き受けるので、アプリの DataAccess は DBMS を全く意識せずアプリケーションロジックの実装のみに注力できます。

そして、SQL の発行は全て Dapper を通して行うため、HalationGhostDataAccessBase が protected で公開している DbConnection さえあれば良いことになります。
そのため DbCommand や DbDataReader の公開は不要ですが、業務系システム等では SQL のログを取るような要件が含まれる場合があります。

そのような場合に DbConnection を公開してしまうと SQL は全てアプリケーションレベルの DataAccess から直接発行されてしまうため、ログの取得処理の共通化が難しくなります。
SQL ログの取得処理が必要な場合等は DbConnection を公開せず HalationGhostDataAccessBase 等で Dapper をラップしたメソッドを定義する方が良い場合もあると思います。
(Dapper のメソッドはオーバーロードが多いのでラップメソッドの実装も大変だとは思います…)

少し横道に逸れましたが、src. 5 のように Dapper からレコードを Select する Query メソッドは以下ような Query ~ から始まるバリエーションが定義されています。

  • Query
  • QueryFirst
  • QueryFirstOrDefault
  • QueryMultiple
  • QuerySingle
  • QuerySingleOrDefault

上記のメソッドにはそれぞれ型パラメータあり・無し、Async あり・無し版のメソッドも用意されていて、型パラメータを設定しない場合は src. 5 のように dynamic 型又は、IEnumerable<dynamic> 型で取得できます。

DB の値を Dapper から dynamic 型で取得すると src. 2 21 ~ 22 行目のように Select 文で指定したフィールド名がプロパティのように利用できます。(※ src. 2 はかなり上の方で紹介しています)



Dapper からレコードを任意の型で Select する

Dapper は src. 6 のように Query メソッドに型パラメータを指定すると、値をマッピングしたクラスのインスタンスが返されます。

型パラメータに指定したクラスへマッピングするには【フィールド名 == クラスのプロパティ名】になっている必要があるので、src. 6 のようにマッピング先のプロパティ名に合わせて【AS 句】を指定します。
※ 今回作成したサンプル DB のフィールド名は業務系システムでよく見る『全て大文字 + ハイフン区切り』の命名規則で作成しました。

加えて Dapper は src. 6 27 行目のようにバインド変数にマッピングすることもできます
(SQLite のバインド変数は【:】、【@】のどちらでも指定可能)
Dapper のバインド変数マッピングはクラスのプロパティマッピングの場合と同じく、名前が一致する項目をマッピングするため、バインド変数の『Id』には『10』が設定されます。
※ 28 行目のように匿名型を渡すこともできます。

バインド変数のマッピングに実在・匿名どちらのクラスを指定した場合でも fig. 4 のような実行結果になります。

fig.4 BleachCharacter 型で取得

バインド変数用のパラメータを ADO.NET 標準の方法で設定するのは結構記述が面倒ですが、Dapper では非常に簡単に記述できると思います。

更に Dapper は src. 7 のように【IN 句】に List 型をマッピングすることもできます。

ここではバインド変数のパラメータに Generic の List を渡していますが、単純な配列(おそらく IEnumerable を継承しているクラスであれば OK)でも渡せます。

但し、注意点が 1 つあります! src. 7 の 24 行目をよく見てください。
【IN 句】の右辺に括弧が無い事に気が付きましたか?
おそらく Dapper 内部で括弧を補完しているのだと思いますが、いつものクセで括弧を書いてしまうと SQL 解析エラーになるので気を付けてください。(管理人は 3 分程悩まされました)

src. 7 の実行結果が fig. 5 です。

fig.5 IN 句のバインド変数にマッピング

SQL を書く機会が多い人であれば『Dapper って便利そう』と感じてもらえるかもしれませんが、この連載で紹介しているような WPF アプリで使用する場合に重大な問題が 1 つあります。

Dapper で Value Object にマッピング

WPF アプリで Dapper を使用した場合の問題とは、src. 8 のように ReactiveProperty で定義したプロパティにマッピングできないと言う問題です。

基本的に Dapper はプロパティを値型とみなしてマッピングするため、ReactiveProperty のような参照型のプロパティに直接マッピングすることはできないようです。

このような問題は ReactiveProperty だけでなく DDD(ドメイン駆動設計)等でもお馴染みの Value Object をプロパティの型に指定する場合も同じですが、Dapper に用意されている SqlMapper.TypeHandler を継承して src. 9 のようにマッピング定義を追加するとマッピングできるようになります

メソッドコメントの通り Parse メソッドはオブジェクトへのマッピングSetValue メソッドはバインド変数へのマッピング時に呼び出されます。
サンプルアプリでは src. 9 に加えて ReactivePropertySlim<string> 型の TypeHandler クラスも作成しています。

TypeHandler クラスを作成したら Dapper に TypeHandler を登録する必要があります。
具体的には src. 10 のようにアプリ起動時に TypeHandler クラスを Dapper へ登録します。

スペースの都合上 1 箇所にまとめていますが、DapperSampleApplicationLayerModule はアプリケーション層、DapperSampleDataAccessBase はデータ層のクラスです。

DapperSampleApplicationLayer プロジェクトは Prism Module プロジェクトテンプレートから作成したので、アプリ起動時に OnInitialized メソッドが呼ばれます
OnInitialized メソッドでは DapperSampleDataAccessBase.InitializedSqlMapper メソッドを呼び出してマッピング設定を登録しています。

OnInitialized メソッドで直接マッピング設定を登録する形でも構いませんが、アプリケーション層用の DapperSampleApplicationLayer プロジェクトに Dapper の参照を追加したくなかったので src. 10 のような方法を採っています。

Value Object や ReactiveProperty で定義したプロパティは読み取り専用で定義することが多いですが、TypeHandler クラスは src. 9 15 行目の通りプロパティのインスタンスを返すのでプロパティの定義に setter も追加する必要があるので、src. 11 のように TypeHandler を指定した型のプロパティに setter も追加します。

一般的な Value Object 等の場合であればここまでマッピングできるはずですが、ReactiveProperty の場合は少し注意が必要です。

ReactiveProperty で定義したプロパティを Subscribe しているような場合は自動実装プロパティではなく src. 12 のような完全記述プロパティに変更する必要があります。

重要なのは 17、30 行目の Dispose で、Subscribe しているプロパティでは最悪メモリリークの危険もあるので、元の ReactiveProperty は必ず Dispose しておく方が良いでしょう。
そして、src. 12 には書いていませんが、元々 Subscribe していたプロパティであれば再度 Subscribe する必要もあります

src. 8 のように ReactiveProperty を単純に new しているだけのプロパティなら src. 11 のように【set;】を追加するだけで問題ないと思いますが、少なくとも Subscribe しているプロパティだけは src. 12 のように変更した方が良いと思います。
但し、管理人も DB から取得した値がマッピングされる所までしか確認していないので、VM に双方向バインドした場合の動作は分かっていません。機会があれば又、確認したいと思います。

Header – Detail モデルへのマッピング

Dapper は単一クラスへのマッピングだけでなく複数のクラスに同時マッピングする事もできます
src. 13 は業務系システムでもよく見かける『ヘッダ – 明細形式』等と呼ばれる複合(入れ子になった)オブジェクト(SoulSocietyParty)へのマッピング例です。

スペースの都合上、アプリケーション層と DataAccess クラスを一緒に紹介しています。

SQL は所属 Table とキャラクター Table を Join して『護廷十三隊別所属キャラクターリスト』を取得するために Dapper の型パラメータ付き Query メソッドを呼び出しています

Query メソッドの型パラメータシグネチャは『<TFirst, TSecond, TReturn>』で定義されていて、それぞれ【<1 つ目のマッピング対象クラス, 2 つ目のマッピング対象クラス, マッピング後の戻り値クラス>】を表します
Query メソッドはマッピング対象クラスを 7 個まで指定できるオーバーロードが用意されています。

複数のオブジェクトに値をマッピングできても生成後のオブジェクトをどこにセットするかは自動で判断できない(判断するのは難しい)ため、親子関係を設定するための匿名メソッドを Query の第 2 パラメータへ指定できるようになっていて src. 13 の 32 ~ 46 行目がその匿名メソッドです。

第 2 パラメータに指定する匿名メソッドは DB から取得した行ごとに呼び出されるので、Query メソッドの外側に宣言した Dictionary に親になるオブジェクト(SoulSocietyParty)を退避しながら親子関係を設定しています。

この匿名メソッドで重要なのは、Query の戻り値は読み捨てている点で、GetCharactersByParty の最終的な戻り値は匿名メソッド内で親オブジェクトを退避していた Dictionary から取得しています。
難しいことをしている訳ではありませんが、他人が書いたコードだと気が付かない場合もあると思うので敢えて書いておきます。(実際、管理人はなかなか気が付きませんでした…)

『ヘッダ – 明細形式』で取得した結果が fig. 6 です。

fig.6 ヘッダ – 明細形式のマッピング

全キャラクターを出力する必要もないので取得する隊を絞っていますが、『護廷十三隊別所属キャラクターリスト』が出力されています。

又、Dapper の Query メソッドで複数のオブジェクトをマッピングする場合、省略可能パラメータの【splitOn パラメータ】を指定する必要もあります

splitOn パラメータ は 1 つ目と 2 つ目のオブジェクトをどのフィールド(Select の)で区切るかを指定するパラメータで、src. 13 の場合は BleachCharacter.Id が区切り位置になるため【Id】を指定していて、3 つ以上のクラスにマッピングする場合は【Id,ZanpakutoId】と言うようにカンマで区切って指定します

ですが、src. 13 の場合、splitOn パラメータをコメントアウトして実行しても正常に動作します。
これは Dapper では【Id】と言うフィールド名は特別扱いなので、フィールドリスト内の【Id】は無条件でクラス間の区切りに使用されると言う仕様があるからだそうです。

Dapper のマッピングについては『Dapper のクエリ – Qiita』等でも紹介されています。
DB からデータを取得する方法については大体紹介し終えたので、続いて登録・追加・削除を一気に紹介します。



Dapper で SQLite へデータを Insert

DB へ Insert する場合も Select する場合と同じで、バインド変数名とクラスのプロパティ名を一致させると src. 14 のようにマッピングされます。

src. 14 では非同期版の Query を呼んでいますが、同期版の場合も変わりません。
22 行目のように DB へ登録する値をセットしたクラスのインスタンスを Query メソッドに渡すとバインド変数とプロパティがマッピングされ Insert されます。
ID 列はオートナンバー型なので登録対象フィールドからは除外しています

併せて 34 行目も見てください。管理人は今回調べるまでは知りませんでしたが、34 行目のように登録するクラスの List を渡すとループを書かなくても List 内の全メンバを Insert してくれます。

Insert、Update、Delete の場合は Query ではなく Execute を呼ぶ程度の違いなので Select のマッピングが分かっていれば迷う事はほとんど無いと思います。
34 行目のように List を渡せば全メンバが対象になるのも Insert、Update、Delete で共通です。

但し、全メンバが対象になると言っても一括登録・更新・削除される訳ではなく、メンバ数分 SQL が発行されるので、例えば Delete の場合なら 45 行目のようにすると SQL 発行は 1 回で済みます。

src. 14 を呼び出すアプリケーション層が src. 15 です。

src. 15 は単純に Insert しているだけでなく以下の処理を実行しています。

  • キャラクターテーブルの最大値 ID を取得
  • 登録対象キャラクターとふりがなが一致するレコードを削除
  • キャラクターを Insert
  • 最初に取得した最大値 ID より大きいキャラクターを Select
  • 取得したキャラクターをコンソールへ表示

実行結果が fig. 7 です。

fig.7 Insert の実行結果

『データをInsert!ボタン』を Click するごとに ID が更新されるので、削除して Insert しているのが分かると思います。
又、ここでは紹介していない ExecuteScalar メソッド等も使用しているので紹介したい所ですが、かなり長いエントリになってしまったので Dapper の紹介はここまでにします。

とりあえず #5 まで続けてきた WPF MVVM L@bo シリーズですが今回で一旦休止します。
アクセスログを見ていてイマイチ反応が良くない(苦笑)のも理由ですが、新規で連載をスタートした WPF UI Gallery にウエイトをかけたいと思っているので手が回らなくなったのが 1 番の理由です。

WPF MVVM L@bo シリーズで紹介してきたプロトタイプアプリを途中で投げ出すつもりは無いので、ボチボチ手は入れていこうと思っているのでその内再開する予定です。
万が一、再開希望のようなコメントでももらえると再スタートが早まるかもしれませんw

とりあえず WPF MVVM L@bo シリーズは今回で一旦休止しますが、今回紹介したサンプルと現時点までのプロトタイプアプリのソースコードはいつもの通り GitHub リポジトリ に上げています。

次回は WPF UI Gallery case: 1-2 を公開する予定です。

 

 

あわせて読みたい

コメントを残す

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

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

%d人のブロガーが「いいね」をつけました。