C# 使用存储过程从 Dapper.net 查询返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14247081/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return Values from Dapper.net query with stored procedure
提问by yellowfever
I am trying to call a stored procedure using Dapper.Netand get return values.
我正在尝试调用存储过程using Dapper.Net并获取返回值。
p.Add("@INCIDENT_ID", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var retResults = con.Execute("usp_GetIncidentID", p, commandType:CommandType.StoredProcedure);
int IncidentID = p.Get<int>("INCIDENT_ID");
I have tried a couple of different things with the parameter direction and using the "@INCIDENT_ID". If you step through the results, you can see that the proper return values are coming down in the retResultsvalue, but I am not able to access the values the way it is described in the documentation as below..
我已经尝试了一些不同的参数方向和使用"@INCIDENT_ID". 如果您逐步查看结果，您可以看到正确的返回值在值中下降retResults，但我无法按照文档中描述的方式访问这些值，如下所示。
Stored Procedures Dapper supports fully stored procs:
存储过程 Dapper 支持完全存储过程：
var user = cnn.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).First();}}}
If you want something more fancy, you can do:
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);
cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure);
int b = p.Get<int>("@b");
int c = p.Get<int>("@c");
采纳答案by GPR
You can read the value as below
您可以读取如下值
var incidentId = retResults.ToList()[0].INCIDENT_ID;
回答by Marc Gravell
I suspect(untested) that this is purely a mismatch in how you name the parameter; try (note the removed @):
我怀疑（未经测试）这纯粹是您命名参数的方式不匹配；尝试（注意已删除@）：
p.Add("INCIDENT_ID", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var retResults = con.Execute("usp_GetIncidentID", p, commandType:CommandType.StoredProcedure);
int IncidentID = p.Get<int>("INCIDENT_ID");
回答by Dustin Geile
I ran into a similar problem reading the results of a QueryMultiple. The first call to Read() returned the correct type, the second returned a DapperRow. I found that using the typed version of Read():
我在读取 QueryMultiple 的结果时遇到了类似的问题。第一次调用 Read() 返回正确的类型，第二次调用返回 DapperRow。我发现使用 Read() 的类型化版本：
var lineExists = query.Read<int?>().FirstOrDefault() == 1;
solved my problem.
解决了我的问题。
回答by Don Rolling
I've pared this down for simplicity. I'm using output parameters because that gives me full control of the datatype that is passed back. I could use this approach with any other scenario, not just inserts.
为简单起见，我已经缩减了这一点。我正在使用输出参数，因为这使我可以完全控制传回的数据类型。我可以将这种方法用于任何其他场景，而不仅仅是插入。
Stored Proc:
存储过程：
ALTER PROCEDURE User_Insert (
@EmailAddress nvarchar(255),
@Id bigint OUT
) AS
INSERT INTO User (
[EmailAddress]
) VALUES (
@EmailAddress
)
set @Id = SCOPE_IDENTITY()
Respository Code:
存储库代码：
var sql = "Execute User_Insert @EmailAddress, @Id = @Id OUTPUT";
var _params = new DynamicParameters();
_params.Add("EmailAddress", user.EmailAddress);
_params.Add("Id", dbType: DbType.Int64, direction: ParameterDirection.Output);
using (var connection = new SqlConnection(ConnectionString)) {
connection.Open();
using (var transaction = connection.BeginTransaction()) {
var result = SqlMapper.Execute(connection, sql, param, transaction);
transaction.Commit();
}
}
var id = _params.Get<long>("Id");
回答by Timothy Dooling
Using the test version of Dapper, I found this works like a charm:
使用 Dapper 的测试版本，我发现这很有效：
result = dbConnection.ExecuteScalar<int>(typeof(UCCCCException).Name + "_c",
obj, commandType: CommandType.StoredProcedure);
I am writing a generic for use in inserting objects of any type into a database.
我正在编写一个泛型，用于将任何类型的对象插入到数据库中。
The stored procedure looks like this:
存储过程如下所示：
ALTER PROCEDURE [dbo].[UCCCCException_c]
(
@Id int = null,
@ExceptionDate datetime = null,
@HResult int = 0,
@Message varchar(8000) = null,
@Source varchar(8000) = null,
@StackTrace varchar(8000) = null,
@Module varchar(8000) = null,
@Name varchar(8000) = null,
@created_at datetime = null,
@updated_at datetime = null,
@created_by int = null,
@updated_by int = null
,
@Creator varchar(255) = null,
@Updator varchar(255) = null
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert into dbo.uccccexceptions ( ExceptionDate, HResult, [Message], [Source], StackTrace, Module, Name)
values (
coalesce(@ExceptionDate,getdate()),
@HResult,
@Message,
@Source,
@StackTrace,
@Module,
@Name
)
;
select @@Identity;
go
The caveats on this are that you must specify an entry for every property in the class you are using, even if those properties do not exist in the target table, as a parameter for the stored procedure. This can be annoying if you have a number of fields which are view fields in MVC situations.
对此的警告是，您必须为正在使用的类中的每个属性指定一个条目作为存储过程的参数，即使目标表中不存在这些属性。如果您有许多字段是 MVC 情况下的视图字段，这可能会很烦人。
To get the return value, you just have to use Execute, and make sure your last statement is Select @@Identity in the stored procedure.
要获取返回值，您只需使用 Execute，并确保您的最后一条语句是存储过程中的 Select @@Identity。
It works perfectly, and allows me to write a generic insert command in my repository like this:
它工作得很好，并允许我在我的存储库中编写一个通用的插入命令，如下所示：
public virtual int Insert(T obj)
{
int result = -2;
if (!databaseOnline)
{
throw new Exception(HttpStatusCode.ServiceUnavailable.ToString());
}
if (obj != null)
{
try
{
using (IDbConnection dbConnection = ConnectionProvider.OpenConnection())
{
dbConnection.Open();
result = dbConnection.ExecuteScalar<int>(typeof(T).Name + "_c",
obj, commandType: CommandType.StoredProcedure);
}
}
catch (SqlException sqlex)
{
Logger.LogError(sqlex, false);
throw;
}
catch (Exception ex)
{
Logger.LogError(ex);
throw;
}
}
return result;
}
I use the convention in my database that the name of the stored procedure is the type name followed by "_s" for select, "_c" for insert, "_d" for delete and "_u" for update.
我在我的数据库中使用约定，存储过程的名称是类型名称后跟“_s”表示选择，“_c”表示插入，“_d”表示删除，“_u”表示更新。
PS: I hate using Dapper's DynamicParameters or any other device that requires you to use a different insert or update method for each of your classes in a generic repository.
PS：我讨厌使用 Dapper 的 DynamicParameters 或任何其他要求您对通用存储库中的每个类使用不同的插入或更新方法的设备。
回答by Houssam Hamdan
The DynamicParameters solution is not clean to me. It is much better to make the stored procedure code return an integer (Select 1;) and use dapper ExecuteScalar<int>("[sp_name]", params);
DynamicParameters 解决方案对我来说并不干净。最好让存储过程代码返回一个整数（Select 1;）并使用dapperExecuteScalar<int>("[sp_name]", params);
