Dapperにて、TVP(テーブル値パラメーター)を扱う

Dapper SQL Server C#

たとえば、以下のようなテーブル定義と、

CREATE TABLE [dbo].[TodoItems](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [IsDone] [bit] NOT NULL,
    CONSTRAINT [PK_TodoItems] PRIMARY KEY CLUSTERED ([Id] ASC)
)

以下のようなユーザー定義テーブル型*1、

CREATE TYPE [dbo].[tvpIds] AS TABLE(
    [Id] [int] NOT NULL
)

CREATE TYPE [dbo].[tvpTodoItems] AS TABLE(
    [Name] [nvarchar](50) NOT NULL,
    [IsDone] [bit] NOT NULL
)

さらに、以下のようなストアドプロシージャを定義します。

CREATE PROCEDURE [dbo].[usp_CreateTodoItems] 
    @Items [dbo].[tvpTodoItems] READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO [dbo].[TodoItems]
    ([Name], [IsDone])
  OUTPUT inserted.[Id], inserted.[Name], inserted.[IsDone]
  SELECT [Name], [IsDone]
  FROM @Items;
END

CREATE PROCEDURE [dbo].[usp_DeleteTodoItems] 
    @Ids [dbo].[tvpIds] READONLY
AS
BEGIN
  SET NOCOUNT ON;

  DELETE [dbo].[TodoItems]
  OUTPUT deleted.[Id]
  WHERE [Id] IN (SELECT [Id] FROM @Ids);
END

今回はこの二つのストアドプロシージャをDapperを介して利用する方法を検討します。

ググった

当然ググります。そうして下記のリンクの情報を得ました。

https://www.codeproject.com/Articles/835519/Passing-Table-Valued-Parameters-with-Dapper

利用してみましょう。

  public class CreateTodoItemPoco
  {
    public string TodoName { get; set; }

    public bool TodoIsDone { get; set; }
  }

  public class TodoItemDto
  {
    public int Id { get; set; }

    public string Name { get; set; }

    public bool IsDone { get; set; }
  }


      var created = await CreateTodoItems(xs);

      var deletingIds = created.Take(3).Select(y => y.Id);

      var deleted = await DeleteTodoItems(deletingIds);
    }

    private async Task<IEnumerable<TodoItemDto>> CreateTodoItems(IEnumerable<CreateTodoItemPoco> items)
    {
      var p = new DynamicParameters();
      var tvp =
        items.AsTableValuedParameter("tvpTodoItems",
          new[] { nameof(CreateTodoItemPoco.TodoName), nameof(CreateTodoItemPoco.TodoIsDone) });

      p.Add("@Items", tvp);

      using (var conn = new SqlConnection(ConnectionString))
      {
        return await conn.QueryAsync<TodoItemDto>("[dbo].[usp_CreateTodoItems]", p, commandType: System.Data.CommandType.StoredProcedure);
      }
    }

    private async Task<IEnumerable<int>> DeleteTodoItems(IEnumerable<int> ids)
    {
      var p = new DynamicParameters();
      var tvp = ids.AsTableValuedParameter("tvpIds");
      p.Add("@Ids", tvp);

      using (var conn = new SqlConnection(ConnectionString))
      {
        return await conn.QueryAsync<int>("[dbo].[usp_DeleteTodoItems]", p, commandType: System.Data.CommandType.StoredProcedure);
      }
    }

  public static class DapperExtensions
  {
    // https://www.codeproject.com/Articles/835519/Passing-Table-Valued-Parameters-with-Dapper

    /// <summary>
    /// This extension converts an enumerable set to a Dapper TVP
    /// </summary>
    /// <typeparam name="T">type of enumerbale</typeparam>
    /// <param name="enumerable">list of values</param>
    /// <param name="typeName">database type name</param>
    /// <param name="orderedColumnNames">if more than one column in a TVP,
    /// columns order must mtach order of columns in TVP</param>
    /// <returns>a custom query parameter</returns>
    public static SqlMapper.ICustomQueryParameter AsTableValuedParameter<T>
      (this IEnumerable<T> enumerable,
        string typeName, IEnumerable<string> orderedColumnNames = null)
    {
      var dataTable = new System.Data.DataTable();
      if (typeof(T).IsValueType || typeof(T).FullName.Equals("System.String"))
      {
        dataTable.Columns.Add(orderedColumnNames == null ?
          "NONAME" : orderedColumnNames.First(), typeof(T));
        foreach (T obj in enumerable)
        {
          dataTable.Rows.Add(obj);
        }
      }
      else
      {
        PropertyInfo[] properties = typeof(T).GetProperties
          (BindingFlags.Public | BindingFlags.Instance);
        PropertyInfo[] readableProperties = properties.Where
          (w => w.CanRead).ToArray();
        if (readableProperties.Length > 1 && orderedColumnNames == null)
          throw new ArgumentException("Ordered list of column names must be provided when TVP contains more than one column");

        var columnNames = (orderedColumnNames ??
          readableProperties.Select(s => s.Name)).ToArray();
        foreach (string name in columnNames)
        {
          dataTable.Columns.Add(name, readableProperties.Single
            (s => s.Name.Equals(name)).PropertyType);
        }

        foreach (T obj in enumerable)
        {
          dataTable.Rows.Add(
            columnNames.Select(s => readableProperties.Single
              (s2 => s2.Name.Equals(s)).GetValue(obj))
                .ToArray());
        }
      }
      return dataTable.AsTableValuedParameter(typeName);
    }

で、実行したら動きました。やったね。
しかしながら、AsTableValuedParameter("tvpTodoItems", の次のパラメータに、

  • プロパティ名を文字列として、
  • ユーザー定義テーブル型の列順に

指定しなければならないのは、あまり嬉しくありません(特に2番目。そんな情報覚えている自信はありません)。

ちょっと変える

私の利用状況では、SQLServerのユーザー定義型をアプリ側で動的に作成することはないため、リフレクションを用いたマッピングは不要です。
そこで次のようにしてみました。

  public static class TvpIds
  {
    public static string Scheme { get; } = "dbo";
    public static string TypeName { get; } = "tvpIds";
    public static string TypeFullName { get; } = $"[{Scheme}].[{TypeName}]";

    public static (string name, Type type) Column { get; } =
        ("[Id]", typeof(int));
  }

  public static class TvpTodoItems
  {
    public static string Scheme { get; } = "dbo";
    public static string TypeName { get; } = "tvpTodoItems";
    public static string TypeFullName { get; } = $"[{Scheme}].[{TypeName}]";

    public static IEnumerable<(string name, Type type)> OrderedColumns { get; } =
      new[]
      {
        ("[Name]", typeof(string)),
        ("[IsDone]", typeof(bool))
      };

    public static object[] Map(CreateTodoItemPoco item) =>
      new object[]
      {
          item.TodoName,
          item.TodoIsDone
      };
  }

    public static SqlMapper.ICustomQueryParameter AsValueListTableValuedParameter<T>(
      this IEnumerable<T> source,
      string typeName,
      (string name, Type type) column)
    {
      var dataTable = new DataTable();

      dataTable.Columns.Add(column.name, column.type);

      foreach (var data in source)
      {
        dataTable.Rows.Add(data);
      }

      return dataTable.AsTableValuedParameter(typeName);
    }

    public static SqlMapper.ICustomQueryParameter AsStructuredListTableValuedParameter<T>(
      this IEnumerable<T> source,
      string typeName,
      IEnumerable<(string name, Type type)> orderedColumns,
      Func<T, object[]> map)
    {
      var dataTable = new DataTable();

      foreach (var (name, type) in orderedColumns)
      {
        dataTable.Columns.Add(name, type);
      }

      foreach (var data in source)
      {
        dataTable.Rows.Add(map(data));
      }

      return dataTable.AsTableValuedParameter(typeName);
    }

    private async Task<IEnumerable<TodoItemDto>> CreateTodoItems2(IEnumerable<CreateTodoItemPoco> items)
    {
      var p = new DynamicParameters();
      var tvp =
        items.AsStructuredListTableValuedParameter(
          TvpTodoItems.TypeFullName,
          TvpTodoItems.OrderedColumns,
          TvpTodoItems.Map);
      p.Add("@Items", tvp);

      using (var conn = new SqlConnection(ConnectionString))
      {
        return await conn.QueryAsync<TodoItemDto>("[dbo].[usp_CreateTodoItems]", p, commandType: System.Data.CommandType.StoredProcedure);
      }
    }

    private async Task<IEnumerable<int>> DeleteTodoItems2(IEnumerable<int> ids)
    {
      var p = new DynamicParameters();
      var tvp = ids.AsValueListTableValuedParameter(TvpIds.TypeFullName, TvpIds.Column);
      p.Add("@Ids", tvp);

      using (var conn = new SqlConnection(ConnectionString))
      {
        return await conn.QueryAsync<int>("[dbo].[usp_DeleteTodoItems]", p, commandType: System.Data.CommandType.StoredProcedure);
      }
    }

これでも正しく動きました。
ユーザー定義テーブル型の列順に依存していた情報は、Dapper利用時にはある程度隠れたと思います。
CreateTodoItemPoco 以外のCLRオブジェクトからのマッピングが必要になったら、TvpTodoItems.Mapを追加するような感じです。
また、実行速度も最初のバージョンよりも上がったと思われます。

さらに抽象化してみる

よせばよいのにさらに抽象化してみましょう。

  public abstract class TvpValueListDefinitionBase
  {
    public string Scheme { get; protected set; }

    public string TypeName { get; protected set; }

    public string TypeFullName => $"[{Scheme}].[{TypeName}]";

    public (string name, Type type) Column { get; protected set; }
  }

  public abstract class TvpStructuredListDefinitionBase
  {
    public string Scheme { get; protected set; }

    public string TypeName { get; protected set; }

    public string TypeFullName => $"[{Scheme}].[{TypeName}]";

    public IEnumerable<(string name, Type type)> OrderedColumns { get; protected set; }

    public abstract object[] Map(object item);
  }

  public class TvpIdsDefinition : TvpValueListDefinitionBase
  {
    public TvpIdsDefinition()
    {
      Scheme = "dbo";
      TypeName = "tvpIds";
      Column = ("[Id]", typeof(int));
    }
  }

  public class TvpTodoItemsDefinition : TvpStructuredListDefinitionBase
  {
    public TvpTodoItemsDefinition()
    {
      Scheme = "dbo";
      TypeName = "tvpTodoItems";
      OrderedColumns =
        new[]
        {
            ("[Name]", typeof(string)),
            ("[IsDone]", typeof(bool))
        };
    }

    public override object[] Map(object obj)
    {
      switch (obj)
      {
        case CreateTodoItemPoco item:
          return new object[]
          {
              item.TodoName,
              item.TodoIsDone
          };

        default:
          throw new NotSupportedException(obj.GetType().ToString());
      }
    }
  }

    public static SqlMapper.ICustomQueryParameter AsValueListTableValuedParameter<T>(
      this IEnumerable<T> source,
      TvpValueListDefinitionBase tvpDefinition)
    {
      var dataTable = new DataTable();

      dataTable.Columns.Add(tvpDefinition.Column.name, tvpDefinition.Column.type);

      foreach (var data in source)
      {
        dataTable.Rows.Add(data);
      }

      return dataTable.AsTableValuedParameter(tvpDefinition.TypeFullName);
    }

    public static SqlMapper.ICustomQueryParameter AsStructuredListTableValuedParameter<T>(
      this IEnumerable<T> source,
      TvpStructuredListDefinitionBase tvpDefinition)
    {
      var dataTable = new DataTable();

      foreach (var (name, type) in tvpDefinition.OrderedColumns)
      {
        dataTable.Columns.Add(name, type);
      }

      foreach (var data in source)
      {
        dataTable.Rows.Add(tvpDefinition.Map(data));
      }

      return dataTable.AsTableValuedParameter(tvpDefinition.TypeFullName);
    }

    private async Task<IEnumerable<TodoItemDto>> CreateTodoItems3(IEnumerable<CreateTodoItemPoco> items)
    {
      var p = new DynamicParameters();
      var tvp =
        items.AsStructuredListTableValuedParameter(new TvpTodoItemsDefinition());
      p.Add("@Items", tvp);

      using (var conn = new SqlConnection(ConnectionString))
      {
        return await conn.QueryAsync<TodoItemDto>("[dbo].[usp_CreateTodoItems]", p, commandType: System.Data.CommandType.StoredProcedure);
      }
    }

    private async Task<IEnumerable<int>> DeleteTodoItems3(IEnumerable<int> ids)
    {
      var p = new DynamicParameters();
      var tvp = ids.AsValueListTableValuedParameter(new TvpIdsDefinition());
      p.Add("@Ids", tvp);

      using (var conn = new SqlConnection(ConnectionString))
      {
        return await conn.QueryAsync<int>("[dbo].[usp_DeleteTodoItems]", p, commandType: System.Data.CommandType.StoredProcedure);
      }
    }

こちらも動くことは動くのですが、CreateTodoItemPoco から object[] へのマッピングが存在するかが実行時まで分からなくなってしまいました。
ままならないものです。

まとめ

DapperでSQLServerのユーザー定義テーブル型をストアドプロシージャのパラメータとして利用する方法を調べました。

*1:すっかり忘れていましたが、PRIMARY KEYとか定義した方が良いと思います