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

[Dj Prpa]

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

[PetarP]

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

[Dj Prpa]

Hi Petar,

That is exactly what I was looking for.

Thank you.
Zoran

[PetarP]

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

[Daniel Plomp]

Is this already part of ORM?

Regards,
Daniel

[PetarP]

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

[Brian]

Very important to retrieve data via stored procs.

[PetarP]

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

[Dj Prpa]

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?

[PetarP]

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

[Shawn Krivjansky]

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.

[PetarP]

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

[Shawn Krivjansky]

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.

[PetarP]

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

[Shawn Krivjansky]

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.

[PetarP]

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