title: Pattern for using Dapper typed QueryAsync with stored procedure return codes
body: <p>I'm running into a situation with Dapper calling a stored procedure where it does some validation logic, updates a row, then returns the results of the row as a strongly-typed POCO.</p>
  
  <p>The problem lies in the validation logic, as there are multiple exit conditions where I want to return an error code for a given situation - and when Dapper <code>QueryAsync</code> does not receive a result set that matches the given POCO then it throws an <code>InvalidOperationException</code>.</p>
  
  <p>(Before anybody comments on mixing logic in with the stored procedure, I understand the concern here but at this point it is unavoidable due to strict performance constraints. The design is not the issue to address.)</p>
  
  <p>How can Dapper handle a stored procedure that can return either a POCO or an error code?</p>
  
  <p>I haven't seen anything in Dapper tutorials about reading stored procedure return codes when using <code>QueryAsync&lt;T&gt;</code>.</p>
  
  <p>So my initial thought is to do something like this (pseudocode below):</p>
  
  <ol>
  <li>Stored procedure: make it always return POCO columns as the first result set (<code>SELECT TOP 0 FROM PocoTable</code>)</li>
  <li>Stored procedure: then <code>SELECT -1 AS ErrorCode</code> as the final result set</li>
  <li>C# repository: instead of doing <code>conn.QueryAsync&lt;Poco&gt;()</code>  do <code>conn.QueryMultiple()</code>, then <code>ReadAsync&lt;Poco&gt;</code>, and if that returns null then <code>ReadAsync&lt;int&gt;</code> for the error code</li>
  </ol>
  
  <p>Is there a better way to handle this?</p>
  
  <p>Example code here:</p>
  
  <p>(note that this is pseudocode and only meant to convey the idea, not actually be an executable example - please don't comment on correcting syntax or usage)</p>
  
  <p>Stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE dbo.SwapData
      @ID1 INT,
      @ID2 INT
  AS
  BEGIN
      DECLARE @Count INT
      DECLARE @Swap Table 
                    (
                        [ID] INT Identity(1,1),
                        [PocoID] INT,
                        [MyData] [nvarchar](255)
                    )
  
      INSERT INTO @Swap
          SELECT PocoID, MyData 
          FROM PocoTable 
          WHERE PocoID IN (@ID1, @ID2)
  
      SELECT @Count = COUNT(1) FROM @Swap
  
      IF @Count &lt;&gt; 2
      BEGIN
          SELECT TOP 0 NULL FROM PocoTable
          SELECT -1 AS ErrorCode
          RETURN -1 -- never used in C# just returning non-0 for convention
      END
  
      UPDATE ORIG
      SET ORIG.MyData = swap.MyData
      FROM PocoTable AS ORIG 
      INNER JOIN @Swap AS swap ON (ORIG.PocoID = @ID1 AND swap.ID = 2)
                               OR (ORIG.PocoID = @ID2 AND swap.ID = 1)
  
      SELECT PocoID, MyData 
      FROM PocoTable 
      WHERE PocoID = @ID1
  END
  </code></pre>
  
  <p>Repository:</p>
  
  <pre><code>public class Repo
  {
      public Task&lt;Poco&gt; SwapData(int targetId, int sourceId)
      {
          using (var conn = GetSqlConnection())
          {
              await conn.OpenAsync();
              var mrs = await conn.QueryMultipleAsync(
                  "dbo.SwapData", 
                  new { ID1 = targetId, ID2 = sourceId }, 
                  commandType: CommandType.StoredProcedure);
  
              var result = await mrs.ReadFirstOrDefaultAsync&lt;TType&gt;();
              if (result != null) return result;
  
              var errorCode = await mrs.ReadFirstAsync&lt;int&gt;();
  
              throw new PocoRepositorySwapException(errorCode);
          }
      }
  }
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I've discovered a nice solution that works <em>synchronously</em> but NOT <em>asynchronously</em>!</p>
  
  <p>Stored procedure w/ simple return:</p>
  
  <pre><code>CREATE PROCEDURE dbo.SwapData
      @ID1 INT,
      @ID2 INT
  AS
  BEGIN
      DECLARE @Count INT
      DECLARE @Swap Table 
                    (
                        [ID] INT Identity(1,1),
                        [PocoID] INT,
                        [MyData] [nvarchar](255)
                    )
  
      INSERT INTO @Swap
          SELECT PocoID, MyData 
          FROM PocoTable 
          WHERE PocoID IN (@ID1, @ID2)
  
      SELECT @Count = COUNT(1) FROM @Swap
  
      IF @Count &lt;&gt; 2
      BEGIN
          RETURN -1
      END
  
      UPDATE ORIG
      SET ORIG.MyData = swap.MyData
      FROM PocoTable AS ORIG 
      INNER JOIN @Swap AS swap ON (ORIG.PocoID = @ID1 AND swap.ID = 2)
                               OR (ORIG.PocoID = @ID2 AND swap.ID = 1)
  
      SELECT PocoID, MyData 
      FROM PocoTable 
      WHERE PocoID = @ID1
  END
  </code></pre>
  
  <p>Repository (synchronous, works):</p>
  
  <pre><code>public class Repo
  {
      public Task&lt;Poco&gt; SwapData(int targetId, int sourceId)
      {
          using (var conn = GetSqlConnection())
          {
              var p = new { ID1 = targetId, ID2 = sourceId };
              var pWithReturnValue = new DynamicParameters(p);
              p.Add("return", null, DbType.Int32, ParameterDirection.ReturnValue);
              // no exception when synchronous
              var result = conn.Query&lt;Poco&gt;("dbo.SwapData", p, commandType: CommandType.StoredProcedure);
  
              var errorCode = p.Get&lt;int&gt;("return");
              if (errorCode != 0)
                  throw new SwapRepositoryException(errorCode);
  
              return result.FirstOrDefault();
          }
      }
  }
  </code></pre>
  
  <p>Repository (asynchronous, Dapper exception):</p>
  
  <pre><code>public class Repo
  {
      public Task&lt;Poco&gt; SwapData(int targetId, int sourceId)
      {
          using (var conn = GetSqlConnection())
          {
              var p = new { ID1 = targetId, ID2 = sourceId };
              var pWithReturnValue = new DynamicParameters(p);
              p.Add("return", null, DbType.Int32, ParameterDirection.ReturnValue);
              // "InvalidOperationException: No columns were selected" happens here when async
              var result = await conn.QueryAsync&lt;Poco&gt;("dbo.SwapData", p, commandType: CommandType.StoredProcedure);
  
              var errorCode = p.Get&lt;int&gt;("return");
              if (errorCode != 0)
                  throw new SwapRepositoryException(errorCode);
  
              return result.FirstOrDefault();
          }
      }
  }
  </code></pre>
  
  <p>The only change is the Query to QueryAsync, and that results in an InvalidOperationException.</p>