DbNull always coming back as a return value from SQL Server stored procedure in C# Spring.NET

Question

I have a simple stored procedure that only returns the number 100 right now. Executing it from C# gives me DBNull when I check the returnvalue parameter when debugging. I know my stored procedure to just return 100 is nonsensical but my real logic was experiencing the same error, so I changed it to be the simplest version of what I'm trying to do to figure out how to get a decimal in C# from a return value of my stored procedure.

The specific exception I'm getting (location of exception noted below) is System.InvalidCastException: Object cannot be cast from DBNull to other types.

I have only changed names in my code below.

I have the following C# code

public class MyDataContext : AdoDaoSupport, IMyDataContext
{
    //Injected from web.config via spring.NET 
    // sp_GetOneHundred
    public string GetOneHundredProc { get; set; }

    public void Process(string name)
    {
        try
        {
            //Call Stored Procedure
            CallGetOneHundredStoredProcedure(name);
        }
        catch 
        {
            throw;
        }
   } 
   private bool CallGetOneHundredStoredProcedure(string name)
   {
        IDbParameters parameters = CreateDbParameters();
        parameters.Add("Name", DbType.String, 254).Value = name;
        var returnValue = parameters.Add("return_value", DbType.Int32);
        returnValue.Direction = ParameterDirection.ReturnValue;

        try
        {
           var  x = AdoTemplate.ExecuteNonQuery(
                   CommandType.StoredProcedure,
                   GetOneHundredProc,
                   parameters);


           return Convert.ToDecimal(returnValue.Value) == 1;
        }
        catch (Exception e)
        {
            //Exception thrown here
            throw;
        }

    }
}

And the following stored procedure

ALTER PROCEDURE [dbo].[sp_GetOneHundred]
    @Name varchar(254)      
AS
BEGIN
   SET NOCOUNT ON;


   DECLARE @oneHundred AS Int;
   SET @oneHundred=100;
   RETURN @oneHundred;
END;

Edit: for fun I tried to do this and got a different exception:

 return Convert.ToDecimal(parameters["return_value"].Value) == 1;

The new exception I receive is

 System.IndexOutOfRangeException: An SqlParameter with ParameterName 'return_value' is not contained by this SqlParameterCollection.

Maybe that could help someone figure out what is happening

Answer 1

In case anyone ever finds this same problem, I found the answer. All I had to do was include the @ sign before my parameter name when checking the value

return Convert.ToDecimal(parameters["@return_value"].Value) == 1;

Note that I did not modify the line defining the parameter, there is no @ sign there.

var returnValue = parameters.Add("return_value", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;