CODEWITHSUNDEEP
c#sqldapper
michasaucer
michasaucer

Reputation: 5238

Dapper stored procedure has too many arguments specified when passing IEnumerable to it

I'm calling my procedure by this method:

public async Task<IEnumerable<Algorithm>> GetAlgorithmsByNameAsync(IEnumerable<string> names)
{
    var parameters = new DynamicParameters();
    parameters.Add("@names", names);

    var connection = _connection.GetOpenConnection();   
    return await connection.QueryAsync<Algorithm>("GetAlgorithmsByName", parameters, commandType: CommandType.StoredProcedure);
}

My Procedure looks like this:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

--PROCEDURE HERE--

CREATE PROCEDURE GetAlgorithmsByName

@names StringList READONLY -- my own type

AS
BEGIN
    SELECT ALgorithmId, Name From Algorithms WHERE Name IN (SELECT Item FROM @names)
END

From the code above, I get an error:

"Procedure or function GetAlgorithmsByName has too many arguments specified."

What am I doing wrong? How do I pass IEnumerable<string> to a stored procedure using dapper?

Upvotes: 3

Views: 679

Answers (3)

ali
ali

Reputation: 329

You can use the IEnumerable (dynamic) rather than IEnumerable (string).

Check this link and try How to Implement IEnumerable (dynamic)

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063734

Table valued parameters aren't trivial to use; one way is via the extension method that Dapper adds on DataTable (something like AsTableValuedParameter), but: it doesn't work as simply as IEnumerable<T> - at least, not today. You also probably don't need DynamicParameters here.

If what you want is just a set of strings, then one very pragmatic option is to look at the inbuilt string_split API in SQL Server, if you can define a separator token that is never used in the data. Then you can just pass a single delimited string.

Upvotes: 3

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

In your stored procedure is expecting [Item] [NVARCHAR](MAX), it means one item Whereas you are passing IEnumerable<string> names. So that's the reason why you are getting the error.

There are numerous way to pass the list of string to sp

  1. XML
  2. Using table-valued parameters like CREATE TYPE NameList AS TABLE ( Name Varchar(100) );
  3. Using names = "Name1, Name2, .. , Namen"; then sql you can use T-SQL split string to get the name list

Updated You are passing param incorrectly, Let's fix it by this way

     using (var table = new DataTable()) 
     {
      table.Columns.Add("Item", typeof(string));

      for (int i = 0; i < names.length; i++)
        table.Rows.Add(i.ToString());

      var pList = new SqlParameter("@names", SqlDbType.Structured);
      pList.TypeName = "dbo.StringList";
      pList.Value = table;

      parameters.Add(pList);
   }

Upvotes: 1

Related Questions