CODEWITHSUNDEEP
c#dapper
Teler
Teler

Reputation: 507

Error could not find stored procedure with Dapper and c# only when I specify commandType

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

Upvotes: 7

Views: 4550

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062965

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.

Upvotes: 10

Related Questions