This is a migrated thread and some comments may be shown as answers.

Sorting with OpenAccessDataSource

22 Answers 445 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

Brandon asked on 17 Dec 2008, 09:48 PM

I am using the latest version of Telerik Q3.2008 with Visual Studio 2008. I am unable to get the OpenAccessDataSource object to sort its results. I have created the following Region object:

    
                [Telerik.OpenAccess.Persistent(IdentityField = "id")] 
        
                public class Region 
        
                { 
        
                    private int id; 
        
                    [Telerik.OpenAccess.FieldAlias("id")] 
        
                    public int Id 
        
                    { 
        
                        get { return id; } 
        
                        set { id = value; } 
        
                    } 
        
                    private string regionName; 
        
                    [Telerik.OpenAccess.FieldAlias("regionName")] 
        
                    public string RegionName 
        
                    { 
        
                        get { return regionName; } 
        
                        set { regionName = value; } 
        
                    } 
        
                    private DateTime? lastModified; 
        
                    [Telerik.OpenAccess.FieldAlias("lastModified")] 
        
                    public DateTime? LastModified 
        
                    { 
        
                        get { return lastModified; } 
        
                        set { lastModified = value; } 
        
                    } 
        
                }

On my WebForm I have dropped an OpenAccessDataSource object. I have set the DataSource's OrderExpression property to 'RegionName.' Finally, I have tied my DataSource to a RadGrid. However, the results are not sorted by RegionName. What must I do to sort these properly?

22 Answers, 1 is accepted

answered on 09 Apr 2009, 07:24 PM

Well, I have already posted in this thread with the details and Telerik's first response was:

----------------------------------------
Hello Mitch,

The problem you reported was evaluated by our team. The bug will be fixed for our next service pack. Your telerik points were updated.

If you have any other issues/suggestions please feel free to contact us again.

Kind regards,
Zoran
the Telerik team
-----------------------------------------

Then, the next response about it from Telerik was:

-----------------------------------------
Hello Web Belief Ltd,
Yes this is a verified problem with our latest release and we are working on solving the problem.

Regards,
Dimitar Kapitanov
the Telerik team
-----------------------------------------

And then...

-----------------------------------------
Hi Web Belief Ltd,
We will release a service pack release latest two weeks from now. It will contain all required functionality for the datasource. The problem was recently discovered and is caused by internals of the datasource that handle the parameters required for sorting and filtering functionality.

Regards,
Dimitar Kapitanov
the Telerik team
-----------------------------------------

I downloaded the latest release of OpenAccess hoping it would fix other bugs, but it did not. I read through the change documentation for the release (2009.1.405.1) and noticed it mentioned "OpenAccessDataSource: Filtering and Ordering work as expected". I'm curious since the verbage says "...Ordering...", is the OADS writing SQL Queries with ORDER BY clauses or is it implementing sorting through object functions? I have not tested the sorting as of yet for the new release.

answered on 10 Apr 2009, 10:45 AM

Hello SuperXRAY,
When saying 'ordering' we are referring to the OrderBy property of the data source. It is translated to ORDERBY clause in the OQL and then it is executed against the db server. It means that the ordering takes place on the server. The internals are that the sorting expression from the grid and the ordering clause are merged together providing an unified 'sorting' OQL representation. The sorting expression is put at the start of the string so it should take precedence when ordering/sorting (basically the same, but it comes from different places). We have a range of tests exactly that incorporate RadGrid with a data source that is sorting and all of them pass so far. Please if you discovered additional problems share them with us so that we could improve the quality of the data source components.
Hope my answer sheds some light on the topics you are refering.

Greetings,
Dimitar Kapitanov
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

answered on 03 Jun 2009, 09:24 AM

Hello Andrew,
we worked with the latest official builds available for download of both OpenAccess and Rad Controls and we were not able to reproduce this behavior. Both filtering and sorting worked fine for us.

    
            </telerik:RadGrid><telerik:OpenAccessDataSource ID="OpenAccessDataSource1"  
        
                        runat="server" ContextTypeName="" EnableDelete="True" EnableInsert="True"  
        
                        EnableUpdate="True"  
        
                        ObjectContextProvider="ClassLibrary33.ObjectScopeProvider1, ClassLibrary33"  
        
                        OrderBy="@Ord1 DESC,@Ord2 ASC" TypeName="ClassLibrary33.Order" Where=""> 
        
                        <OrderByParameters> 
        
                        <asp:Parameter Name="Ord1" DefaultValue="Freight" /> 
        
                        <asp:Parameter Name="Ord2" DefaultValue="Freight" /> 
        
                        </OrderByParameters> 
        
                    </telerik:OpenAccessDataSource>

Defining the data source like this resulted in perfectly proper behavior. Even when we tried to sort on the field Freight everything worked perfectly fine. Filtering worked as expected as well.
Can you please confirm that you are using the latest available builds.

Greetings,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

answered on 09 Jun 2009, 07:36 AM

Hello Andrew,
you should be able to do that freely. We have tested it several times and we could not reproduce the false behavior.
Typically the steps are as fallows:
You create a class library and reverse map your classes.
Refer that class library in your web application.
Define the datasource as the example I gave in my previous post.
Specify the grid data source property to be your newly created data source.
Enable all properties that you find necessary on the grid.
Run your application.
The above steps should be enough to produce a working example. If you are still facing difficulties can you please open a support thread where our team can attach a working project for you.

Kind regards,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

answered on 09 Jun 2009, 10:32 PM

Hi Petar,

Thanks for response, you are quite right, if the OADataSource is decalred using the TypeName property, then the filtering works. But I am using the CommandText property, and that seems to prevent filtering (or ordering) from the RadGrid. Sorry for not saying this earlier.

I wanted to use the CommandText property in order to display composite data from the OA classes.

Using the TypeName to refer to a class permits access to the properties of the class, and of classes which are (Parents) of the class (Parents in terms of the hierarchy), but I also want to get to summary and selected properties of child classes.

answered on 09 Jun 2009, 10:48 PM

Further to this, I have reverted to using a TypeName, my Class structure comprises an "Account" which has a parent class of "Product", and Product has a property called "FixedYears (Fixed_Years in the database)", when I put a column on my gird to display the Product.FixedYears, I can filter by it, but when I sort I get an error with the pertinent part of the StackTrace as follows:

    
            SELECT  TOP(??T) a.[Fndr_ID] AS COL1, a.[Loan_ID] AS COL2, a.[Completion_Date] AS COL3, a.[Locked_to_Swap] AS COL4, a.[Principal_Balance] AS COL5, a.[Product_ID] AS COL6, a.[Fndr_ID] AS COL7, a.[Pool_ID] AS COL8, a.[Swap_List_ID] AS COL9 FROM [Account] a JOIN [Product] AS b ON (a.[Product_ID] = b.[ID]) WHERE b.[Fixed_Years] = 2 ORDER BY  FixedYears   Telerik.OpenAccess.RT.sql.SQLException: Invalid column name 'FixedYears'.

Note that the Order By attempts to refer to a column called "FixedYears" which does not exist (the Product Class Property is FixedYears, but this maps to Fixed_Years in the database).

I think I must point out that when I say filter and sort, I mean that I have enabled these options on the RadGrid and am exercising them from the grid (i.e. what a user would do).

And Yes this happens for any property of a parent class of the TypeName class.

Arrg - too quick to post, forget this problem - the Grid's SortExpression has to include the Parent Class (Product.FixedYears)..

answered on 16 Jun 2009, 07:56 AM

Hi Andrew,
we are very happy that you have managed to figure it out. And now let me give you some insight on the CommandText problem. The issue comes from that the CommandText lifts the limitation of display a certain table in the grid. Giving this the grid cant "know" what will be returned from the data source using the CommandText. You can at first return customers than orders and so on. Because of that the grid columns cant be bound to a certain result thus enabling sorting. You can however set the data source like this:

    
            telerik:OpenAccessDataSource ID="OpenAccessDataSource1" runat="server"  
        
                        ContextTypeName="" EnableDelete="False" EnableInsert="False" EnableUpdate="False"  
        
                        ObjectContextProvider="ClassLibrary3.ObjectScopeProvider1, ClassLibrary3"  
        
                        OrderBy="@param asc" TypeName="" Where="" CommandText="Select * From CustomerExtent"> 
        
                        <OrderByParameters> 
        
                        <asp:Parameter Name="param" DbType="String" DefaultValue="City" /> 
        
                        </OrderByParameters> 
        
                    </telerik:OpenAccessDataSource>

This way you will be able to pass the sorting parameter from a server control or query parameter( You have several options here, just review all the available parameters in the orderby tag).
Hope that my response will help you.

All the best,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Sorting with OpenAccessDataSource

22 Answers, 1 is accepted