codeshinobi codeshinobi - 3 months ago 9
C# Question

Execution status on INSERT and UPDATE - MS SQL Server

I am having a somewhat frustrating issue.

On the lower level: I want to be able to know whether an INSERT or UPDATE query in a proc was successful or not. I am not 100% sure if there is a status that is returned on all queries (apart from the SELECT). I know SQL Server gives a return type to all stored procs, and currently all mine have a return type of Integer.

On the middle level: In my repository, I want to use Entity Framework to call my stored procs and return the status as a converted (from int) boolean from the proc execution to my service.

On the higher level: I want to be able to use the returned boolean from my service to report back to an MVC controller on the task that was being performed.

On the most important layer of my issue, I have the following code:

public virtual ObjectResult<int> Proc_AddApprovalProcessor(string userId, string approverId, int approvalOrder)
{
return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<int>(
"EXECUTE [dbo].[Proc_AddApprovalProcessor] @userId, @approverId, @approvalOrder",
new SqlParameter { ParameterName = "userId", Value = userId },
new SqlParameter { ParameterName = "approverId", Value = approverId },
new SqlParameter { ParameterName = "approvalOrder", Value = approvalOrder }).FirstOrDefault();
}

Answer

Make sure your insert/update stored procedures end with the following line:

SELECT @@ROWCOUNT

Then examine the return code and check that it is 1 (or however many rows you were expecting to insert/update).