Execute a Query Using a Stored Procedure

[Hans]

Hi !

In Telerik ORm documentation under the  Execute a Query Using a Stored Procedure module you have this example

using ( NorthwindDbContext dbContext = new NorthwindDbContext() ) {    object[] custOrdersOrders = dbContext.CustOrdersOrders( "ALFKI" ) as object[]; }

if my stored procedure return 2 fields of type string and I have a class TwoFieldClass with 2 propertied Field1 and Field2 both of them of type string how I can convert the result of the stored procedure to a list of TwoFieldsClass .

Thank you !
Hans

[N Mackay]

Hans,

Good question.

The documentation shows you how to exec a stored proc but very little else, no samples on binding or using it's returned value or rows.

I tried to bind a stored proc to a WPF radgrid just for testing but I couldn't get it to work.

_radgrid_output.ItemsSource = _DBContext.MyStoredProc("MyValue").ToList

I submitted a ticket, I'll post the solution here if there is one, can't imagine it's too hard but the documentation is a bit light on this subject.

[Petko_I]

Hello Hans and Norman,

There is a way to retrieve a strongly typed result set of persistent objects which has not been exposed yet by the domain model approach. However, this can be easily rectified when we know that the OpenAccessContext internally uses an IObjectScope to handle the persistence. The scope API offers an alternative to invoking stored procedures. We can extend the definition of the OpenAccessContext  derived type and create a method that returns a collection of persistent types:

public partial class EntitiesModel : OpenAccessContext
    {
        public IEnumerable<Category> GetCategoriesProcedure()
        {
            IObjectScope scope = this.GetScope();
            Query<Category> query = scope.GetSqlQuery<Category>("GetCategories", null);
            QueryResultEnumerable<Category> result = query.ExecuteEnumerable(new object[]{});

           return result;
        }
    }

Here we assume that we have a stored procedure in the database defined in the following manner:

CREATE PROCEDURE GetCategories
AS
SELECT * FROM Categories;

If you would like to specify stored procedure parameters, you can have a look at this help article.
We would also like to give a clarification with regard to the meaning of the object array return type for the execution of the stored procedures. Each object in the array is in turn an object array where the items in the nested array correspond to the values of the selected columns in the database. The ordering of the object subitems is the same as the column order in the procedure definition in the database. You can take advantage of this fact to create helper classes which encapsulate a custom result set not corresponding to a persistent type. Such a custom result set would be returning, for example, only the names of the categories when the database table also contains a picture, id and description columns. In this case the strongly typed API for obtaining stored procedure results will not work and the object array approach is the way to go. The workaround would be to traverse the array and create an instance of the helper class for each element in this array.

We admit that the documentation about the usage of the stored procedures needs improvements and we will take action to address the issues in that direction. Should you have further questions, do not hesitate to contact us.


Best wishes,
Petko_I
the Telerik team

[N Mackay]

Hi Petar,

I replied to your ticket but I'll stick the same questions here as others will havethe same questiosn I'd imagine.

**

If I'm reading that right it's a big step back from even classic ADO.

If the stored procedure was based on a single table we'd used a select on the persistent object or more likely a view if there was any filtering required.

A stored procedure would be used generally to return some complex statistical information in our case and as such will not be a strong type that can be used.

If I'm reading you right I'd have to create a helper class for every stored procedure results that differ and do not match a persistent class in the model.

Worse still you have to manually traverse the rows and create an instance of the helper class, it just seems really clunky.

Can you not just traverse the returns rows without having to create a helper class ?

Could you provide a sample of a stored procedure that doesn't return a select on a single table (not a persistent class....that rarely happens) and how you would you assign the results to a grid, list etc

This could be a real show stopper as we have existing stored procedures we need to bring back data from, if we have to create a helper class for every single one it'll be time consuming and pretty inefficient.

[Petko_I]

Hello Norman,

We admit that currently the stored procedure support can benefit from further customization and we would want to assure you that your feedback will not pass unnoticed. Currently we return object arrays and although it may seem inconvenient at first sight, there is an easy solution to binding the results to a RadGridView in WPF, for example. The thing that needs to be configured is an appropriate binding for the columns in a grid. We are attaching simple examples so that interested customers can get an idea of how the OpenAccess result set from a stored procedure is displayed. We agree that usually a stored procedure is used for obtaining customized result sets that involve multiple tables. Because of this fact the returned result set often does not consist of persistent objects. Therefore, the column binding trick in the majority of scenarios is sufficient. 

We insist on saying that the future versions of OpenAccess will pay more attention on the stored procedures handling as long as there are customers such as you that need better support. Any further suggestions and questions are highly welcome.

Greetings,
Petko_I
the Telerik team

[rick]

I'm very interested in the ability to use stored procedure in the Client side programming,  I'm involved in a project that uses both KENDO and Telerik,   I've been assigned to use the KENDO object, and coming from a .NET server side world, it's quite a culture shock, to say the least.

That said what is the current state in the ability to access Stored Procedures, as a data-source.    The Binding issue should be an easy issue, if we were to use a single #Tmp table as the output, or a well labeled Joined SQL statement...

again i'm very interested in where the use of Stored Procedures are with Telerik.

[Doroteya]

Hello Rick,

Thank you for your interest in Telerik OpenAccess ORM.

Currently, the ability to execute stored procedures and functions through OpenAccess ORM is improved for both domain models (created and maintained through Visual Designer) and fluent models (utilizing the Code - Only Mapping approach). At the time present it is possible to map a stored procedure or a function to a method of the context and to call it through it. The call itself utilizes our Low Level (ADO) API, and in order to assist you in generating the method (for domain models only) we introduced Domain Method Editor.

Basically, Domain Method Editor generates the domain method for you in the context class, and creates the appropriate result shape (if such is necessary). You can find more details, code samples, and fundamental workflows in this section of your documentation.

In case you decide to use a fluent model and need to create methods mapped to stored procedures / functions manually, the relevant information is available in this section.

Regarding the consumption of the generated methods with Kendo UI as user interface layer, please kindly find attached to this message a sample project that uses OpenAccess ORM in the data access layer, and a Web API service generated with our Add OpenAccess Service wizard that exposes the stored procedure. The sample utilizes our SofiaCarRental database and requires the following steps in order to run it:
1. Unrar the attached file.
2. Execute the script for the SofiaCarRental database on your local server instance.
3. Apply the necessary settings for the connection string in the web.config file.
4. Upgrade the OpenAccess references as described in this documentation article.
5. Build the solution and run the sample.

I hope you find this feasible. If you have additional questions, or experience difficulties, do not hesitate to get back to us.



Regards,
Doroteya
Telerik