title: How do I use Dapper to get the return value of stored proc?
body: <p>I'm using Dapper in asp.net mvc 4 project .net f/w 4.6.1 using sql server 2016 express</p>
  
  <pre><code>&lt;packages&gt;
    &lt;package id="Dapper" version="1.50.2" targetFramework="net461" /&gt;
  &lt;/packages&gt; 
  </code></pre>
  
  <p>I have a stored proc which deletes from 2 tables which should be transactional </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[FeedbackDelete] @FeedbackID UNIQUEIDENTIFIER
  AS 
      SET NOCOUNT OFF 
      SET XACT_ABORT ON  
  
  BEGIN TRANSACTION
      DELETE
      FROM dbo.Document
      WHERE FeedbackId = @FeedbackID
      IF(@@ERROR != 0)
          BEGIN
              ROLLBACK TRANSACTION
              RETURN 1
          END
  
      DELETE
      FROM   [dbo].[Feedback]
      WHERE  [FeedbackID] = @FeedbackID
      IF(@@ERROR != 0 OR @@ROWCOUNT != 1)
          BEGIN
              ROLLBACK TRANSACTION
              RETURN 1
          END
  
  COMMIT TRANSACTION
  RETURN 0
  </code></pre>
  
  <p>My repo method uses dapper like this</p>
  
  <pre><code>public Response DeleteFeedback(Guid feedbackId)
          {
              string storedProcName = "FeedbackDelete";
              int returnValue = int.MinValue;
              using (var con = Connection)
              {
                  con.Open();
                  returnValue = con.Execute(storedProcName, new { feedbackId }, commandType: CommandType.StoredProcedure);
              }
              return Convert.ToInt32(returnValue) == 0 ? new Response(Code.Success, "Feedback successfully deleted") : new Response(Code.Failure, "There was an error deleting feedback");
          }
  </code></pre>
  
  <p>The <strong>returnValue</strong> I get is 1 each time which is understandable since dapper returns the number of rows affected. </p>
  
  <p>However I want to get to the value of the return statement of my stored proc to check for errors during transactional delete (which in my case is 0 for success and 1 for any error)</p>
  
  <p>How do I achieve this?</p>
  
  <p>With bare metal ado.net I used to do this and it worked</p>
  
  <pre><code>var returnValue = db.ExecuteScalar(storedProcName, new object[] { feedbackId });
  </code></pre>
  
  <p>With dapper I have tried con.ExecuteScalar which does not work since dapper metadata reveals that scalar  // Returns: //The first cell selected</p>
  
  <p>Any help will be appreciated?</p>
  
  <p>Here is the next procedure that I need to execute with Dapper</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[FeedbackUpdate] 
      @DocumentData VARBINARY(MAX),
      @DocumentName NVARCHAR(100),
      @FeedbackID UNIQUEIDENTIFIER,
      @FirstName NVARCHAR(100),
      @LastName NVARCHAR(100),
      @Notes NVARCHAR(MAX)
  AS 
      SET NOCOUNT ON 
      SET XACT_ABORT ON  
  
      BEGIN TRAN
  
      UPDATE [dbo].[Feedback]
      SET    [FirstName] = @FirstName, [LastName] = @LastName, [Notes] = @Notes
      WHERE  [FeedbackID] = @FeedbackID
      IF(@@ERROR != 0 OR @@ROWCOUNT != 1)
          BEGIN
              ROLLBACK TRAN
              RETURN 1
          END
  
      IF DATALENGTH(@DocumentData) &gt; 1
      BEGIN
  
              DELETE
              FROM   [dbo].[Document]
              WHERE  FeedbackId = @FeedbackId
              IF(@@ERROR != 0)
                  BEGIN
                      ROLLBACK TRAN
                      RETURN 1
                  END
  
              INSERT [dbo].[Document] (DocumentData,DocumentName,DocumentId,FeedbackId)
              VALUES(@DocumentData,@DocumentName,NEWID(),@FeedbackID)
              IF(@@ERROR != 0 OR @@ROWCOUNT != 1)
                  BEGIN
                      ROLLBACK TRAN
                      RETURN 1
                  END
          END
  
          COMMIT TRAN
          RETURN 0
  </code></pre>