0

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?

CDove
  • 1,940
  • 10
  • 19
  • Please show us the dapper code you are using? How does the stored procedure look like? Is it using OUT parameters? – Alex Apr 03 '20 at 05:40

1 Answers1

0

Add an output parameter to capture the return code.

EG

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

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Unfortunately, it's not the return code that contains the required data; we're looking to get the dataset that results from the proc. – CDove Apr 08 '20 at 11:26
  • Daper's `Queryxxx` methods return the resultset from the proc. If you get confused, just revert to `ExecuteReader()` with a parameterized SQL statement. That's the lowest-level API to run queries, and exposes the most functionality. – David Browne - Microsoft Apr 08 '20 at 12:58