从存储过程获取输出参数的另一种方法 [英] Alternative way to get output parameter from stored procedure

问题描述

我喜欢使用Dapper满足我的ORM需求，但我知道必须有更好的方法使用存储过程和强类型列表插入/更新我的sql server数据库。

I love using Dapper for my ORM needs but I know there must be a better way to insert/update my sql server database using a stored procedure and strongly typed Lists.

例如：

我上了一堂课：

    public class Song
{
    public int Id { get; set; }
    public string title { get; set; }
    public string genre { get; set; }
}

有人提交了歌曲列表：

 List<Song> songs = new List<Song> { 
            new Song { Id = 1, title = "Song 1" , genre="rock"}, 
            new Song { Id = 2, title = "Song 2" , genre="disco"}};

我想使用存储过程更新数据库，该存储过程要么插入新歌曲，要么在更新歌曲时对其进行更新。歌曲已经存在。我的存储过程有两个输出参数：
@success_added int = 0和@success_updated int = 0

I want to update the database using my stored procedure which either inserts the new song or updates it if the song already exists. My stored procedure has two output parameters: @success_added int = 0 and @success_updated int = 0

我的存储程序如下：

ALTER PROCEDURE [dbo].[UpdateSong] 
@Id int = 0, 
@title varchar(25) = NULL,
@genre varchar(25) = NULL,
@success_updated bit = 0 OUTPUT,
@success_added bit = 0 OUTPUT
AS
IF NOT EXISTS (SELECT Id  FROM Songs WHERE Id = @Id)
    BEGIN
        INSERT INTO Songs
        (
        -- Id created by db
        title,
        genre
        ) 
        VALUES
        (
        @title, 
        @genre
        )
        SELECT @Success_Added = 1, @Success_Updated = 0
    END
ELSE -- song already exists
    BEGIN
        UPDATE Songs  
        SET
        title = @title,
        @genre = @genre
        WHERE Id = @Id
        SELECT @Success_Added = 0, @Success_Updated = 1
    END
RETURN

我知道这可行：

dbConn.Open();
DynamicParameters p = new DynamicParameters();
foreach (var song in songs)
  {
    p.Add("@Id", song.Id);
    p.Add("@title", song.title);
    p.Add("@genre", song.genre);
    p.Add("@success_updated", dbType: DbType.Boolean, direction: ParameterDirection.Output);
    p.Add("@success_added", dbType: DbType.Boolean, direction: ParameterDirection.Output);
    dbConn.Execute("Test_UpdateSong", p, commandType: CommandType.StoredProcedure);
    Console.WriteLine("@success_added: " + p.Get<Boolean>("@success_added"));
    Console.WriteLine("@success_updated: " + p.Get<Boolean>("@success_updated"));
  }
dbConn.Close();

但这需要手动将每个Song属性转换为匿名类型DynamicParameter。我宁愿这样做：

But that requires manually converting each Song property to a anonymous type DynamicParameter. I'd rather simply do this:

dbConn.Open();
foreach (var song in songs)
  {
    var updateResult = dbConn.Query<dynamic>("Test_UpdateSong", song, commandType: CommandType.StoredProcedure);
  }
dbConn.Close();

也可以。但是，现在如何获取输出参数？

Which also works. But, now how do I get my output parameters?

推荐答案

正如我最初所说的那样，我不想手动将每个类属性转换为Dapper动态参数。这很关键，因为如果我创建一个泛型方法，则可能不知道将哪个类传递给该方法，也不知道传递给哪个属性以转换为动态参数。采纳@Metro Smurfs的建议（当其他所有方法都无法阅读说明时），我调查了Dapper测试类，找到了一个适用于我的解决方案：

As I stated originally, I didn't want to have to manually convert each class property to a Dapper dynamic parameter. This is critical because if I create a generic method, I may not know which class is being passed into the method and thus which properties to pass to convert to dynamic parameters. Taking @Metro Smurfs advice (when all else fails read the directions), I looked into the Dapper test class and found a solution that works for me:

DynamicParameters p = new DynamicParameters(song);

通过将歌曲对象添加到DynamicParameters构造函数中，将创建一个DynamicParameters模板，该模板将自动转换所有属性参数。然后，我可以简单地添加两个输出参数并执行sproc：

by adding the song object to the DynamicParameters constructor, a DynamicParameters template is created which will automatically convert all properties to parameters. Then I can simply add my two output parameters and execute the sproc:

p.Add("@success_updated", dbType: DbType.Boolean, direction: ParameterDirection.Output);
p.Add("@success_added", dbType: DbType.Boolean, direction: ParameterDirection.Output);
dbConn.Execute("Test_UpdateSong", p, commandType: CommandType.StoredProcedure);
// get my output parameters...
var success_added = p.Get<bool>("@success_Added");
var success_added = p.Get<bool>("@success_Updated");

我很好！感谢@Nick和@Metro Smurf的建议！

and I'm good to go! Thanks to @Nick and @Metro Smurf for the suggestions!

这篇关于从存储过程获取输出参数的另一种方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！