1

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

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);

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

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

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

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Judy007
  • 5,484
  • 4
  • 46
  • 68

1 Answers1

2

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);
ali
  • 1,301
  • 10
  • 12
  • what is BrandId type in sql parameter? Can you show content of usp_getFilteredCatalogItems? – toha Apr 28 '22 at 05:55
  • It is int as written in question. My answer only focuses how to send list of integers to a stored procedure. – ali Apr 30 '22 at 15:02
  • yes that is what I need. But I do not understand how to declare list of int in sql stored procedure – toha May 01 '22 at 04:10
  • @toha Actually you can't declare as in C# but you can declare a table with an integer column as work around. – ali May 06 '22 at 08:00