使用Dapper，如何将sql类型的值作为参数传递? [英] Using Dapper, how do I pass in the values for a sql type as param?

问题描述

我正在尝试使用dapper并将我在这里使用DDL定义的整数列表传递到存储过程中

I'm attempting to use dapper and pass into a stored procedure a list of integers which I have defined here using DDL

CREATE TYPE [dbo].[BrandIDSet] AS TABLE ([BrandID] INT NULL);

我已经创建了这个存储过程:

I've created this stored procedure:

CREATE PROCEDURE dbo.usp_getFilteredCatalogItems 
    @BrandIDSet [dbo].[BrandIDSet] READONLY

并尝试在C#代码中将该参数的值传递为

and attempting to pass in in c# code the value for that parameter as

public async Task<PaginatedCatalogItemsVM> GetFilteredCatalogItems(int pageSize, int pageNumber, List<int> brandIDSet)
{
     ..
     string storedProcName = "dbo.usp_getFilteredCatalogItems";
     paginatedItemsVM.CatalogItemResults = await connection.QueryAsync<CatalogItemVM>(storedProcName, new { BrandIDSet = brandIDSet }, commandType: CommandType.StoredProcedure);

但是，dapper似乎并没有按预期方式转换列表.使用上面的代码，将导致以下SQL被执行

However, dapper does not seem to be converting the list as expected. With the above code, it results in the following SQL getting executed

exec dbo.usp_getFilteredCatalogItems @BrandIDSet1=1

这不正确，因为BrandIDSet1不是参数的名称.精氨酸 因此，它会导致

Which isn't right, since BrandIDSet1 is not the name of the parameter. Arg. Thus, it results in

SqlException:@ BrandIDSet1不是过程usp_getFilteredCatalogItems的参数.

SqlException: @BrandIDSet1 is not a parameter for procedure usp_getFilteredCatalogItems.

如何获取类型以转换为正确的SQL?

How do I get the type to convert to the proper SQL?

推荐答案

您可以传递DataTable以获得正确的结果.您可以尝试下面的代码；

You can pass DataTable for proper result. You can try the code below;

var dt = new DataTable();
dt.Columns.Add(new DataColumn("BrandID", typeof(int)));
foreach (var id in brandIDSet)
{
    var row = dt.NewRow();
    row["BrandId"] = id;
    dt.Rows.Add(row);
}

string storedProcName = "dbo.usp_getFilteredCatalogItems";
paginatedItemsVM.CatalogItemResults = await connection.QueryAsync<CatalogItemVM>(storedProcName, new { BrandIDSet = dt }, commandType: CommandType.StoredProcedure);

这篇关于使用Dapper，如何将sql类型的值作为参数传递?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！