読者です 読者をやめる 読者になる 読者になる

きよくらの備忘録

「三日坊主と呼ばせない!日記」改め。主にソフトウェア開発関連の話題。

テーブル値パラメータについて少し整理とメモ その2・Dapper編

ADO.NET SQL Server ASP.NET dapper

前回 『テーブル値パラメータについて少し整理とメモ - きよくらの備忘録』の続きです。

前回は基本ということで、System.Data.SqlClient、つまり巣のADO.NETでやってみました。が、実際にはORMやMicroORMを利用することが大半でしょう。ということで、私が普段使っているDapperでテーブル値パラメータ (Table-Valued Parameters / TVPs) を使う方法について試してみました。

 

Dapperでテーブル値パラメータを使う

とりあえずDapperでやってみよう、ということで、素直に以下のように書き換えて実行しました。……が、結論から言うと、例外が発生して実行できませんでした。

// using Dapper; が必要
using (var con = new SqlConnection(connectionString))
{
  con.Open();
  var sql = "SELECT Id, Name, Age FROM Users WHERE Id IN (SELECT Value FROM @tvp);" ;
  var param = new { tvp = tvp };
  var rows = con.Query(sql, param); // ★例外発生!
  foreach ( var row in rows)
  {
    // 値を読みだす
  }
}

例外の内容は以下の通り。

"table 型パラメーター 'tvp' には、有効な型名が必要です。"

このエラーの内容だけ見ると、前回のSqlClientを使う場合にSqlParameterのTypeName プロパティに正しく型名を設定するのと、同様の何かが必要であるように読めます。

 

その答えはDapperのIssueにありました。

github.com

つまるところ、パラメータを設定する際に AsTableValuedParameter拡張メソッドを使用して型名を設定すれば良い模様。ということで、以下のように書いてみると、期待通りに動作しました。

using (var con = new SqlConnection(connectionString))
{
  con.Open();
  var sql = "SELECT Id, Name, Age FROM Users WHERE Id IN (SELECT Value FROM @tvp);" ;
  var param = new { tvp = tvp.AsTableValuedParameter( "dbo.IntValueTableType") }; //型名を指定する
  var rows = con.Query(sql, param);
  foreach ( var row in rows)
  {
    // 値を読みだす
  }
}

 

また前回に例として挙げた、ストアドプロシージャ / スカラ値関数 / テーブル値関数 についても、同じ対応で実行できました。

 

ストアドプロシージャだけはAsTableValuedParameterが省略できる

が、実はストアドプロシージャの場合、このAsTableValuedParameterによる型名の設定無しでもいけました*1

using (var con = new SqlConnection(connectionString))
{
  con.Open();

  var sql = "dbo.SummaryValueProc" ;
  var param = new { Values = tvp }; // AsTableValuedParameter 無しでもいける
  var rows = con.Query(sql, param, commandType: CommandType .StoredProcedure);
  foreach ( var row in rows)
  {
    // 値を読みだす
  }
}

この理由については調べれていませんが、受け取るストアド側でパラメータの型情報を持っているのためパラメータ名を使って問題なくマッピングできるから……というあたりでしょうか?

 

【追記】

よくよくMSDNのサンプルを見たら、ストアドの場合はSqlClientでも、TypeName プロパティを設定していませんでした。ということで、たぶん、上記の推測でだいたいあってるんじゃなかろうかと思います。

 

サンプルについて

今回のDapperのサンプルを前回と同じリポジトリに追加しました。

github.com

*1:実は先にストアドで使って普通に実行できていたので、ストアド以外のパターンで例外で出て悩んだという事情があったり