Oracle Stored Procedures and Entity Framework Methods

[mservidio]

Hi -

I'm using Entity Developer for my application, and I've mapped all the tables, and everything works great so far. Today, I wanted to create a method to execute an Oracle Store Procedure that has 4 in parameters and 1 out parameter. I haven't had any luck with getting the method to work. Since this is Oracle sp, I don't believe that the return type would be scaler as this isn't a function returning a scaler value. How do I go about configuring EF through Entity Developer to call an oracle store procedure with an number out parameter?

thanks,
Mark

[AndreyR]

I have just added a stored procedure having four input and one output parameter to Devart Entity model and had successfully called the procedure using the latest 5.70.190 release of dotConnect for Oracle.

[mservidio]

Here's the error message I get:

The data reader is incompatible with the specified 'DataService.Metadata.CREATEVERSIONResult'. A member of the type, 'aNEWVERSIONID', does not have a corresponding column in the data reader with the same name.

'aNEWVERSIONID' is the name of my output parameter in the oracle procedure:

Code: Select all

PROCEDURE CREATEVERSION (
    aORIGINALVERSIONID NUMBER,
    aUSERID            NUMBER,
    aUSERCOMMENT VARCHAR2,
    aVERSIONTYPE VARCHAR2,
    aNEWVERSIONID OUT NUMBER);

Here is the result set class:

Code: Select all

public partial class CREATEVERSIONResult
    {
        #region Primitive Properties
    
        public Nullable aNEWVERSIONID
        {
            get;
            set;
        }

        #endregion
    }

Here is the generated function:

Code: Select all

public ObjectResult CREATEVERSION(Nullable aORIGINALVERSIONID, Nullable aUSERID, string aUSERCOMMENT, string aVERSIONTYPE, ObjectParameter aNEWVERSIONID)
        {
    
            ObjectParameter aORIGINALVERSIONIDParameter;
    
            if (aORIGINALVERSIONID.HasValue)
            {
                aORIGINALVERSIONIDParameter = new ObjectParameter("AORIGINALVERSIONID", aORIGINALVERSIONID);
            }
            else
            {
                aORIGINALVERSIONIDParameter = new ObjectParameter("AORIGINALVERSIONID", typeof(decimal));
            }
    
            ObjectParameter aUSERIDParameter;
    
            if (aUSERID.HasValue)
            {
                aUSERIDParameter = new ObjectParameter("AUSERID", aUSERID);
            }
            else
            {
                aUSERIDParameter = new ObjectParameter("AUSERID", typeof(decimal));
            }
    
            ObjectParameter aUSERCOMMENTParameter;
    
            if (aUSERCOMMENT != null)
            {
                aUSERCOMMENTParameter = new ObjectParameter("AUSERCOMMENT", aUSERCOMMENT);
            }
            else
            {
                aUSERCOMMENTParameter = new ObjectParameter("AUSERCOMMENT", typeof(string));
            }
    
            ObjectParameter aVERSIONTYPEParameter;
    
            if (aVERSIONTYPE != null)
            {
                aVERSIONTYPEParameter = new ObjectParameter("AVERSIONTYPE", aVERSIONTYPE);
            }
            else
            {
                aVERSIONTYPEParameter = new ObjectParameter("AVERSIONTYPE", typeof(string));
            }
            return base.ExecuteFunction("CREATEVERSION", aORIGINALVERSIONIDParameter, aUSERIDParameter, aUSERCOMMENTParameter, aVERSIONTYPEParameter, aNEWVERSIONID);
        }

[mservidio]

I haven't defined any output types as this is a stored procedure and has output parameters, which I assume should be defined in the custom class as shown above. Could you show me example of the one you setup?

[AndreyR]

Are you able to reproduce the problem using the latest 5.70.190 release build?
Please try to regenerate the model using the latest version.
Notify me if anything goes wrong.

[mservidio]

I'm using latest version of DevArt.Data.Oracle (5.70.190) already. Can you send me your test project, so that I can try and mimic it?

Still getting this error:

The data reader is incompatible with the specified 'DataService.Metadata.CREATEVERSIONResult'. A member of the type, 'ANEWVERSIONID', does not have a corresponding column in the data reader with the same name.

Here is the class for CREATEVERSIONResult:

Code: Select all

public partial class CREATEVERSIONResult
    {
        #region Primitive Properties
    
        public Nullable ANEWVERSIONID
        {
            get;
            set;
        }

        #endregion
    }

Here is the method from which I try and execute the sp call:

Code: Select all

public virtual void CreateVersion(int versionId, String versionType, int userId, String userComment)
        {
            using (ModelEntities context = new ModelEntities())
            {
                ObjectParameter newVersion = new ObjectParameter("ANEWVERSIONID", typeof(Decimal));
                context.CREATEVERSION(versionId, userId, userComment, versionType.Trim().ToUpper(), newVersion);
                int newVersionId = Convert.ToInt32(newVersion.Value);
            }
        }

Thanks,
Mark

[AndreyR]

I have sent the project to the e-mail provided in your user profile.
Please let me know if anything goes wrong.

[mservidio]

Thanks for the example. One difference between my procedure call and yours was that I had a return type, so I adjusted mine to have similar settings. The other thing I just realized, is that when I turn the return type to none, my tt4 poco entity generator doesn't create a method for the method that I created in the entity framework model. tt4 picked up when it had a return type and created a function but it doesn't seem to create a method for me properly. Do you know if tt4 poco generator supports methods?

[mservidio]

I've figured out my issue. I'm using tt4 template with poco objects, so the context class is built using template. The default Poco tt4 template v1.1 from microsoft doesn't support sp's with no return type. It doesn't include them in generation. Here is the fix at these links:

http://pastebin.com/sLbECkBg

http://visualstudiogallery.msdn.microso ... 3d02752313

[AndreyR]

Glad to hear the issue is resolved.
We plan to add POCO templates to Devart Entity models, it's in our roadmap.
You can influence our roadmap at Devart User Voice.