在C＃代码中使用dapper将输出参数传递给存储过程 [英] Passing Output parameters to stored procedure using dapper in c# code

问题描述

我具有这种格式的存储过程

I have a stored procedure in this format

CREATE PROCEDURE SP_MYTESTpROC
    @VAR1 VARCHAR(10),
    @VAR2 VARCHAR(20),
    @BASEID INT ,
    @NEWID INT OUTPUT
As Begin
   INSERT INTO TABLE_NAME(username, firstname)
      select @VAR1, @VAR2 
      WHERE ID = @BASEID

   SET @NEWID = SCOPE_IDENTITY() AS INT
END

我正在使用dapper从C＃代码调用此存储过程。我的问题是：如何在使用dapper时将输出参数传递给存储过程？

I am calling this stored procedure from C# code using dapper. My question is: how do I pass in the output parameter to the stored procedure while using dapper?

推荐答案

只需搜索 Test.cs 文件，您可以找到此示例

Just searching the Test.cs file you could find this example

    public void TestProcSupport()
    {
        var p = new DynamicParameters();
        p.Add("a", 11);
        p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
        p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
        connection.Execute(@"create proc #TestProc 
                         @a int,
                             @b int output
                             as 
                             begin
                                 set @b = 999
                                 select 1111
                                 return @a
                             end");
        connection.Query<int>("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);
        p.Get<int>("c").IsEqualTo(11);
        p.Get<int>("b").IsEqualTo(999);
    }

因此，我想您的C＃代码可以写为

So, I suppose that your C# code could be written as

    public void InsertData()
    {
        var p = new DynamicParameters();
        p.Add("VAR1", "John");
        p.Add("VAR2", "McEnroe");
        p.Add("BASEID", 1);
        p.Add("NEWID", dbType: DbType.Int32, direction: ParameterDirection.Output);
        connection.Query<int>("SP_MYTESTpROC", p, commandType: CommandType.StoredProcedure);
        int newID =  p.Get<int>("NEWID");
    }

请注意，请勿将SP用作存储过程的前缀。它保留用于系统定义的过程，如果Microsoft决定使用相同的名称，则可能会遇到麻烦。尽管不太可能，但这是一种不好的做法，为什么要冒险？

As a side note, do not use SP as prefix for your stored procedure. It is reserved for system defined procedures and you could find yourself in troubles if Microsoft decides to use the same name. Albeit improbable it is a bad practice and why risk?

这篇关于在C＃代码中使用dapper将输出参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！