OpenAccess ORM Problems with RadGrid Sorting and Filtering

Thread is closed for posting
5 posts, 0 answers
  1. Nathan
    Nathan avatar
    11 posts
    Member since:
    Sep 2008

    Posted 02 Jun 2010 Link to this post

    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:
    1. RadScriptManager
    2. RadGrid
    3. 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

     

  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 04 Jun 2010 Link to this post

    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.
  3. Nathan
    Nathan avatar
    11 posts
    Member since:
    Sep 2008

    Posted 16 Jun 2010 Link to this post

    Damyan,

    Thank you for the explanation.  I have successfully worked with your suggestion about the default sorting successfully.  However, I'm not quite sure still what is meant by " will have to use the AND operator and two comparison statements to filter the results based on a time interval."  Could you please elaborate on this and provide an example please?

    Thanks,
    Nathan
  4. Nathan
    Nathan avatar
    11 posts
    Member since:
    Sep 2008

    Posted 16 Jun 2010 Link to this post

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

    The steps I took are the following:
    1. Remove the OpenAccessDataSource, and any references to it, from the aspx page
    2. 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.
    3. Convert the IQueriable results from the LINQ query to a dataset (code included below).
    4. 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 == nullreturn 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;  
        }  
     

  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 17 Jun 2010 Link to this post

    Hi Nathan,

    I am glad to see that you have found a better approach to this problem.
    If any other questions arise please contact us back.

    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
Back to Top