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

SortCommand/FitlerCommand with ObjectDataSource

5 Answers 130 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Matt
Top achievements
Rank 1
Matt asked on 24 Jun 2010, 05:25 PM
We are using an ObjectDataSource with the RadGrid.  When I use filter and sort the full list is retrieved from the datasource and then filtered/sorted afterwards.

I would like to be able to use these in the retrieval process (and push the work back onto the SQL server).  However I can't seem to find a way to get the filtercommand or sortcommand where I want it (and tell the grid that I've already done the work).  With a standard DataGrid/ObjectDataSource pair I would simply add a sort parameter name and the gridview would fill it in.  Does the RadGrid support anything like this?

As another option it doesn't have to be so automatic, I can provide the values to the datasource using its onselecting event.  I just can't figure out where to get the values at that point.  Right now I have placed them in a page level variable, which is a little ugly.  I would like to be able to just access the current FilterCommand or SortCommand from the Grid.  Something like:  RadGrid.GetCurrentSortCommand() ?

Thanks
Matt

5 Answers, 1 is accepted

Sort by
0
Matt
Top achievements
Rank 1
answered on 24 Jun 2010, 07:50 PM
I got this working for sorting, using the sort parameter.  Not sure exactly what I did wrong before.

I'm still curious if there is a way that I can get the filter command from the grid so I can pass it in when I want?

Thanks,
0
Nikolay Rusev
Telerik team
answered on 25 Jun 2010, 03:32 PM
Hello Matt,

For your convenience I am attaching sample application demonstrating RadGrid paging/sorting/filtering with ObjectDataSource.

All the best,
Nikolay
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
0
Andrey
Top achievements
Rank 1
answered on 13 Oct 2010, 04:13 PM
 Hello, Nikolay.
Thanks for your example, it was very useful for me, but I have one question.
In example you used LINQ to SQL, in my project I used Oracle, so I  change your code:

<asp:ObjectDataSource ID="SourcePersonEntities" runat="server" TypeName="PersonEntitiesDataSource"
    SelectCountMethod="Count" SelectMethod="Select"
        SortParameterName="sortExpressions" EnablePaging="true"
        onprerender="SourcePersonEntities_PreRender"
        onselected="SourcePersonEntities_Selected" onselecting="SourcePersonEntities_Selecting" 
>
   <SelectParameters>
            <asp:Parameter Name="filterExpressions" Type="String" ConvertEmptyStringToNull="true" />
        </SelectParameters>
    </asp:ObjectDataSource>

public PersonEntityDataSet3[] Select(int maximumRows, int startRowIndex, string filterExpressions,
                                                   string sortExpressions)
    {
        OracleConnection conn = new OracleConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["AmlBlConString"].ConnectionString);
        conn.Open();
         
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        string cmdIn1 = " select  rownum rn, pe.pkpersonentity, pe.fkdatasource, nd.surname,nd.firstname,nd.middlename "
                       + " from personentity pe join namedetails nd on pe.pkpersonentity=nd.fkpersonentity ";
        if (!string.IsNullOrEmpty(filterExpressions))
            cmdIn1 += " and " + filterExpressions; // Problem is here
        if (!string.IsNullOrEmpty(sortExpressions))
            cmdIn1 += " ORDER BY " + sortExpressions;
 
 
        string cmdIn2 = " select rownum, o.* "
                      + " from ( " + cmdIn1 + ") o "
                      + "where rownum < ( :StartRowIndex + :MaximumRows) ";
         
        string cmdText = " BEGIN "
                       + " select personentity_simple_rec(o.pkpersonentity, o.fkdatasource, o.surname,o.firstname, o.middlename)"
                       + " bulk collect into :PE_DS"
                       + " from ( " + cmdIn2 + " ) o "
                       + " where o.rn> :StartRowIndex ; "
                       + " END;";
 
        cmd.CommandText = cmdText;
 
        OracleParameter param = new OracleParameter("PE_DS", OracleDbType.Array, ParameterDirection.Output);
        param.UdtTypeName = "PERSONENTITY_SIMPLE_DS";
        cmd.Parameters.Add(param);
         
        cmd.Parameters.Add("StartRowIndex", OracleDbType.Int32, startRowIndex, ParameterDirection.Input);
        cmd.Parameters.Add("MaximumRows", OracleDbType.Int32, maximumRows, ParameterDirection.Input);       
 
        try
        {
            cmd.ExecuteNonQuery();
            return (PersonEntityDataSet3[])param.Value;
             
        }
 
        catch
        {
            return null;
        }
 
        finally
        {
            conn.Close();
        }
 
    }


Paging and Sorting working OK, but with Filtering I have trouble: "filterExpressions" has non SQL syntax. How can I override standart expressions? For exaple, for "Contains" filter and "Surname" column override standart filterExpressions "   (iif(Surname ==null,"", Surname ).ToString().Contains("Smith")  "       on   " Surname LIKE %Smith%".

Thanks,
Andrey.
0
Iana Tsolova
Telerik team
answered on 14 Oct 2010, 10:06 AM
Hello Andrey,

Can you see if setting the EnablelinqExpressions property of the grid to false, makes any difference?

All the best,
Iana
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
0
Andrey
Top achievements
Rank 1
answered on 14 Oct 2010, 10:35 AM
 Thank you, Iana. It`s resolve my problem.
Tags
Grid
Asked by
Matt
Top achievements
Rank 1
Answers by
Matt
Top achievements
Rank 1
Nikolay Rusev
Telerik team
Andrey
Top achievements
Rank 1
Iana Tsolova
Telerik team
Share this question
or