1. はじめに
- Dapperを使用してDBのデータを取得したい
- 動的Where句をSQL Builderでロジックに記述せずにSQLだけで実現したい
- 動的Where句で、かつIN句の場合に発生した問題(口述）をなんとか回避したい
2. 開発環境
- C#
- .NET 6
- Visual Studio 2022
- SQLite
- DB Browser for SQLite
- Dapper (NuGet)
- System.Data.SQLite.Core (NuGet)
- ToString.Fody (Nuget)
3. 開発環境の準備
3.1. Dapperのインストール
- NuGetより
Dapperをインストールする
3.2. System.Data.SQLite.Coreのインストール
- NuGetより
System.Data.SQLite.Coreをインストールする - DBは今回SQLiteを使用するため準備する
3.3. ToString.Fodyのインストール
- NuGetより
System.Data.SQLite.Coreをインストールする - DBの検索結果を簡単に確認するために準備する
3.3. DB Browser for SQLiteのインストール
- 下記サイトよりインストーラーをダウンロードして、インストールする
- SQLiteのDB参照のために準備する
4. テストDBの作成
4.1. データベースの作成
-
DB Browser for SQLiteを起動して、新しいデータベース作成をクリックする
-
任意のフォルダ、ファイル名で保存する
4.2. テーブルの作成
- SQLの実行タブよりCREATE文を入力して、実行ボタンをクリックする
CREATE TABLE "Person" (
"Id" varchar(8) NOT NULL,
"Name" varchar(40) NOT NULL,
"Age" INTEGER,
PRIMARY KEY("Id")
)
4.4. テストデータの投入
-
データ閲覧タブより新規レコード挿入ボタンをクリックする
-
カラムに値を入力後に、変更を書き込みするボタンをクリックしてコミットする
5. サンプルコード（固定SQL）
5.1. DTOクラス
-
[ToString]属性を追加することでプロパティを含めて出力することができる
PersonDto.cs
[ToString]
public class PersonDto
{
public int Id { get; set; }
public string? Name { get; set; }
public int Age { get; set; }
}
5.2. DBアクセスクラス（抜粋）
- 通常の値のDynamic Object以外にIN句はList型を渡すと勝手に展開してくれるので便利だ
using Dapper;
using System.Data.SQLite;
using System.Diagnostics;
// プロジェクト直下の同クラスと同じ階層にDBを格納
string ConnectionString = @"Data Source=../../../test.db;";
// SQLiteDB接続
using (var connection = new SQLiteConnection(ConnectionString))
{
// DBオープン
connection.Open();
// トランザクションを開始（今回は未使用）
using (var tarn = connection.BeginTransaction())
{
// SQL文
string sql = "SELECT * FROM Person WHERE Id IN @Id";
// Dynamic Object作成（IN句の場合）
var condtion = new
{
Id = new List<int>() { 1, 3 }
};
// Dapperで検索
var result = connection.Query<PersonDto>(sql, condtion);
// 検索結果を確認
foreach (var person in result)
{
Debug.Print(person.ToString());
}
}
// DBクローズ
connection.Close();
}
5.3. 動作確認
6. サンプルコード（動的Where句）
6.1. DBアクセスクラス（抜粋）
using Dapper;
using System.Data.SQLite;
using System.Diagnostics;
// プロジェクト直下の同クラスと同じ階層にDBを格納
string ConnectionString = @"Data Source=../../../test.db;";
// SQLiteDB接続
using (var connection = new SQLiteConnection(ConnectionString))
{
// DBオープン
connection.Open();
// トランザクションを開始（今回は未使用）
using (var tarn = connection.BeginTransaction())
{
// SQL文
string sql = @"SELECT * FROM Person WHERE (@Id IS NULL OR @Id = '' OR Id = @Id)";
// Dynamic Object作成
var condtion = new
{
Id = ""
};
// Dapperで検索
var result = connection.Query<PersonDto>(sql, condtion);
// 検索結果を確認
foreach (var person in result)
{
Debug.Print(person.ToString());
}
}
// DBクローズ
connection.Close();
}
6.2. 動作確認
- Dynamic Objectの変数がNULLまたは空白の場合、Where条件対象から外すことができる
7. サンプルコード（動的Where句かつIN句）
7.1. 問題点
7.2. 今回対応した方法
- 動的Where句のIN句の条件対象を判定するDynamic Objectの変数を追加する
- SQLで追加した変数で条件を判定するように変更する
// SQL文
string sql = @"SELECT * FROM Person WHERE (@Id_IsWhere IS NULL OR @Id_IsWhere = '' OR Id IN @Id)";
// Dynamic Object作成
var condtion = new
{
// IN句がある場合
Id = new List<int>() { 1, 3 },
Id_IsWhere = "Add"
// IN句がない場合
// Id = null
};
// Dapperで検索
var result = connection.Query<PersonDto>(sql, condtion);
8. 参考文献