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

OpenAccess ORM Problems with RadGrid Sorting and Filtering

4 Answers 201 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.

Nathan asked on 02 Jun 2010, 08:11 PM

I am having a heck of a time getting OpenAccess ORM adopted at my agency. I have been pushing toward testability for two years, and every time I take a step in the right direction, like with OA, I get slapped with stupid little things like sorting and filtering that are essential in our applications.

Scenario:
I have a solution with six different projects. The only ones that are of immediate consideration for this issue are the main web application, and the ORM project. In a blank page I added the following:

RadScriptManager
RadGrid
OpenAccessDataSource

I configured the datasource to look at a view in the database. I then connect the grid to use the OA datasource. The sample grid contains two columns: an ID field, and a date field. I need to be able to sort the data as it is bound to the grid so that it is not in an unusable state when the page loads. If I set a sort order in the OA datasource, the page loads with the sort order as expected. However, if you then try to sort the column differently, you get the following error:

A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Statement(s) could not be prepared.

Resorting works if I set the order in the SQL view, but this is not an acceptable solution. We have many pages that rely on a single view, and need to sort pages differently.

The next error is just as critical to us and is, I think, closely related. With a standard ADO.NET datasource I am able to use the between filter in the radgrid for date columns. However, once I added OA ORM, I encounter the following error when I try to use "1/1/10 2/1/10" (obviously without the " marks) as a between filter:

String was not recognized as a valid DateTime.

The database column is defined as a DateTime, and I am not using a GridDateTimeColumn.

Please shed some light on these issues so I may continue to use your products.

HTML

    
            <%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="Accidents_test" %> 
        
            <%@ Register Assembly="Telerik.OpenAccess.Web" Namespace="Telerik.OpenAccess" TagPrefix="telerik" %> 
        
            <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
        
            <html xmlns="http://www.w3.org/1999/xhtml">  
        
            <head runat="server">  
        
                <title></title>  
        
            </head> 
        
            <body> 
        
                <form id="form1" runat="server">  
        
                    <div> 
        
                        <telerik:RadGrid ID="RadGrid1" runat="server" AllowFilteringByColumn="True" AllowPaging="True"   
        
                                         AllowSorting="True" DataSourceID="OpenAccessDataSource1" GridLines="None">  
        
                            <MasterTableView AutoGenerateColumns="False" DataKeyNames="RailItemID"   
        
                                            DataSourceID="OpenAccessDataSource1">  
        
                                <RowIndicatorColumn HeaderStyle-Width="20px" /> 
        
                                <ExpandCollapseColumn HeaderStyle-Width="20px" /> 
        
                                <Columns> 
        
                                    <telerik:GridBoundColumn DataField="RailItemID" DataType="System.Int32"   
        
                                        HeaderText="RailItemID" ReadOnly="True" SortExpression="RailItemID"   
        
                                        UniqueName="RailItemID">  
        
                                    </telerik:GridBoundColumn> 
        
                                    <telerik:GridBoundColumn DataField="InstallationDate"   
        
                                        HeaderText="InstallationDate" SortExpression="InstallationDate"   
        
                                        UniqueName="InstallationDate">  
        
                                    </telerik:GridBoundColumn> 
        
                                </Columns> 
        
                            </MasterTableView> 
        
                        </telerik:RadGrid> 
        
                        <telerik:OpenAccessDataSource ID="OpenAccessDataSource1" runat="server"   
        
                                                      ObjectContextProvider="Rail.ORM.RailObjectProvider, Rail.ORM"   
        
                                                      TypeName="Rail.ORM.ViewCrossing" OrderBy="InstallationDate DESC" /> 
        
                    </div> 
        
                    <telerik:RadScriptManager ID="RadScriptManager1" Runat="server" /> 
        
                </form> 
        
            </body> 
        
            </html>

There is no C#, or custom attributes in the ORM.

Thanks,
Nathan

4 Answers, 1 is accepted

answered on 04 Jun 2010, 05:21 PM

Hi Nathan,

1. The sorting problem arises when an initial sort value is specified in the order clause of the OpenAccessDataSource. I recommend you to use either the manual sorting (by setting the order attribute of the data source) or the automatic sorting provided by the grid.
2. It might be a bit confusing but the OpenAccessDataSource uses OQL (Object Query Language) but not SQL. OQL has similar syntax to SQL but is object-oriented. Unfortunately OQL does not support the BETWEEN statement and you will have to use the AND operator and two comparison statements to filter the results based on a time interval.
Hope that helps.

Regards,
Damyan Bogoev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

answered on 17 Jun 2010, 12:52 AM

Nevermind! I found a much better way than trying to foolishly hack something together.

The steps I took are the following:

Remove the OpenAccessDataSource, and any references to it, from the aspx page
Add an OnNeedDataSource to the grid the between functionality is needed on, and use LINQ to retrieve the data that would have come from the OpenAccessDataSource.
Convert the IQueriable results from the LINQ query to a dataset (code included below).
Use built in grid filtering without hacking!

public DataSet GetData()

{

DataSet ds = new DataSet();

IObjectScope scope = null;

try

{

scope = ApplicationProvider.GetNewObjectScope();

var results = from a in scope.Extent<EntityName>()

select a;

DataTable dt = LINQToDataTable(results);

ds.Tables.Add(dt);

}

catch (Exception ex)

{

throw;

}

finally

{

if (scope != null)

scope.Dispose();

}

return ds;

}

public static DataTable LINQToDataTable<T>(IEnumerable<T> varlist)

{

DataTable dtReturn = new DataTable();

// column names

PropertyInfo[] oProps = null;

if (varlist == null) return dtReturn;

foreach (T rec in varlist)

{

// Use reflection to get property names, to create table, Only first time, others will follow

if (oProps == null)

{

oProps = ((Type)rec.GetType()).GetProperties();

foreach (PropertyInfo pi in oProps)

{

Type colType = pi.PropertyType;

if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))

{

colType = colType.GetGenericArguments()[0];

}

dtReturn.Columns.Add(new DataColumn(pi.Name, colType));

}

DataRow dr = dtReturn.NewRow();

foreach (PropertyInfo pi in oProps)

{

dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue

(rec, null);

}

dtReturn.Rows.Add(dr);

}

return dtReturn;

}