如何使用Dapper获取存储过程的返回值? [英] How do I use Dapper to get the return value of stored proc?

问题描述

我正在使用SQL Server 2016 Express在ASP.NET MVC 4项目.NET F/W 4.6.1中使用Dapper

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> 

我有一个存储的proc，它从2个应该是事务性的表中删除

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

我的repo方法使用像这样的dapper

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

每次获得的 returnValue 都是1，这是可以理解的，因为dapper返回受影响的行数.

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

但是我想获取存储的proc的return语句的值来检查事务删除期间的错误(在我的情况下，成功则为0，任何错误为1)

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)

我该如何实现?

我曾经使用裸金属ado.net做到这一点，并且效果很好

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

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

使用dapper，我尝试了con.ExecuteScalar，该方法不起作用，因为dapper元数据显示该标量//返回://选择的第一个单元格

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

有什么帮助吗?

这是我需要用Dapper执行的下一个步骤

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

推荐答案

您可以声明动态参数，其方向为:ReturnValue 您也可以使用选择"代替返回"，并使用Query<T>扩展名.

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

这篇关于如何使用Dapper获取存储过程的返回值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！