Dapper and mssql using stored procedure returning meaningful error

dapper sql-server

Question

I have a stored procedure inserts a row, and some conditions returns result set or single error code but when I use dapper return always same return class. so I couldn't understand If code gives me error or message rather than successful result set.

    public static List<Result> Results(int Id)
    {
        using (IDbConnection connection = BL.DataProvider.OpenConnection())
        {
            return connection.Query<Result>("SearchResultGet", new { Id = Id }, commandType: CommandType.StoredProcedure).ToList();
        }
    }

ALTER PROCEDURE SearchResultGet
@Id int
AS
IF(id != 0)
SELECT * FROM XX WHERE Id = Id
ELSE
SELECT -1

codes are just sample, doesn't have any meaning.

Expert Answer

There is no ORM/micro-ORM API that is going to like this; having a select -1 for one set of cases is just ... not pleasant. Options:

  • change the sproc to not do that - just run the regular select that returns zero rows
  • add the logic to the Results method instead (or in addition to) the sproc - i.e. check whether Id is zero in the C#
  • use a return -1 rather than a select -1 (although note that dapper doesn't make it trivial to capture return values)
  • use a sql error (raiserror)



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why