I've been tasked with replacing one of those nasty "GUI-based programming" things that businesses sometimes buy when trying to shrink their programming staff with a .Net Core app using Dapper (now that they've realized it was a horrible mistake and made garbage code).
I am not permitted to change the stored procs as it stands.
The proc being called contains two values, a
select statement and a
return value, which were both retrieved somehow by the app. We can't see how the app did it; this is obfuscated in proprietary "core" libraries. It looks something like this:
--things it does Select Prop1, Prop2, Prop3 from TheTable; Return 0; --success
(I cannot paste the actual query because we have aggressive cybersecurity, but this is the base logic behind it. There are no OUT params.)
When I use Dapper's
ExecuteAsync( method, I only get a return value of 0 or null, depending on the return value. I've tried changing it to a Query , but i always get null, even if there is a value giving the same params to the proc in SSMS.
var result = await _dbc.QueryFirstOrDefaultAsync<dynamic>("storedproc", paramsObject);
How can I also fetch the results of the select statement without altering the stored procedure to return both in variables?
Add an output parameter to capture the return code.
var result = await _dbc.QueryFirstOrDefaultAsync<dynamic>("exec @rc = storedproc", paramsObject);
Or use CommandType.Stored procedure with a ReturnValue parameter. Per Passing Output parameters to stored procedure using dapper in c# code