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:
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.
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.