0

I have c# code which is calling stored procedure; responsible to delete record from multiple tables. I cannot read output parameters from stored procedure in c# class, although I can read output parameter when I execute stored procedure from sql server management studio.

Stored Procedure

ALTER PROCEDURE [dbo].[DeleteFunctionsNavigation]

 @FunctionID INT,
 @Action_identity INT OUTPUT,
 @ActionInFunction_Count INT OUT,
 @Controller_identity INT OUTPUT,
 @FunctionDeleted INT OUTPUT

  AS
  BEGIN

SET NOCOUNT ON;

SELECT @Action_identity = Navigation_FunctionInAction.ActionID 
FROM Navigation_FunctionInAction
WHERE Navigation_FunctionInAction.Function_ID = @FunctionID

SELECT @ActionInFunction_Count = COUNT(Navigation_FunctionInAction.ActionID) FROM Navigation_FunctionInAction WHERE Navigation_FunctionInAction.ActionID =@Action_identity

SELECT @Controller_identity = Navigation_FunctionInController.ControllerID
FROM Navigation_FunctionInController
WHERE Navigation_FunctionInController.Function_ID = @FunctionID

DELETE FROM Navigation_FunctionHierarchy
WHERE Navigation_FunctionHierarchy.Function_IDs = @FunctionID

DELETE FROM Navigation_FunctionInAction
WHERE Navigation_FunctionInAction.Function_ID = @FunctionID

IF(@ActionInFunction_Count<=1)
Begin

    DELETE FROM Navigation_FunctionAction
    WHERE Navigation_FunctionAction.ActionID = @Action_identity
End

DELETE FROM Navigation_FunctionInController
WHERE Navigation_FunctionInController.ControllerID = @Controller_identity

Delete FROM Navigation_Functions 
WHERE Navigation_Functions.Function_ID = @FunctionID

--Check if deleted function exist... to find if record been deleted or not .. 0 means deleted >0 means not deleted
SELECT @FunctionDeleted = COUNT(Navigation_Functions.FunctionName) FROM Navigation_Functions WHERE Navigation_Functions.Function_ID = @FunctionID

RETURN @FunctionDeleted  
END

c# class

public void DeleteNavigationFunctionByID(int _FunctionNavigationID)
   {
       using (var dbContext = new FunctionContext())
       {
           List<DeleteFunctionNavigation_SP_Map> _query;

           //int _functionDeleted = -1;

           var Action_identity_out = new SqlParameter("Action_identity", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
           var ActionInFunction_Count_out = new SqlParameter("ActionInFunction_Count", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
           var Controller_identity_out = new SqlParameter("Controller_identity", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
           var FunctionDeleted_out = new SqlParameter("FunctionDeleted", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };

           var _functionDeleted = dbContext.Database.SqlQuery<DeleteFunctionNavigation_SP_Map>("exec DeleteFunctionsNavigation @FunctionID, @Action_identity out, @ActionInFunction_Count out, @Controller_identity out, @FunctionDeleted out",
                     new SqlParameter("@FunctionID", _FunctionNavigationID),
                     Action_identity_out,
                     ActionInFunction_Count_out,
                     Controller_identity_out,
                     FunctionDeleted_out
           );
       }
}
K.Z
  • 5,201
  • 25
  • 104
  • 240

0 Answers0