DapperLib / DapperLib/Dapper

Stored procedure return value on multiple result sets

Open
#1,346 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

I have a procedure that validates certain data and in case the validation succeeds, it outputs 3 result sets and returns 0. If validation fails, then it just returns an error code (and error message in an output parameter).
In my C# code I am trying to check the return value before reading any of the possibly returned record sets. If my procedure successfully passes validation and returns the mentioned sets, I get System.ApplicationException : Attempting to cast a DBNull to a non nullable type! when I try to check return value before reading all the result sets. I get proper value if I first read all result sets.

Example:

using (var connection = new SqlConnection(GetConnectionString())
{
   var p = new DynamicParameters();
   p.Add("a", 11);
   p.Add("o", dbType: DbType.StringFixedLength, size: 500, direction: ParameterDirection.Output);
   p.Add("r", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
   connection.Open();
   connection.Execute(@"DROP PROCEDURE IF EXISTS spEcho");
   connection.Execute(@"
CREATE PROCEDURE spEcho
    @a INT,
    @o NVARCHAR(500) OUT
AS 
BEGIN
    IF @a = 11
    BEGIN
        SELECT @a Id, 'ping' Name, 1 Id, 'pong1' Name
        SELECT @a Id, 'ping' Name, 2 Id, 'pong2' Name
    END
    ELSE
        SET @o = 'No data returned'
    RETURN @a
END");
   var grid = connection.QueryMultiple("spEcho", p,
                                       commandType: CommandType.StoredProcedure);
   int procRetVal = 0;
   //procRetVal = p.Get<int>("r"); //this statement fails with System.ApplicationException
   var result1 = grid.Read();
   var result2 = grid.Read();
   procRetVal  = p.Get<int>("r"); //this returns expected value
}

If validation fails, return value is retrieved properly.
I know I could alter the procedure and instead of using return value, I could return this information in additional result set, but this feels wrong to me. Is there any other way I can get store procedure's return value without going through all returned results?

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the supplied QueryMultiple call and DynamicParameters return-value registration, then inspect how consuming multiple result sets affects output and return parameters. Test both the two-result-set success branch and the no-result-set failure branch; done means establishing the supported retrieval behavior and documenting it or adding focused regression coverage.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
backend, database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.