如何使用Dapper获取存储过程的返回值？(How do I use Dapper to get the return value of stored proc?)

如何使用Dapper获取存储过程的返回值？(How do I use Dapper to get the return value of stored proc?)

我在asp.net mvc 4项目中使用Dapper .net f / w 4.6.1使用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

我的回购方法使用这样的小巧玲珑

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"); }

每次我得到的returnValue都是可以理解的，因为dapper返回受影响的行数。

不过，我想获得我的存储过程的返回语句的值，以检查事务性删除期间的错误（在我的情况下，对于成功为0，对于任何错误为1）

我如何实现这一目标？

随着裸机ado.net我曾经这样做，它的工作

var returnValue = db.ExecuteScalar(storedProcName, new object[] { feedbackId });

随着精简我已经尝试过con.ExecuteScalar这不起作用，因为小巧的元数据揭示了标量//返回：//第一个单元格被选中

任何帮助将不胜感激？

下面是我需要用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

I'm using Dapper in asp.net mvc 4 project .net f/w 4.6.1 using sql server 2016 express

<packages> <package id="Dapper" version="1.50.2" targetFramework="net461" /> </packages>

I have a stored proc which deletes from 2 tables which should be transactional

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

My repo method uses dapper like this

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 returnValue I get is 1 each time which is understandable since dapper returns the number of rows affected.

However I want to get to the value of the return statement of my stored proc to check for errors during transactional delete (which in my case is 0 for success and 1 for any error)

How do I achieve this?

With bare metal ado.net I used to do this and it worked

var returnValue = db.ExecuteScalar(storedProcName, new object[] { feedbackId });

With dapper I have tried con.ExecuteScalar which does not work since dapper metadata reveals that scalar // Returns: //The first cell selected

Any help will be appreciated?

Here is the next procedure that I need to execute with 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

最满意答案

您可以用方向声明动态参数：ReturnValue您也可以使用“select”而不是“return”并使用Query<T>扩展名。

create procedure sp_foo as begin return 99 end [Test] public void TestStoredProcedure() { using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo")) { var p = new DynamicParameters(); p.Add("@foo", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); conn.Execute("sp_foo", p, commandType: CommandType.StoredProcedure); var b = p.Get<int>("@foo"); Assert.That(b, Is.EqualTo(99)); } }

You can declare dynamic params with direction: ReturnValue You can also use "select" instead of "return" and use Query<T> extension.

create procedure sp_foo as begin return 99 end [Test] public void TestStoredProcedure() { using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo")) { var p = new DynamicParameters(); p.Add("@foo", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); conn.Execute("sp_foo", p, commandType: CommandType.StoredProcedure); var b = p.Get<int>("@foo"); Assert.That(b, Is.EqualTo(99)); } }如何使用Dapper获取存储过程的返回值？(How do I use Dapper to get the return value of stored proc?)

我在asp.net mvc 4项目中使用Dapper .net f / w 4.6.1使用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

我的回购方法使用这样的小巧玲珑

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"); }

每次我得到的returnValue都是可以理解的，因为dapper返回受影响的行数。

不过，我想获得我的存储过程的返回语句的值，以检查事务性删除期间的错误（在我的情况下，对于成功为0，对于任何错误为1）

我如何实现这一目标？

随着裸机ado.net我曾经这样做，它的工作

var returnValue = db.ExecuteScalar(storedProcName, new object[] { feedbackId });

随着精简我已经尝试过con.ExecuteScalar这不起作用，因为小巧的元数据揭示了标量//返回：//第一个单元格被选中

任何帮助将不胜感激？

下面是我需要用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

I'm using Dapper in asp.net mvc 4 project .net f/w 4.6.1 using sql server 2016 express

<packages> <package id="Dapper" version="1.50.2" targetFramework="net461" /> </packages>

I have a stored proc which deletes from 2 tables which should be transactional

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

My repo method uses dapper like this

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 returnValue I get is 1 each time which is understandable since dapper returns the number of rows affected.

However I want to get to the value of the return statement of my stored proc to check for errors during transactional delete (which in my case is 0 for success and 1 for any error)

How do I achieve this?

With bare metal ado.net I used to do this and it worked

var returnValue = db.ExecuteScalar(storedProcName, new object[] { feedbackId });

With dapper I have tried con.ExecuteScalar which does not work since dapper metadata reveals that scalar // Returns: //The first cell selected

Any help will be appreciated?

Here is the next procedure that I need to execute with 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

最满意答案

您可以用方向声明动态参数：ReturnValue您也可以使用“select”而不是“return”并使用Query<T>扩展名。

create procedure sp_foo as begin return 99 end [Test] public void TestStoredProcedure() { using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo")) { var p = new DynamicParameters(); p.Add("@foo", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); conn.Execute("sp_foo", p, commandType: CommandType.StoredProcedure); var b = p.Get<int>("@foo"); Assert.That(b, Is.EqualTo(99)); } }

You can declare dynamic params with direction: ReturnValue You can also use "select" instead of "return" and use Query<T> extension.

create procedure sp_foo as begin return 99 end [Test] public void TestStoredProcedure() { using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo")) { var p = new DynamicParameters(); p.Add("@foo", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); conn.Execute("sp_foo", p, commandType: CommandType.StoredProcedure); var b = p.Get<int>("@foo"); Assert.That(b, Is.EqualTo(99)); } }