Getting a Return value from a Stored Procedure in PetaPoco

For the past several months I’ve been using PetaPoco for almost all of my data access needs in .Net, and I’ve absolutely loved it. Until today.

I had a stored procedure from a legacy database that returns a code (e.g. -1, invalid user id, 0 success etc.)  I sat down to execute this stored procedure and ran the following command:

https://gist.github.com/1870197

However, PetaPoco kept throwing an exception.  As I  stepped into the code, I found out why, the cmd.ExecuteScalar was always returning null.  I tried a bunch of different solutions, and none of them worked.  Then, I opened up SSMS (which should have been my first step) and started looking at how it executes stored procedures.  That’s when it hit me.  PetaPoco is passing a command with a type ‘text’ so I needed to specify more data to make it work.  The query below did the trick.

https://gist.github.com/1870209

Pay particular notice to the @@result.  If you simply put ‘@result’ PetaPoco will expect a parameter named result.  Putting the dual ‘@’ tells PetaPoco that this is a SQL parameter.