我在 asp.net mvc 4 项目 .net f/w 4.6.1 中使用 Dapper 使用 sql server 2016 Express
<packages>
<package id="Dapper" version="1.50.2" targetFramework="net461" />
</packages>
我有一个存储过程,它从 2 个应该是事务性的表中删除
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
我的回购方法使用这样的 dapper
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");
}
The 返回值我每次得到的都是 1,这是可以理解的,因为 dapper 返回受影响的行数。
不过,我想获取存储过程的 return 语句的值,以检查事务删除期间是否有错误(在我的例子中,0 表示成功,1 表示任何错误)
我该如何实现这一目标?
使用裸机 ado.net,我曾经这样做过并且有效
var returnValue = db.ExecuteScalar(storedProcName, new object[] { feedbackId });
使用 dapper 我尝试了 con.ExecuteScalar ,但它不起作用,因为 dapper 元数据显示该标量 // Returns: //The first cell selected
任何帮助将不胜感激?
这是我需要使用 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