7

I'm using Dapper with C# and SQL Server. I have an stored procedure in SQL Server that inserts a person into my database. The problem is that when I specify in the query that its an stored procedure I get

Could not find stored procedure

but without using commandType, everything works fine.

For example: I had the following code:

var result = connection.Execute("KBunnyGame_Players_InsertPlayer @Name, @Score, @FacebookId", player, commandType: CommandType.StoredProcedure);

And I kept getting the error, but after I changed to:

var result = connection.Execute("KBunnyGame_Players_InsertPlayer @Name, @Score, @FacebookId", player);

I didn't get the error anymore and the player got inserted in the database. I don't know if that's the expected behaviour, it seems weird to me. Is there any documentation I could follow?

EDIT:

Stored procedure code:

ALTER PROCEDURE [dbo].[KBunnyGame_Players_InsertPlayer] 
    @Name NCHAR(20),
    @Score INT,
    @FacebookId NCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO KBunnyGame_Players (name, score, facebookId) 
    VALUES (@Name, @Score, @FacebookId); 
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Teler
  • 477
  • 6
  • 15

1 Answers1

9

Don't specify the parameters in the command-text when using CommandType.StoredProcedure; provide just the name - the parameters are passed by name already.

If you need to filter the properties on the object, use a projection:

var result = connection.Execute("KBunnyGame_Players_InsertPlayer",
    new { player.Name, player.Score, player.FacebookId },
    commandType: CommandType.StoredProcedure);

SQL Server has implicit EXEC if something looks sufficiently close enough to an EXEC, which is why the other version works. So if you prefer: use that. With or without a leading EXEC.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • It works, thanks a lot. Just a small question, is there any difference between specifying the commandType.StoredProcedure, and using it without it?, or is it the same either way? – Teler Nov 09 '18 at 23:35
  • @Teler ADO.NET (and more specifically: SqlClient) treats the two scenarios very differently. With just the name and with `CommandType.Text` (or omitted) you wouldn't actually be passing the parameters - you'd be invoking it with the default parameters – Marc Gravell Nov 09 '18 at 23:38