title: Dapper Distinguishing Return Value and Result Set
body: <p>I am using <code>Dapper</code>'s <code>QueryMultipleAsync</code> method to read multiple result set. My stored procedure checks some conditions (for example a user might be trying to get someone else's data by sending an Id to my API) to determine whether the data should return or not.</p>
  
  <p>I, on C# side, first need to read the <code>return value</code> (not result set) to determine if data is returned or SP simply returned 3 (which means insufficient rights). To illustrate the case:</p>
  
  <pre><code>IF @UserRole &lt; 10
  BEGIN
      RETURN 3; -- Insufficient rights.
  END
  
  IF @IsCurrentUserOwner = 0
  BEGIN
      RETURN 5; -- Not owner.
  END
  
  -- Get users.
  SELECT
          Id
      ,   [Name]
      ,   LastName
  FROM
      Users
  -- WHERE  ...
  
  -- Get chat messages.
  SELECT
      *
  FROM
      ChatMessages
  -- WHERE  ...
  </code></pre>
  
  <p>I know that <code>output</code> and <code>return</code> values are written at the end of reader so I can only read return parameter when all the data (result set) is read. So, I always have to read the result set first then return/output parameters.</p>
  
  <p>What if my <code>SP</code> looked like this:</p>
  
  <pre><code>-- ...some code above...
  IF @IsCurrentUserOwner = 0
  BEGIN
      RETURN 5; -- Not owner.
  END
  
  DECLARE @RType TINYINT = NULL
  
  -- some other code here to get @RType value here...
  
  SELECT @RType -- To make this a result so QueryMultiple's reader can read this.
  
  -- ...some other code to get users and chat messages...
  </code></pre>
  
  <p>To describe the problem:</p>
  
  <p><code>@RType</code> variable could be <code>5</code> as well as <code>SP</code>'s return value. When I read the result first (because output/return parameters are at the end of reader), how do I know the value I just read (which is <code>5</code> in this case) is the @RType or return value? (they are convertible)</p>
  
  <p>This is how my C# code roughly looks like:</p>
  
  <pre><code>var parameters = new DynamicParameters();
  parameters.Add("@RetVal", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
  var dbResult = await Context.SqlConnection.QueryMultipleAsync(sql: "my_sp_name", param: parameters, commandType: CommandType.StoredProcedure)
  // Some code here...
  
  using (var reader = dbResult)
  {
      var rType = reader.Read&lt;byte&gt;(); // &lt;----- How to know if I read @RType or just return value of SP since they are convertible to each other?
      var users = reader.Read&lt;User&gt;();
      var chatMessages = reader.Read&lt;ChatMessage&gt;();
      // ...
  }
  
  var returnValue = parameters.Get&lt;int&gt;("@RetVal");
  if (returnValue == 5)
  {
      return "You are not allowed to see this data";
  }
  </code></pre>
  
  <p>How do you recommend me to handle the case?</p>