NPocoの紹介 -機能編- 生クエリ

最初の記事はこちら

今回はSQLを直接記述してデータベースを操作します。

データベースはPostgreSQLを使用しました。

テーブルの用意

emp

前回と同じものを流用

dept

列名	型	主キー
dept_code	varchar(5)	1
dept_name	varchar(20)
created_at	timestamp
updated_at	timestamp

Execute メソッド

Insert, Update, Deleteといった変更操作を行うものについてはこのメソッドを実行します。

//sqlの用意
string sql = @"
INSERT INTO emp (
   emp_id
  ,first_name
  ,created_at
  ,updated_at
) VALUES (
   @empId
  ,@firstName
  ,@createdAt
  ,@updatedAt
)
";
//パラメータの用意
var param = new
{
    empId = "E0010",
    firstName = "たろー",
    createdAt = DateTime.Now,
    updatedAt = DateTime.Now,
};

//実行
database.Execute(sql, param);

実行結果ログは以下のように出力されます。

INSERT INTO emp (
   emp_id
  ,first_name
  ,created_at
  ,updated_at
) VALUES (
   @p0
  ,@p1
  ,@p2
  ,@p3
)

         -> @p0 [String] = "E0010"
         -> @p1 [String] = "たろー"
         -> @p2 [DateTime] = "2020/06/18 0:03:19"
         -> @p3 [DateTime] = "2020/06/18 0:03:19"

Dapper と似たような感覚で操作することができます。

ただDapper とは異なりこの処理があるおかげでOracle などの場合でもパラメータを@パラメータ とアットマークで渡せるようです。

Fetch などの検索系

ここでは empテーブルとdeptテーブルを結合し社員名、部署名を取得します。

まずクエリの結果を格納するクラスを用意

EmpAndDeptNameDto.cs

public class EmpAndDeptNameDto
{
    public string EmpName { get; set; }

    public string DeptName { get; set; }

    public override string ToString()
    {
        return $"[EmpName={EmpName}, DeptName={DeptName}]";
    }
}

実行コード

//sqlの用意
string sql = @"
SELECT
  e.family_name || e.first_name AS emp_name
 ,d.dept_name
FROM
  emp e
LEFT OUTER JOIN
  dept d
ON (e.dept_code = d.dept_code)
WHERE
  e.dept_code = @deptCode
ORDER BY
  e.emp_id
";
//パラメータの用意
var param = new
{
    deptCode = "001"
};

//実行
List<EmpAndDeptNameDto> results = database.Fetch<EmpAndDeptNameDto>(sql, param);
results.ForEach(Console.WriteLine);

実行結果ログは以下のように出力されます。

SELECT
  e.family_name || e.first_name AS emp_name
 ,d.dept_name
FROM
  emp e
LEFT OUTER JOIN
  dept d
ON (e.dept_code = d.dept_code)
WHERE
  e.dept_code = @p0
ORDER BY
  e.emp_id

         -> @p0 [String] = "001"
[EmpName=Nぽこ太郎, DeptName=東京本社]
[EmpName=田中次郎, DeptName=東京本社]

Fetch メソッドでは結果をList<>で返しますが、これ以外にも

First
FirstOrDefault
Single
SingleOrDefault

などがあります。

また直接クエリを渡す形式の Query<T> というものもあります。
こちらは大量データ処理などのために1件毎に対象をインスタンス化して利用する場合に使うものだと思います。
(間違ってたらすみません。)

Query<T>のドキュメントはこちら

EnableAutoSelect について

DatabaseクラスのEnableAutoSelectが有効な場合(デフォルトで有効)

生SQLがSELECT, EXECUTE, CALL, EXEC 以外から始まっている場合、自動でSELECT (列一覧) を付与してくれます。

この機能はAutoSelectHelperというクラスによって行われています。

コード例です。

WHERE句から開始した場合

string sql1 = @"
WHERE
  emp_id = @empId
";

var param1 = new
{
    empId = "E0001"
};

database.SingleOrDefault<Emp>(sql1, param1);

実行されたクエリ

SELECT "emp_id" AS "EmpId", "first_name" AS "FirstName", "family_name" AS "FamilyName", "dept_code" AS "DeptCode", "created_at" AS "CreatedAt", "updated_at" AS "UpdatedAt" FROM "emp"
WHERE
  emp_id = @p0

         -> @p0 [String] = "E0001"

FROM句から開始した場合

string sql2 = @"
FROM
  emp
WHERE
  emp_id = @empId
";

var param2 = new
{
    empId = "E0001"
};

database.SingleOrDefault<Emp>(sql2, param2);

実行されたクエリ

SELECT "emp_id" AS "EmpId", "first_name" AS "FirstName", "family_name" AS "FamilyName", "dept_code" AS "DeptCode", "created_at" AS "CreatedAt", "updated_at" AS "UpdatedAt"
FROM
  emp
WHERE
  emp_id = @p0

         -> @p0 [String] = "E0001"

注意点

この機能は大変便利なのですがwith でクエリを開始した場合にもselect 列一覧 が先頭に付与され、不正なSQLになってしまいます。

これを回避するためには with の前に ; を付与すれば回避可能です。

;
with X as (
  select
  -- 以下省略
)

コードの箇所で説明するとこのif がtrue になるためです。