How do I use Dapper to get the return value of stored proc?

Question

I'm using Dapper in asp.net mvc 4 project .net f/w 4.6.1 using sql server 2016 express

<packages>
  <package id="Dapper" version="1.50.2" targetFramework="net461" />
</packages> 

I have a stored proc which deletes from 2 tables which should be transactional

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

My repo method uses dapper like this

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");
        }

The returnValue I get is 1 each time which is understandable since dapper returns the number of rows affected.

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)

How do I achieve this?

With bare metal ado.net I used to do this and it worked

var returnValue = db.ExecuteScalar(storedProcName, new object[] { feedbackId });

With dapper I have tried con.ExecuteScalar which does not work since dapper metadata reveals that scalar // Returns: //The first cell selected

Any help will be appreciated?

Here is the next procedure that I need to execute with Dapper

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) > 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

Tenne

This is completely off topic, but will save you some major headaches. Most of the time your query will be much, much faster if you define local variables, set those to your input parameters, and use your local variables in your query. I've gotten an easy 10x performance out of most SPROCs by doing that single thing. Sorry for the distraction, but you will thank me later. – Juice

#14247581 – Juice

@Juice Thanks for your interesting tip. Any explanation about why performance increases before I bring this up with my team and update stored procs. I would need a strong rational case for the upheaval ;-) – Tenne

in researching the source for you, I learned something new. You can do the same thing with a one line statement changing the way sql optimizes. Note, the web will point out cases in which it doesn't always work, but it has always worked for me... In a big way. https ://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/ – Juice

@Juice Thanks for the source. Have you seen the discussion here #14469103 – Tenne

Yes I have. I can only say that it has consistently worked for me, and sometimes have taken 10-sec queries and made them 100ms queries. Like everything in the DB world, measure, measure, measure - just keep it in your tool kit as an option. – Juice

Answer 1

You can declare dynamic params with direction: ReturnValue You can also use "select" instead of "return" and use Query<T> extension.

create procedure sp_foo
    as
    begin
        return 99
    end

[Test]
public void TestStoredProcedure()
{
    using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo"))
    {
        var p = new DynamicParameters();
        p.Add("@foo", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

        conn.Execute("sp_foo", p, commandType: CommandType.StoredProcedure);

        var b = p.Get<int>("@foo");

        Assert.That(b, Is.EqualTo(99));
    }
}

Brothers

I used select in my stored proc and Query<T> extension. I did not want to use DynamicParameters since I use reflection to add params to stored procs – Tenne

@void-Ray is it possible to return string too like int? The problem is that I'm getting an error if I'm trying to return a string Conversion failed when converting the varchar value 'Success from Procedure' to data type int. – Thessalonian

Changing return datatype to DbType.String and p.Get<string>("@foo") should work – Brothers

Stumbled on this question, but @HamzaAhmedZia DynamicParameters has a method called AddDynamicParams where you can automatically add in your existing object and not have to manually map all the parameters. Then just add the parameter for the output, and you are good to go. – Iconoduly

I used kind of similar approach to Hamza Ahmed Zia, but QuerySingle<int> instead and my proc is returning integers like (-1,-2, 1) – Autotype

The approved solution doesn't work with mysql 5.7.1. that needs select (not return) and dappers consider it a function, even after specifying commandType: CommandType.StoredProcedure – Autotype

Answer 2

you'll have to define a SqlParameter for your stored procedure with a Direction.ReturnValue

Hershberger

And can this be done with Dapper which typically takes a dynamic object for parameters? – Downstage

Answer 3

csharp/server side

var result = conn.QuerySingle<int>(
                "sp_test",
                new
                {
                    param1 = "something"
                },
                commandType: CommandType.StoredProcedure
            );

and the stored procedure

create procedure sp_test(varchar(255) param1)
    as
    begin
        if param1 = "bla bla" then -- or some other validation against param1
           select -1;
        end if;

        -- do something

        select 1; -- if reached this point everything is fine
    end

Approved solution didn't work for me using mysql 5.7.1. So i'm just posting my solution here. result will be 1 for the example and and -1 if param1 is changed to something

Autotype