c# - 包含 TableValueParameter 的 Dapper 存储过程

Question

我很难理解将包含 TableValueParameter(TVP) 的参数与其他参数一起传递给存储过程的正确 Dapper 语法。我的代码的简化版本:

类

public class GalleryViewModel
{
    public string GalleryName { get; set; }
    public string Category { get; set; }
    public IEnumerable<Image> Images { get; set; }
}

public class Image
{
    public string FileName { get; set; }
    public int Order { get; set; }
}

C# 代码段

var param = new DynamicParameters();
param.Add("@Images", model.Images);//TVP
param.Add("@GalleryName", model.GalleryName);
param.Add("@Category", model.Category);

connection.Execute("CreateGallery", param, commandType: CommandType.StoredProcedure);

SQL 片段

--Create table type
CREATE TYPE GalleryImageType AS TABLE   
( 
    FileName    NVARCHAR(64),
    Order       INT
)
GO 

CREATE PROCEDURE [dbo].[CreateGallery]
    @Images             GalleryImageType READONLY,
    @GalleryName        NVARCHAR(32),
    @Category           NVARCHAR(32)
AS
-- SPROC contents here....

Answer 1

使用 DataTable 的实例而不是 IEnumerable:

DataTable dt = new DataTable();
dt.Columns.Add("FileName");
dt.Columns.Add("Order");

foreach (var image in viewmodel.Images)
{
    dt.Rows.Add(image.FileName, image.Order);
}

var param = new DynamicParameters();
param.Add("@Images", dt);//TVP
param.Add("@GalleryName", model.GalleryName);
param.Add("@Category", model.Category);

connection.Execute("CreateGallery", param, commandType: CommandType.StoredProcedure);