我可以在模板中使用DynamicParameters并在dapper中使用返回参数吗? [英] Can I use DynamicParameters with Template and have a return parameter in dapper?
问题描述
我当前正在使用的系统使用存储过程进行所有数据访问。目前,我正在研究Dapper(到目前为止看起来不错),但我想知道是否可以使用通过Template创建的DynamicParameters对象,但可以将其中一个参数设为输出参数。例如:
The system I am currently working on uses Stored Procedures for all data access. I'm looking into Dapper at the moment (so far it looks great) but I was wondering if I can use a DynamicParameters object created using a Template but make one of the parameters an output param. For example:
SP:
CREATE PROCEDURE InsertPerson
@ID int Output,
@Name varchar(100),
@DOB DateTime2
AS
--INSERT STATEMENT
SET @ID = SCOPE_IDENTITY()
POCO:
internal class Person
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime DOB { get; set; }
}
代码:
var procParams = new DynamicParameters(person);
connection.Execute("InsertPerson", procParams, commandType: CommandType.StoredProcedure);
// This is where i'm having the issue, can it be done?
person.ID = procParams.Get<int>("ID");
当前我收到一个错误,因为找不到密钥。有没有一种方法可以在不手动设置所有存储的procs参数的情况下获取ID输出参数?
Current I receive an error because the key was not found. Is there a way to get the ID output parameter without manually setting up all of the stored procs parameters?
推荐答案
,添加
现在替换模板中的值,允许:
With a quick tweak, Add
now replaces the value from a template, allowing:
public void TestProcWithOutParameter()
{
connection.Execute(
@"CREATE PROCEDURE #TestProcWithOutParameter
@ID int output,
@Foo varchar(100),
@Bar int
AS
SET @ID = @Bar + LEN(@Foo)");
var obj = new
{ // this could be a Person instance etc
ID = 0,
Foo = "abc",
Bar = 4
};
var args = new DynamicParameters(obj);
args.Add("ID", 0, direction: ParameterDirection.Output);
connection.Execute("#TestProcWithOutParameter", args,
commandType: CommandType.StoredProcedure);
args.Get<int>("ID").IsEqualTo(7);
}
距离足够近吗?您还可以使用 ParameterDirection.ReturnValue
,既可以是预先存在的值,也可以是新值。请注意,它不不会直接更新回原始模板;该值必须从 DynamicParameters
实例中获取(如图所示)。
Is that close enough? You can also use ParameterDirection.ReturnValue
, of either a pre-existing value or a new value. Note it does not update directly back into the original template; the value must be fetched from the DynamicParameters
instance (as shown).
这篇关于我可以在模板中使用DynamicParameters并在dapper中使用返回参数吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!