Access stored procedures' return value(s) using OA context

16 posts, 0 answers
  1. Dj Prpa
    Dj Prpa avatar
    20 posts
    Member since:
    Aug 2012

    Posted 13 Dec 2010 Link to this post

    Hi,

    Here is the method in OA context that is a wrapper for a SQL2008 stored procedure.

    [Function("usp_GetNextID")]
    public object[] UspGetNextID([Parameter("nextID", Mode = ParameterMode.InOut, IsNullable = true, OpenAccessType = OpenAccessType.Int64, SqlType = "bigint")]long? nextID)
    {
        SqlParameter parameterNextID = new SqlParameter("nextID", OpenAccessType.Int64, ParameterMode.InOut);

        List<SqlParameter> sqlParameters = new List<SqlParameter>()
        {
            parameterNextID
        };
       
        object[] queryResult = this.ExecuteStoredProcedure<object>("'usp_GetNextID' ?", sqlParameters ,nextID);  
        return queryResult;
    }

    How can I execute this method in my code and access the returned value?
    Thanks,
    Zoran
  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 15 Dec 2010 Link to this post

    Hi Dj Prpa,

     Unfortunately return parameters are not yet supported by Telerik OpenAccess ORM and therefore cannot be accessed. Currently we support only out parameters. All the out parameters are stored in a dictionary collection within the query result and their values can be accessed via their names. We plan to introduce similar support for the return parameters where they will be stored in a collection called ReturnParameters and will be accessible via their names. So for example if you have a return parameter called "sampleParam" you will be able to get its value by writing:

    queryResult.ReturnParameters["sampleParam"];
    Please do let me know if that would be the expected approach in your perspective.


    Greetings,
    Petar
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
  4. Dj Prpa
    Dj Prpa avatar
    20 posts
    Member since:
    Aug 2012

    Posted 20 Dec 2010 Link to this post

    Hi Petar,

    That is exactly what I was looking for.

    Thank you.
    Zoran
  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 20 Dec 2010 Link to this post

    Hi Dj Prpa,

     Great. This functionality should make it in one of our next service packs. Do check our release notes to see when exactly the feature will be available.

    Greetings,
    Petar
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  6. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 21 Jan 2011 Link to this post

    Is this already part of ORM?

    Regards,
    Daniel
  7. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 21 Jan 2011 Link to this post

    Hi Dj Prpa,

     No, unfortunately it is still not. We still have it in our plans however it was not with one of the highest priorities and therefore we have not implemented it yet.
    I do hope, however, that we will be able to implement it for our next service pack. How important is that feature for your projects? 
    We look forward for your reply.

    All the best,
    Petar
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  8. Brian
    Brian avatar
    28 posts
    Member since:
    Dec 2008

    Posted 06 Feb 2011 Link to this post

    Very important to retrieve data via stored procs.
  9. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 07 Feb 2011 Link to this post

    Hi Brian,

     We are able to retrieve data via stored procedures. What we do not support currently is using return parameters via a stored procedure. While this is indeed a missing functionality it can easily be work arounded by switching the return parameters to out ones (Telerik OpenAccess ORM fully supports out parameters.
    Please have in mind that we plan to have full support for return parameters for Q1 2011.

    Kind regards,
    Petar
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  10. Dj Prpa
    Dj Prpa avatar
    20 posts
    Member since:
    Aug 2012

    Posted 07 Feb 2011 Link to this post

    Could you provide us with the example of accessing stored procedures' out parameter using OA context? I have a stored procedure that has one out parameter (Int64).
    Do I use ExecuteStoredProcedure method or something else?
  11. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 10 Feb 2011 Link to this post

    Hi Dj Prpa,

     Currently we are missing the code generation part that is responsible for generating the required code for accessing the out parameters. In order to use them you will have to slightly modify your generated code. You will need to add a dictionary that will be filled with a map between the out parameter name and its value. You will also have to add code that assigns those values to the original parameters passed to the method. Here is an example:

    public object[] TenMostExpensiveProducts(ref int? sampleOut)
            {
                SqlParameter parameterSampleOut = new SqlParameter("sampleOut", OpenAccessType.Int32, ParameterMode.Out);
             
                List<SqlParameter> sqlParameters = new List<SqlParameter>()
                {
                    parameterSampleOut
                };
                IDictionary<string, object> outParams = new Dictionary<string, object>();
                object[] queryResult = this.ExecuteStoredProcedure<object>("'Ten Most Expensive Products' ?", sqlParameters,out outParams ,sampleOut);
                sampleOut = (int)outParams["sampleOut"];
                return queryResult;
            }


    Regards,
    Petar
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  12. Shawn Krivjansky
    Shawn Krivjansky avatar
    86 posts
    Member since:
    Jan 2010

    Posted 28 Apr 2011 Link to this post

    Just want to put in my vote to get this code generation piece fixed.

    The amount of overhead it takes per SP to use output parameters with all the manual changes that it requires makes them almost unuseable.  Not to mention that your code disappears and you have to redo everything if something changes in the SP that would cause a re-gen.  Try managing that with more than 1 SP with output parameters....what if you had a 20, 50, or a 100 of them?

    I'm not even sure the current "solution" would be called a workaround.  I would say it is more like a technique of absolute last resort when you are completely out of other options and you don't mind the thing suddenly not working because you forgot to go back and change your functions after a re-gen.

    Just saying.... this makes your data layer like a house of cards.
  13. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 03 May 2011 Link to this post

    Hello Shawn Krivjansky,

    I understand your concern and we will rise the priority of implementing this functionality. I believe that you will be able to find this feature implemented with one of our next service packs.
    I am sorry for the inconvenience caused.

    Regards,
    Petar
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  14. Shawn Krivjansky
    Shawn Krivjansky avatar
    86 posts
    Member since:
    Jan 2010

    Posted 30 Aug 2011 Link to this post

    Haven't heard a peep on this issue, and didn't really see it mentioned in any "Service Pack" or any of the full releases.

    Without me completely breaking all my context Stored Procedure code by doing a re-gen to test (because I have the custom code in there to handle the output parameters...and a re-gen will blow that code away if Telerik hasn't "fixed" the code-gen part), can somebody confirm that this hasn't been implemented yet??

    We are quite a few releases and service packs past the December 2010 stated "next service packs".  Just wondering how much longer I have to manage this cluster of 20 SPs with output parameters like this?  Anytime anything with the interface changes with the SP (that requires me to "update from datebase", I have to literally re-code the "code-gen" pieces by hand.  As dumb as it sounds, I am almost 100% better off NOT using the "update from database" and just coding the changes in the generated code myself.  It is far less effort.

    If that doesn't make this issue a HIGH priority, I don't know what does.
    Although the ORM analysis tools are "cool" and would be fun to play with if I had time, unfortunetely I spend most of my time re-writing the SP code-gen part for these silly things and can't get time to use it.  So, how the ORM analysis tools were made a higher priority than something like this I'll never know.  All I know is we aren't talking about brain surgery here.  All we are talking about is asking the code gen part to stick a few things into the generated function that is required for the output parameters.  I just can't believe something as useful (fixing something that is broken) as this which would require so little time can't find it's way to being done.


  15. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 03 Sep 2011 Link to this post

    Hello Shawn Krivjansky,

     We are going to improve the process of working with stored procedures greatly. However I do understand your frustration and I can see a possible solution for you. Since we are depending on T4 code generation to generate our code we can produce an improved template for you with which you will need to replace your original one. This way you will be able to automatically generate the code you are doing on hand. 
    Please do let me know if that will work for you and we will prepare the template.

    Best wishes,
    Petar
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  16. Shawn Krivjansky
    Shawn Krivjansky avatar
    86 posts
    Member since:
    Jan 2010

    Posted 03 Sep 2011 Link to this post

    I'm open to anything at this point.

    Please provide me the template and exactly how to implement it (as I've never used a custom T4 template with Telerik before).

    Thank You.
  17. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 07 Sep 2011 Link to this post

    Hi Shawn Krivjansky,

     The implementation will be limited to copying and pasting them in the correct place. I will post here as soon as the template is ready.

    Kind regards,
    Petar
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

Back to Top
DevCraft banner