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