Telerik
Skip Navigation LinksHome / Community / Code Library / ASP.NET and ASP.NET AJAX > Grid > True Custom Paging and Sorting

True Custom Paging and Sorting

Feed from this thread
  • Alfred Ortega Master Alfred Ortega's avatar

    Posted on Sep 13, 2006 (permalink)

    Requirements

    r.a.d.controls version

    2006 Q2 (probably a few earlier too)
    .NET version

    2.0 or 1.1
    Visual Studio version

    2005
    programming language

    c#
    browser support

    all browsers supported by r.a.d.controls


     
    PROJECT DESCRIPTION
    This simple project was the result of needing to use custom paging in the grid and wanting to use the new row_number() function in SQL 2005 to achieve it. 
    It only contains one page with 2 RadGrids on it.  The first grid only does paging of the database at 5 records per page.  The second adds dynamic sorting to the paging. 
    If you run this example against the db the results will not really vary that much from simply using the needdatasource event or binding it to a object/sql datasource.  But this is because there are only 12 records in the dummy db. If you run this against a large db you will notice a huge performance difference and of course so will your users!

    Al

    Reply

  • Telerik Admin admin's avatar

    Posted on Sep 18, 2006 (permalink)

    Hello Alfred,

    Thank you for the time you dedicated to assemble this application and post it in this code library thread. We have added 2000 telerik points to your account for the involvement - this demo will surely help other people which are looking for a similar custom paging/sorting functionality.

    Kind regards,

    Stephen,

    the telerik team


    www.telerik.com | dnn.telerik.com | www.sharepointcontrols.com | www.sitefinity.com | www.mcmscontrols.com

     

    Reply

  • Posted on May 2, 2007 (permalink)

    How to disable sorting features in Rad Grid 4.0.2.0 ?
    I has remove AllowSorting in Header but when add group expression, this grid still allow sorting asc as normal

    Reply

  • Alfred Ortega Master Alfred Ortega's avatar

    Posted on May 2, 2007 (permalink)

    Tuan,
    You need to set the AllowSorting property to "false" to disable sorting on any column.  The default is true so removing it just means use the default.

    Al

    Reply

  • Posted on Nov 26, 2007 (permalink)

    Does this work for filtering?

    Dave

    Reply

  • Alfred Ortega Master Alfred Ortega's avatar

    Posted on Nov 26, 2007 (permalink)

    Since only the records for that particular page are being returned (say 25 for example) then the filtering will work against those 25 but not against the whole table/view.  This is because the filtering doesn't happen until after the data has already been returned.

    If there is an OnFiltering server side event to capture (I don't know if there is or not but if not it would be nice) then you could capture the filter requirements and make it part of the sql's where clause and then it would work against the entire table/view.

    hth,
    Al

    Reply

  • jch0001 avatar

    Posted on Feb 19, 2008 (permalink)

    I was not able to download this file.?

    please help.

    thanks

    Reply

  • Telerik Admin admin's avatar

    Posted on Feb 19, 2008 (permalink)

    Hello jch0001,

    Thank you for drawing our attention to this issue. There was a temporary problem with downloads from our website, which has just been fixed. Now you should be able to download the file in question.

    Best wishes,
    Natalie Farah
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center

    Reply

  • Al Al's avatar

    Posted on Aug 12, 2008 (permalink)

    How do you edit an entry once you select it? I'm trying to use 

    EditMode="EditForms"

    but there's something missing. Do I need to use a user control edit form?

    Al

    Reply

  • Al Al's avatar

    Posted on Aug 12, 2008 (permalink)

    Here's the code I have now. What's missing. Something simple?

    <

    html xmlns="http://www.w3.org/1999/xhtml" >

    <

    head runat="server">

    <title>Address Page</title>

    </

    head>

    <

    body>

    <form id="form1" runat="server">

    <rad:RadAjaxManager ID="RadAjaxManager1" runat="server">

    <AjaxSettings>

    <rad:AjaxSetting AjaxControlID="RadGrid1">

    <UpdatedControls>

    <rad:AjaxUpdatedControl ControlID="RadGrid1" />

    </UpdatedControls>

    </rad:AjaxSetting>

    </AjaxSettings>

    </rad:RadAjaxManager>

    <rad:RadGrid ID="RadGrid1"

    runat="server" AutoGenerateColumns="False"

    GridLines="None" Skin="FFCIGreen"

    AllowPaging="True" AllowCustomPaging="True"

    EnableAJAXLoadingTemplate="True"

    PageSize="10" Width="100%" AllowAutomaticUpdates="true" AllowAutomaticInserts="true" AllowAutomaticDeletes="true"

    OnNeedDataSource="RadGrid1_NeedDataSource"

    OnEditCommand="RadGrid1_EditCommand"

    OnInsertCommand="RadGrid1_InsertCommand"

    OnPageIndexChanged="RadGrid1_PageIndexChanged">

    <ClientSettings>

    <Selecting AllowRowSelect="True"></Selecting>

    </ClientSettings>

    <AJAXLoadingTemplate>

    <div >Loading....</div>

    </AJAXLoadingTemplate>

    <MasterTableView DataKeyNames="AddressID" AutoGenerateColumns="false" EditMode="EditForms">

    <Columns>

    <rad:GridButtonColumn Text="Select" CommandName="Select"></rad:GridButtonColumn>

    <rad:GridEditCommandColumn UniqueName="EditCommandColumn" ButtonType="ImageButton"

    HeaderText="Edit">

    </rad:GridEditCommandColumn>

    <rad:GridBoundColumn DataField="AddressID" DataType="System.Int32" HeaderText="AddressID"

    ReadOnly="True" SortExpression="AddressID" UniqueName="AddressID">

    </rad:GridBoundColumn>

    <rad:GridBoundColumn DataField="StreetAddress" HeaderText="StreetAddress" SortExpression="StreetAddress"

    UniqueName="StreetAddress">

    </rad:GridBoundColumn>

    <rad:GridBoundColumn DataField="City" HeaderText="City" SortExpression="City" UniqueName="City">

    </rad:GridBoundColumn>

    <rad:GridBoundColumn DataField="StateCD" HeaderText="StateCD" SortExpression="StateCD"

    UniqueName="StateCD">

    </rad:GridBoundColumn>

    <rad:GridBoundColumn DataField="ZipCode" HeaderText="ZipCode" SortExpression="ZipCode"

    UniqueName="ZipCode">

    </rad:GridBoundColumn>

    </Columns>

    <RowIndicatorColumn Visible="True">

    <HeaderStyle Width="20px" />

    </RowIndicatorColumn>

    <PagerStyle NextPageText="Next" PrevPageText="Prev" Mode="NextPrevAndNumeric" Position="Bottom" >

    </PagerStyle>

    <EditFormSettings EditFormType="Template">

    <FormTemplate>

    Demo mode

    <table>

    <tr>

    <td align="right" colspan="2">

    <asp:Button ID="btnUpdate" Text='<%# (Container is GridEditFormInsertItem) ? "Insert" : "Update" %>'

    runat="server" CommandName='<%# (Container is GridEditFormInsertItem) ? "PerformInsert" : "Update" %>'>

    </asp:Button>&nbsp;

    <

    asp:Button ID="btnCancel" Text="Cancel" runat="server" CausesValidation="False"

    CommandName

    ="Cancel"></asp:Button></td>

    </tr>

    </table>

    </FormTemplate>

    </EditFormSettings>

    </MasterTableView>

    </rad:RadGrid>

    </form>

    </

    body>

    </

    html>

    using

    System;

    using

    System.Data;

    using

    System.Configuration;

    using

    System.Collections;

    using

    System.Web;

    using

    System.Web.Security;

    using

    System.Web.UI;

    using

    System.Web.UI.WebControls;

    using

    System.Web.UI.WebControls.WebParts;

    using

    System.Web.UI.HtmlControls;

    public

    partial class Operations_Address : System.Web.UI.Page

    {

    protected int pageIndex; //for paging only

    protected int pageSize = 10;

    protected void Page_Load(object sender, EventArgs e)

    {

    if (!Page.IsPostBack)

    {

    SetVirtualItemCount();

    pageIndex = 1;

    int addressID = -1;

    if (!String.IsNullOrEmpty(Request.QueryString["AddressID"]))

    {

    Boolean validInt = Int32.TryParse(Request.QueryString["AddressID"].ToString(), out addressID);

    if (validInt)

    {

    int[] selectedIndex = { addressID };

    RadGrid1.SelectedIndexes.Add(selectedIndex);

    }

    }

    }

    }

    //Get the count of records in the table and use the number to set the virtual count

    //of each grid.

    protected void SetVirtualItemCount()

    {

    try

    {

    object tempResult = DataUtilities.GetScalarValue("Select count(*) from Address");

    int addressCount = -1;

    if (int.TryParse(tempResult.ToString(),out addressCount))

    {

    RadGrid1.VirtualItemCount = addressCount;

    }

    }

    catch (Exception e)

    {

    // I dunno.

    }

    }

    protected void RadGrid1_PageIndexChanged(object source, Telerik.WebControls.GridPageChangedEventArgs e)

    {

    //db paging starts with 1, RadGrid starts with 0 so we add 1 to what ever page is being added.

    pageIndex = e.NewPageIndex + 1;

    // No need for a rgPaging.Rebind()

    }

    protected void RadGrid1_NeedDataSource(object source, Telerik.WebControls.GridNeedDataSourceEventArgs e)

    {

    RadGrid1.DataSource = getPagingData(pageIndex, pageSize);

    }

    protected DataTable getPagingData(int PageNumber, int PageSize)

    {

    String sqlStatement = "Select * from Address ";

    sqlStatement +=

    "WHERE AddressID between (" + PageNumber + "-1)*" + PageSize + " and (" + PageNumber + ")*" + PageSize;

    return DataUtilities.GetDataTable(sqlStatement);

    }

    protected void RadGrid1_InsertCommand(object source, Telerik.WebControls.GridCommandEventArgs e)

    {

    }

    protected void RadGrid1_EditCommand(object source, Telerik.WebControls.GridCommandEventArgs e)

    {

    String s = "test";

    }

    }

    Reply

  • Alfred Ortega Master Alfred Ortega's avatar

    Posted on Aug 13, 2008 (permalink)

    Al,
    While it's certainly possible to use a user-control to do the editing it is not required.  You can edit data in a number of ways and depending on the version (and if it's classic or prometheus) it could vary.

    If your are using the latest controls and want to use a usercontrol check out: http://www.telerik.com/DEMOS/ASPNET/Prometheus/Grid/Examples/DataEditing/UserControlEditForm/DefaultCS.aspx

    Or to build a template form check out: http://www.telerik.com/DEMOS/ASPNET/Prometheus/Grid/Examples/DataEditing/TemplateFormUpdate/DefaultCS.aspx

    -Al

    Reply

  • Matt Davis avatar

    Posted on Sep 4, 2008 (permalink)

    How would I adapt this to get a set of paged (and sortable) results based on categoryid?

    I made the following change:

    from test where categoryid = 1

    This seems to work ok but then if I try and add a parameter so I can pass in any categoryid like: from test where ' + @categoryid + ', I get a convesion error.

    Any ideas?

    Reply

  • Alfred Ortega Master Alfred Ortega's avatar

    Posted on Sep 4, 2008 (permalink)

    Matt,
    To do you'll need to add your where clause to the CTE portion of the Stored Procedure.  Using the demo db provided in the sample I added a column to the test table called "CategoryID" of type int to replicate what I think you're trying to do.  I then populated each row with a "1" or a "2" so I can filter the results.

    Now we'll create a paging/sorting stored procedure to filter by the categoryid column just created.

    Create PROCEDURE [dbo].[sp_PagingAndSortingExampleByCategory]
    (
     @pageNum int
    ,@pageSize int
    ,@orderby nvarchar(100)
    ,@categoryId int  //We add our categoryid parameter
    )AS

    SET NOCOUNT ON
     
    declare @lownum nvarchar(10)
    declare @highnum nvarchar(10)
    declare @sql nvarchar(4000)
    declare @catID nvarchar(1)  //A string version to cast the categoryid to

    set @lownum = convert(nvarchar(10), (@pagesize * (@pagenum - 1)))
    set @highnum = convert(nvarchar(10), (@pagesize * @pagenum))
    //Set the variable by converting the int to a nvarchar
    set @catID = convert(nvarchar(1),@categoryid)

    set @sql = 'select * from (
      select row_number() over (order by ' + @orderby  + ') as rownum
             ,id
             ,textfield1
             ,textfield2
      from test
      WHERE CategoryID = ' + @catID + '  //Add the new where clause
     ) A WHERE RowNum > ' + @lownum + ' AND rownum <= ' + @highnum

    EXEC sp_executesql @sql   


    MS-SQL doesn't like to mix strings and int's so that is why the @catID variable is a string version of the supplied category.  Then when the @sql parameter is created it's all strings being combined.

    hth
    Al

    Reply

  • Matt Davis avatar

    Posted on Sep 4, 2008 (permalink)

    Thanks Alfred.

    Reply

  • Matt Davis avatar

    Posted on Sep 11, 2008 (permalink)

    Hello again, any idea how I can get this to work with dates?

    I have been trying something like the below, where I try to query a date column between 2 passed in dates, however it returns the error 'incorrect syntax near '9'.

    set

    set @sql = 'select * from (
      select row_number() over (order by ' + @orderby  + ') as rownum
             ,id
             ,textfield1
             ,textfield2
      from test
      WHERE date Between ' + CONVERT(nvarchar(20),  @startDate) + '  AND ' + CONVERT(nvarchar(20), @endDate)  + ' ) A WHERE RowNum > ' + @lownum + ' AND rownum <= ' + @highnum

    EXEC sp_executesql @sql  

    Reply

  • Alfred Ortega Master Alfred Ortega's avatar

    Posted on Sep 12, 2008 (permalink)

    SqlServer is kind of weird.  In this case the dates have to be enclosed in single quotes but since your in a string you have to use the escape character which for SqlServer is also a single quote. Hopefull the below snippet helps - remember all quotes are single.

    declare @sql nvarchar(4000)
    declare @startDate nvarchar(12)
    declare @endDate nvarchar(12)

    set @startdate = convert(nvarchar(12),getdate() - 180)
    set @enddate = convert(nvarchar(12),getdate())

    set @sql = 'select count(*) from yourtable where datefield between ''' + @startDate + ''' and ''' + @enddate + ''''

    EXEC sp_executesql @sql 

    Reply

Back to Top

Skip Navigation LinksHome / Community / Code Library / ASP.NET and ASP.NET AJAX > Grid > True Custom Paging and Sorting

Powered by Sitefinity ASP.NET CMS

Contact Us | Site Feedback | Terms of Use | Privacy Policy
Copyright © 2002-2010 Telerik. All rights reserved.