SortCommand/FitlerCommand with ObjectDataSource

[Matt]

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

[Matt]

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,

[Nikolay Rusev]

Hello Matt,

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

All the best,
Nikolay
the Telerik team

[Andrey]

 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.

[Iana Tsolova]

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

[Andrey]

 Thank you, Iana. It`s resolve my problem.