locked
EF Imported function fails to operate stored procedure RRS feed

  • Question

  • User-1842880510 posted

    This is the fifth attempt I've to post this thread so if there ends up being several duplicates that is why.

    I have an Asp.Net MVC 4 EF 5 database first application.  I have a number of stored procedures in the application that insert, delete, update and select data from the database.  There has never been any issues with their properly running.  Until three days ago.

    I have a stored procedure that deletes a requirement that looks like this.

    ALTER PROCEDURE [dbo].[zt_prc_Requirement_Delete] 
    (
    	@ReqID int,
    	@UserRecID int
    )
    AS
    BEGIN
    		
    		IF EXISTS(SELECT * FROM VFS_ProcurementTracking.dbo.prc_FMS_Requirement_Data FMS WHERE FMS.ReqID = @ReqID)
    		BEGIN
    			DECLARE @FMSRelatedReqInfoID int
    			Set @FMSRelatedReqInfoID = (SELECT FMS.FMSRelatedReqInfoID FROM VFS_ProcurementTracking.dbo.prc_FMS_Requirement_Data FMS WHERE FMS.ReqID = @ReqID)
    			EXECUTE prc_ext_FMSRequirementData_Delete @FMSRelatedReqInfoID, @UserRecID
    		END
    		IF EXISTS(SELECT * FROM VFS_ProcurementTracking.dbo.prc_RequirementRelatedNSNs NSN WHERE NSN.ReqID = @ReqID)
    		BEGIN
    			Execute zt_prc_RequirementRelatedNSNsOneReq_Delete @ReqID, @UserRecID
    		END
    		IF EXISTS(SELECT * FROM VFS_ProcurementTracking.dbo.prc_RequirementRelatedPartNumbers PN WHERE PN.ReqID = @ReqID)
    		BEGIN
    			Execute zt_prc_RequirementRelatedPartNumbersOneReq_Delete @ReqID, @UserRecID
    		END
    		
    		
    		Delete FROM prc_Requirements WHERE ReqID = @ReqID 
    	
    
    
    
    END
    
    

    The EF function Import dialog looks like this: Function Import Name: Requirement_Delete

    Stored Procedure / Function Name: zt_prc_Requirement_Delete

    Returns a collection of None

    The HttpsPost Action I am trying to run looks like this.

          [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Delete(RequirementSummaryViewModel ReqDelete)
            {
                using (var db = new VFS_ProcurementTrackEntities(Session["csProcTrack"].ToString()))
                {
                    try
                    {
                        using (TransactionScope transactionScope = new TransactionScope())
                        {
                            int userRecID = (int)Session["UserRecID"];
                            String checkWoDelete = db.CheckReqExistsInWOClin(ReqDelete.Requirement.ReqID).FirstOrDefault();
                            String CheckCtrDelete = db.CheckReqExistsInCtrSubClin(ReqDelete.Requirement.ReqID).FirstOrDefault();
                            if(CheckCtrDelete == "Pass")
                            {
                                if (checkWoDelete == "Pass")
                                {
                                    db.PRSubLinesRelatedPartNumbersOneRequirement_Delete(ReqDelete.Requirement.ReqID, userRecID);
                                
                                    db.PRSubLinesRelatedPartNumbersOneRequirement_Delete(ReqDelete.Requirement.ReqID, userRecID);
    
                                    db.PRSubLinesOneRequirement_Delete(ReqDelete.Requirement.ReqID, userRecID);
    
                                    db.FundDocLineByReqID_Delete(ReqDelete.Requirement.ReqID, userRecID);
    
                                    db.Requirement_Delete(ReqDelete.Requirement.ReqID, userRecID);
                                }
                                else
                                {
                                    throw new Exception("Unable to Delete Requirement.  It is being used by a Work Order.");
                                }
                            }
                            else
                            {
                                throw new Exception("Unable to Delete Requirement.  It is being used by a Contract.");
                            }
                        }
                        return RedirectToAction("Index");
                    }
                    catch (Exception ex)
                    {
                        String errorReport = ex.Message;
                        if (ex.InnerException != null)
                        {
                            errorReport = "  " + ex.InnerException.Message;
                        }
                        TempData["ER"] = errorReport;
                        return RedirectToAction("ErrorReport", "Home");
                    }
                }
            }
    

    I put a breakpoint on the line using (var db = new VFS_ProcurementTrackEntities(Session["csProcTrack"].ToString()))

    I stepped through the code.

    No exceptions were thrown.

    But when I returned to the index page, the requirement I was trying to delete was still there.

    It was my thought there was something wrong with one of the stored procedures.  I went to SSMS and ran all of the stored procedures in order.  No exceptions were thrown.  The requirement was successfully deleted.

    I found a requirement that had nothing related to it. I commented out everything except Requirement_Delete().  I step through it again.  Again no exceptions.  Again requirement was not deleted.  Again the stored procedure ran fine in SSMS.

    I went into the stored procedure and added a try catch block like this

    ALTER PROCEDURE [dbo].[zt_prc_Requirement_Delete] 
    (
    	@ReqID int,
    	@UserRecID int
    )
    AS
    BEGIN
    	BEGIN TRY
    		
    		IF EXISTS(SELECT * FROM VFS_ProcurementTracking.dbo.prc_FMS_Requirement_Data FMS WHERE FMS.ReqID = @ReqID)
    		BEGIN
    			DECLARE @FMSRelatedReqInfoID int
    			Set @FMSRelatedReqInfoID = (SELECT FMS.FMSRelatedReqInfoID FROM VFS_ProcurementTracking.dbo.prc_FMS_Requirement_Data FMS WHERE FMS.ReqID = @ReqID)
    			EXECUTE prc_ext_FMSRequirementData_Delete @FMSRelatedReqInfoID, @UserRecID
    		END
    		IF EXISTS(SELECT * FROM VFS_ProcurementTracking.dbo.prc_RequirementRelatedNSNs NSN WHERE NSN.ReqID = @ReqID)
    		BEGIN
    			Execute zt_prc_RequirementRelatedNSNsOneReq_Delete @ReqID, @UserRecID
    		END
    		IF EXISTS(SELECT * FROM VFS_ProcurementTracking.dbo.prc_RequirementRelatedPartNumbers PN WHERE PN.ReqID = @ReqID)
    		BEGIN
    			Execute zt_prc_RequirementRelatedPartNumbersOneReq_Delete @ReqID, @UserRecID
    		END
    		
    		
    		Delete FROM prc_Requirements WHERE ReqID = @ReqID 
    	
    	END TRY
    	BEGIN CATCH
    		DECLARE 
            @ErrorMessage    NVARCHAR(4000),
            @ErrorNumber     INT,
            @ErrorSeverity   INT,
            @ErrorState      INT,
            @ErrorLine       INT,
            @ErrorProcedure  NVARCHAR(200);
            
            SELECT 
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorLine = ERROR_LINE(),
            @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
            
            RAISERROR 
            (
            @ErrorMessage, 
            @ErrorSeverity, 
            1,               
            @ErrorNumber,    -- parameter: original error number.
            @ErrorSeverity,  -- parameter: original error severity.
            @ErrorState,     -- parameter: original error state.
            @ErrorProcedure, -- parameter: original error procedure name.
            @ErrorLine       -- parameter: original error line number.
            );
        End Catch
    
    
    END
    

    Still no exceptions thrown still SP does not work when called through EF but does when called through SSMS.

    So in desperation I went into all of those Stored procedures and entered a lin

    Select 'Delete Completed'

    I updated the model from the database, and I changed all of those stored procedures to return a collection of Scalar Values String

    I change the action to look like this:

            [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Delete(RequirementSummaryViewModel ReqDelete)
            {
                using (var db = new VFS_ProcurementTrackEntities(Session["csProcTrack"].ToString()))
                {
                    try
                    {
                        using (TransactionScope transactionScope = new TransactionScope())
                        {
                            int userRecID = (int)Session["UserRecID"];
                            String checkWoDelete = db.CheckReqExistsInWOClin(ReqDelete.Requirement.ReqID).FirstOrDefault();
                            String CheckCtrDelete = db.CheckReqExistsInCtrSubClin(ReqDelete.Requirement.ReqID).FirstOrDefault();
                            if(CheckCtrDelete == "Pass")
                            {
                                if (checkWoDelete == "Pass")
                                {
                                    var errorMessage = db.PRSubLinesRelatedPartNumbersOneRequirement_Delete(ReqDelete.Requirement.ReqID, userRecID).FirstOrDefault();
                                
                                    //db.PRSubLinesRelatedPartNumbersOneRequirement_Delete(ReqDelete.Requirement.ReqID, userRecID);
    
                                    var msgTwo = db.PRSubLinesOneRequirement_Delete(ReqDelete.Requirement.ReqID, userRecID).FirstOrDefault();
    
                                    //db.FundDocLineByReqID_Delete(ReqDelete.Requirement.ReqID, userRecID);
    
                                    var msgThree = db.Requirement_Delete(ReqDelete.Requirement.ReqID, userRecID).FirstOrDefault();
                                }
                                else
                                {
                                    throw new Exception("Unable to Delete Requirement.  It is being used by a Work Order.");
                                }
                            }
                            else
                            {
                                throw new Exception("Unable to Delete Requirement.  It is being used by a Contract.");
                            }
                        }
                        return RedirectToAction("Index");
                    }
                    catch (Exception ex)
                    {
                        String errorReport = ex.Message;
                        if (ex.InnerException != null)
                        {
                            errorReport = "  " + ex.InnerException.Message;
                        }
                        TempData["ER"] = errorReport;
                        return RedirectToAction("ErrorReport", "Home");
                    }
                }
            }
    
    

    I again stepped through the code, this time examining the output.  Sure enough in every case the Select that occurs after the Delete statement was saved to the variable.  But the Delete statement did not succeed.  Again I tried running the SPs in SSMS, and again the delete succeeded. I have no idea what is going on here. This is the only imported stored procedure that behaves like this.

     Can someone help.

     

    Thursday, April 2, 2015 10:50 PM

Answers

  • User-1842880510 posted

    I am sure anyone who read the action carefully discovered my error.  Note this line

    joeller

    using (TransactionScope transactionScope = new TransactionScope()) {

    I put the SP executes inside this transaction, but failed to do whatever C# does to commit the transaction.  Given the short time to delivery, rather than go through the learning process to find out how to properly commit the transaction, (I did a couple of lookups and it did not seem simple), I commented out the transaction and everything worked as designed.

    So it is not EF's fault, it is my coding error.  I should just delete this but someone else might have the same issue from the same kind of coding error I had.  So I will let this stand for at least a time.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2015 12:10 AM