Custom Paging and Filters with SQL Datasource

3 posts, 1 answers
  1. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 19 Dec 2013 Link to this post

    Custom paging works great, I have about 400,000 rows, so I just return 100 from sql server when I change pages, using the need_datasource event.  Now it's going to get a bit more complicated when I have 8 different items I can sort or filter.

    I basically need to get the filter and convert that filter and sorting into the sql statement that I use to query the database.

    I also basically have to make 2 queries to get the VirtualItemCount.

    I'm basically doing a  "select ROW_NUMBER() OVER"  to make my row numbers and only getting the range of row numbers I want.  SQL Server 2012 has a fetch(I have 2008) and I saw there's a more effecient way than ROW_NUMBER() OVER, but this is easy for me to manipulate.  My main concern is CPU and memory on the webserver, so getting back just the number of rows in the page is the most important.

    I'm guessing this is the best way to do it. (I'm not going to use entity framework).

    I think the hard part is me translating the filters and sort orders to my sql statement.  I was wondering if there was an example like this.  No big deal if there isn't, I don't think it will be a big deal to do it, but I was wondering if maybe there was some code out there that already existed.
  2. Answer
    Genady Sergeev
    Genady Sergeev avatar
    1600 posts

    Posted 26 Dec 2013 Link to this post

    Hello Dan,

    Please take a look at the following forum thread that explains how to do it:

    You basically should try setting the EnableLinqExpressions property of the grid to false and then take the FilterExpression from the MasterTableView - it should be in a sql format when linq expressions are disabled.

    Genady Sergeev
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  3. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 30 Dec 2013 Link to this post

    Thanks, yeah it's been working very well like that.
Back to Top