Invalid cast exception from generated domain method with char output parameter

8 posts, 0 answers
  1. Craig
    Craig avatar
    112 posts
    Member since:
    Nov 2011

    Posted 12 Nov 2013 Link to this post

    Hi

    I'm getting an Invalid Cast Exception from the generated domain method when it is processing the output parameter of my stored procedure in SQL Server 2008 R2. The domain method is generated from a stored procedure with a void result shape. Here's the stored procedure header:

    Create Procedure mySp
    ( @parm1 smallint
    , @parm2 varchar(20)
    , @parm3 char(1)
    , @parm4 char(1) OUTPUT
      )
    begin
    ...


    @parm4 returns either 'Y' or 'N'.



    Here's the call to the stored procedure in the generated method:

       ...
       int queryResult = this.ExecuteNonQuery("[mySp]", CommandType.StoredProcedure, parameterParm1, parameterParm2, parameterParm3, parameterParm4, parameterReturnValue);
             
       returnValue = parameterReturnValue.Value == DBNull.Value
                ? -1
                : (int)parameterReturnValue.Value;
            parm4 = parameterParm4.Value == DBNull.Value
                    ? default(System.Nullable<System.Char>)
                    : (System.Nullable<System.Char>)parameterParm4.Value;
             
       return queryResult;
    }


    The stored procedure executes ok. Return value is 0 and parameterParm4.value = 'N'.

    The error System.InvalidCastException was unhandled by user code Message=Specified cast is not valid is raised when the statement

    (System.Nullable<System.Char>)parameterPar4.Value; is executed.



    In the immediate window in VS IDE I executed the following (with results)

    ?(System.Nullable<System.Char>)parameterParm4.Value;
    Cannot unbox 'parameterParm4.Value' as a 'char?'
    ?parameterParm4.Value;
    "N"




    Perhaps there is a better way I can do the same to avoid this issue?

    OpenAccessORM is version 2013.3.1014.1.

    Craig



  2. Ady
    Admin
    Ady avatar
    588 posts

    Posted 15 Nov 2013 Link to this post

    Hello Craig,

     Based on some research, it seems that the value returned by the stored procedure seems to be something other than what are are trying to cast.
    Are you sure the exception is thrown at the '(System.Nullable<System.Char>)parameterPar4.Value' statement or is it during the 'parameterReturnValue.Value == DBNull.Value ? -1 : (int)parameterReturnValue.Value ' statement?

    Can you examine the type of the value that is being cast?  You can copy the generated code into a partial class (so that is not overwritten the next time you save the rlinq file) and modify the cast by first 'unboxing' to the type of the value and then to the desired type.

    Regards,
    Ady
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  3. DevCraft banner
  4. Craig
    Craig avatar
    112 posts
    Member since:
    Nov 2011

    Posted 15 Nov 2013 Link to this post

    Hi Ady,
    The code below reproduces the error. OAMethodSim is a stripped down version of the generated method and my call to it is in the two lines in Main. I don't think it should matter what value I assign to parm4 since it should be updated by OAMethodSim.
    Craig

    using System;
    using System.Data;
    using System.Linq;
    using Telerik.OpenAccess.Data.Common;
     
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                char? parm4 = char.Parse(" ");
                int result = OAMethodSim(ref parm4);
     
                Console.WriteLine("Press a key ...");
                Console.ReadKey();
            }
     
            static int OAMethodSim(ref System.Nullable<System.Char> validInd)
            {
                OAParameter parameterValidInd = new OAParameter();
                parameterValidInd.ParameterName = "Valid_Ind";
                parameterValidInd.Direction = ParameterDirection.InputOutput;
                if (validInd.HasValue)
                {
                    parameterValidInd.Value = validInd.Value;
                }
                else
                {
                    parameterValidInd.DbType = DbType.String;
                    parameterValidInd.Value = DBNull.Value;
                }
     
                //simulate sp call
                parameterValidInd.Value = "N";
     
                validInd = parameterValidInd.Value == DBNull.Value
                    ? default(System.Nullable<System.Char>)
                    : (System.Nullable<System.Char>)parameterValidInd.Value;
     
                return 0;
            }
         
        }
    }
  5. Ady
    Admin
    Ady avatar
    588 posts

    Posted 19 Nov 2013 Link to this post

    Hello Craig,

    Thank you for the sample code. That helped in verifying the problem.
    The root cause of the problem lies in the fact that although 'param4' is of type 'char(1)' and is assigned a character literal in the stored procedure, the value is obtained as a 'string' via the ADO.NET framework.

    Since we detect that the parameter length is 1, we generate the method with a parameter of CLR type 'char'. Casting a string to char fails.

    To work around this you can either change the parameter length in the stored procedure from 1 to 2. Delete the generated method via the designer (Model Object Explorer), Update the model from the database and then generate the method again. This time a 'string' parameter will be generated and it should work.
    Alternatively you can copy the generated method into a partial context class and modify the method parameter to a string or then convert the string to a char explicitly.

    Do get back in case you need further assistance.

    Regards,
    Ady
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  6. Craig
    Craig avatar
    112 posts
    Member since:
    Nov 2011

    Posted 19 Nov 2013 Link to this post

    Hi Ady,
    I will be unable to change the stored procedure because it is shared and in use in existing applications.

    "Since we detect that the parameter length is 1, we generate the method with a parameter of CLR type 'char'. Casting a string to char fails."

    Will this be fixed so that the generated OA Method correctly handles char(1) parameters or handles them as varchar (strings)? 


    We use char(1) indicators a lot. I would like to try the second option but I would not want to have to edit the method every time the RLINQ model is updated from the database or regenerated. Would that be the case?  If not, please can you provide an example?

     
    Thanks,
    Craig
  7. Ady
    Admin
    Ady avatar
    588 posts

    Posted 22 Nov 2013 Link to this post

    Hi Craig,

     Since the domain method generation code is generic for all backends (Oracle, MySql etc) and not specific for Sql Server any changes there would need careful consideration and deliberation. We would need to test it against various use cases. I will bring this up for discussion and let you know if and when a fix is scheduled.

    The workaround I suggested needs to be done only once.
    1. You should copy the generated domain method.
    2. Create a partial class for the EntitiesModel class (or whatever you context class is called). This will be a separate source file in the project with the same class name. This file is not overwritten by the designer on every save.
    3. Past the method code there.
    This way you will not lose the changes on saving the rlinq file.

     Do get back in case you need further assistance.

    Regards,
    Ady
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  8. Craig
    Craig avatar
    112 posts
    Member since:
    Nov 2011

    Posted 27 Nov 2013 Link to this post

    Hi Ady,
    Thanks for your help.

    I found it easier in the end to wrap the stored procedure with my own stored procedure that uses varchar(2) as the output parameter which was treated as a string. (Varchar(1) was considered the same as char(1) and also translated to char?).

    Regards,
    Craig
  9. Ady
    Admin
    Ady avatar
    588 posts

    Posted 02 Dec 2013 Link to this post

    Hi Craig,

     Glad that helped.

    Feel free to get back in case you need further assistance.

    Regards,
    Ady
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
Back to Top
DevCraft banner