Dapper：帮我运行包含多个用户定义表类型的存储过程

Question

我有一个带有3个输入参数的存储过程。

... PROCEDURE [dbo].[gama_SearchLibraryDocuments]
@Keyword nvarchar(160), 
@CategoryIds [dbo].[IntList] READONLY, 
@MarketIds [dbo].[IntList] READONLY ...

IntList是用户定义的表类型。

CREATE TYPE [dbo].[IntList]
AS TABLE ([Item] int NULL);

我的目标是使用dapper调用此存储过程。

我已经发现了一些关于如何使用dapper传递用户定义类型的例子。其中一个例子是在Dapper.Microsoft.Sql nuget包中实现的TableValuedParameter类。

var list = conn.Query<int>("someSP", new
{
Keyword = (string)null,
CategoryIds = new TableValuedParameter<int>("@CategoryIds", "IntList", new List<int> { }),
MarketIds = new TableValuedParameter<int>("@MarketIds", "IntList", new List<int> { 541 })
}, commandType: CommandType.StoredProcedure).ToList();

上面的代码会抛出异常。

An exception of type 'System.NotSupportedException' occurred in Dapper.dll but was not handled in user code

Additional information: The member CategoryIds of type Dapper.Microsoft.Sql.TableValuedParameter`1[System.Int32] cannot be used as a parameter value

我已经使用一个用户定义的表类型测试了我的存储过程，它运行良好。

conn.Query<int>("someSP", new TableValuedParameter<int>("@MarketIds", "IntList", new List<int> { 541 }), commandType: CommandType.StoredProcedure).ToList();

我需要帮助来运行原始存储过程。 谢谢。

- Mike

Answer 1

Dapper有用于处理表值参数的扩展方法。

public static SqlMapper.ICustomQueryParameter AsTableValuedParameter(this DataTable table, string typeName = null)

您可以按照以下方式使用 Dapper：

var providersTable = new DataTable();
providersTable.Columns.Add("value", typeof(Int32));
foreach (var value in filterModel.Providers)
{
    providersTable.Rows.Add(value);
}
var providers = providersTable.AsTableValuedParameter("[dbo].[tblv_int_value]");

var filters =
    new
    {
        campaignId = filterModel.CampaignId,
        search = filterModel.Search,
        providers = providers,
        pageSize = requestContext.PageSize,
        skip = requestContext.Skip
    };

using (var query = currentConnection.QueryMultiple(StoredProcedureTest, filters, nhTransaction, commandType: CommandType.StoredProcedure))
{
    var countRows = query.Read<int>().FirstOrDefault();
    var temp = query.Read<CategoryModel>().ToList();
    return new Result<IEnumerable<CategoryModel>>(temp, countRows);
}

它将被翻译为SQL：

declare @p3 dbo.tblv_int_value
insert into @p3 values(5)
insert into @p3 values(34)
insert into @p3 values(73)
insert into @p3 values(14)

exec [dbo].[StoredProcedureTest] @campaignId=123969,@search=NULL,@providers=@p3,@pageSize=20,@skip=0

- Dmitry

---

我认为更详细的解释会更好 :) - DatRid

Dapper有扩展方法可用于处理表值参数。public static SqlMapper.ICustomQueryParameter AsTableValuedParameter(this DataTable table, string typeName = null) - Dmitry

很好的回答！只是纯代码的答案通常不太有用，所以你能给出越多的解释，对其他用户就越有帮助！ - DatRid

Answer 2

我在从Dapper调用接受用户定义表类型的存储过程的类似帖子中回答了一个类似的问题，并提供了以下内容，我认为这也可能对您有所帮助！

我知道这有点老了，但是我仍然想在这方面发帖，因为我希望通过我创建的NuGet软件包使其变得更加容易。 我希望我已经做到了这一点，它将允许使用以下代码：

public class IntList
{
     public int Item { get; set; }
}

var list1 = new List<IntList>{new IntList{ Item= 1 }, new IntList{ Item= 2}};
var list2 = new List<IntList>{new IntList{ Item= 3 }, new IntList{ Item= 4}};

var parameters = new DynamicParameters();
parameters.Add("@Keyword", "stringValue");
parameters.AddTable("@CategoryIds", "IntList", list1);
parameters.AddTable("@MarketIds", "IntList", list2);

 var result = con.Query<int>("someSP", parameters, commandType: CommandType.StoredProcedure);

NuGet包: https://www.nuget.org/packages/Dapper.ParameterExtensions/0.2.0 仍处于早期阶段，可能无法与所有内容兼容！ 请阅读README文件，并随时在GitHub上贡献：https://github.com/RasicN/Dapper-Parameters

- JCisar

---

我发现这个答案是在Dapper中传递多个表值参数到存储过程的问题上最优雅的解决方案。 - Maurice Klimek

Answer 3

这是一个有点久远的帖子，但我相信有更好的方法来实现这一点。具体而言，允许插入简单和复杂类型并执行单个更新的方式可能很重要，特别是如果您拥有大量索引。

在此示例中，我正在使用名为ProductAdd的存储过程和名为ProductList的用户定义表类型添加具有名称、ImageUrl和年份的Product。您可以将其简化为数字或字符串列表。

DataTable productList = new DataTable();
productList.Columns.Add(new DataColumn("Name", typeof(string)));
productList.Columns.Add(new DataColumn("ImageUrl", typeof(string)));
productList.Columns.Add(new DataColumn("Year", typeof(Int32)));
foreach (var product in products)
{
    // The order of these must match the order of columns for the Type in SQL!
    productList.Rows.Add(product.Name, product.ImageUrl, product.Year);
}

using (var connection = new SqlConnection(_appSettings.ConnectionString))
{
    await connection.OpenAsync();
    var result = new List<Product>();
    try
    {
        result = await connection.QueryAsync<Product>("[dbo].[ProductAdd]",
            new { product = productList },
            commandType: CommandType.StoredProcedure
        );
    }
    catch (SqlException ex)
    {
        // Permissions and other SQL issues are easy to miss, so this helps
        throw ex;
    }
}

return result;

用户定义的类型是通过以下方式定义的：

CREATE TYPE [dbo].[ProductList] AS TABLE(
    [Name] [nvarchar](100) NOT NULL,
    [ImageUrl] [nvarchar](125) NULL,
    [Year] [int] NOT NULL,
)

存储过程使用传递进来的列表作为表格。如果您正在使用相同的逻辑执行 GET/SELECT 操作并尝试使用 IN，则可以使用相同的逻辑。 SQL 的 IN 实际上是在底层进行了一个 JOIN，因此我们通过在表格上使用 JOIN 替换 IN 来更加明确。

CREATE PROCEDURE ProductAdd
    @product ProductList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [Product] ([Name], [ImageUrl], [Year])          
    SELECT paramProduct.[Name]
        , paramProduct.[ImageUrl]
        , paramProduct.[Year]
    FROM @product AS paramProduct
        LEFT JOIN [Product] AS existingProduct
            ON existingProduct.[Name] = paramProduct.[Name] 
                AND existingProduct.[Year] = paramProduct.[Year]
    WHERE existingProduct.[Id] IS NULL
END
GO

- Red