Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Grid server side paging with ASP

  1. #1

    Default Grid server side paging with ASP

    Hello,

    I am looking for Grid server side paging with ASP. Please let me know your thoughts.

    Thanks in advance.
    -RamaRao R.

  2. #2
    Sencha Premium Member ajaxvador's Avatar
    Join Date
    Nov 2007
    Location
    PARIS, FRANCE
    Posts
    211

    Default JSON Utility

    Hi,

    for your looking for Grid server side paging with ASP :

    JSON RETURN DATA :

    try this CLASS for ASP :

    http://code.google.com/p/aspjson/

    or for SQL SERVER :


    ' example: exec getJson 'campagne', 1 -- tablename, number of record

    create procedure [dbo].[GetJSON]
    (
    @table_name varchar(50),
    @registries_per_request smallint = null
    )
    as
    begin
    if((select count(*) from information_schema.tables where table_name = @table_name) > 0)
    begin
    declare @json varchar(max),
    @line varchar(max),
    @columns varchar(max),
    @sql nvarchar(max),
    @columnNavigator varchar(50),
    @counter tinyint,
    @size varchar(10)

    if (@registries_per_request is null)
    begin
    set @size = ''
    end
    else
    begin
    set @size = 'top ' + convert(varchar, @registries_per_request)
    end
    set @columns = '{'

    declare schemaCursor cursor
    for select column_name from information_schema.columns where table_name = @table_name
    open schemaCursor

    fetch next from schemaCursor
    into @columnNavigator

    select @counter = count(*) from information_schema.columns where table_name = @table_name

    while @@fetch_status = 0
    begin
    set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + '''''''
    set @counter = @counter - 1
    if(0 != @counter)
    begin
    set @columns = @columns + ','
    end

    fetch next from schemaCursor
    into @columnNavigator
    end

    set @columns = @columns + '}'

    close schemaCursor
    deallocate schemaCursor

    set @json = '['

    set @sql = 'select ' + @size + '''' + @columns + ''' as json into tmpJsonTable from ' + @table_name
    exec sp_sqlexec @sql

    select @counter = count(*) from tmpJsonTable

    declare tmpCur cursor
    for select * from tmpJsonTable
    open tmpCur

    fetch next from tmpCur
    into @line

    while @@fetch_status = 0
    begin
    set @counter = @counter - 1
    set @json = @json + @line
    if ( 0 != @counter )
    begin
    set @json = @json + ','
    end

    fetch next from tmpCur
    into @line
    end

    set @json = @json + ']'

    close tmpCur
    deallocate tmpCur
    drop table tmpJsonTable

    select @json as json
    end

    end--------------------------------------------------------------------------------------

    PAGINATION (SQL SERVER 2005) :

    ALTER PROCEDURE [dbo].[req_pag]


    @datasrc nvarchar(200)
    ,@orderBy nvarchar(200)
    ,@orderBy2 nvarchar(200)
    ,@fieldlist nvarchar(200) = '*'
    ,@filter nvarchar(200) = ''
    ,@filter2 nvarchar(200) = ''
    ,@join nvarchar(500) = ''
    ,@pageNum int = 1
    ,@pageSize int = NULL
    AS
    SET NOCOUNT ON
    DECLARE
    @STMT nvarchar(max)
    ,@recct int

    IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
    IF @pageSize IS NULL BEGIN
    SET @STMT = 'SELECT ' + @fieldlist +
    'FROM ' + @datasrc +
    ' ' + @filter +
    ' ORDER BY ' + @orderBy
    EXEC (@STMT)
    END ELSE BEGIN
    SET @STMT = 'SELECT @recct = COUNT(*)
    FROM ' + ' ' + @filter

    --print @STMT
    EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
    SELECT @recct AS recct
    --print @recct
    DECLARE
    @lbound int,
    @ubound int

    SET @pageNum = ABS(@pageNum)
    SET @pageSize = ABS(@pageSize)
    IF @pageNum < 1 SET @pageNum = 1
    IF @pageSize < 1 SET @pageSize = 1
    SET @lbound = ((@pageNum - 1) * @pageSize)
    SET @ubound = @lbound + @pageSize + 1
    IF @lbound >= @recct BEGIN
    SET @ubound = @recct + 1
    SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if -- no records would be on the
    -- specified page
    END
    SET @STMT = 'SELECT ' + @fieldlist + '
    FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
    FROM ' + '
    ' + @filter2 + ' '
    + @join + '

    WHERE
    row > ' + CONVERT(varchar(9), @lbound) + ' AND
    row < ' + CONVERT(varchar(9), @ubound) + ' ' +
    @orderBy2
    -- print @STMT
    EXEC (@STMT) -- return requested records
    END
    Vador

  3. #3

    Default

    Thanks Vador for your thoughts.

    As per my requirement, I cannot able to push more load on database. I am looking for server side paging using either ASP or ASP.NET with just DB connection.

    Please let me know your thoughts.

    Thanks,
    RamaRao R.

  4. #4
    Sencha Premium Member ajaxvador's Avatar
    Join Date
    Nov 2007
    Location
    PARIS, FRANCE
    Posts
    211

    Default

    you want to extract lots of data or do pagination?

    for big data use :

    http://www.extjs.com/forum/showthrea...light=livegrid
    Vador

  5. #5

    Default

    In my typical grid, I will be having around 25,000 records. I have already faced performance issues with buffering in 'livegrid'. So, I came back to native grid paging with ASP.NET.

    Please let me know is there any sample with ASP.NET(VB.NET)

    Thanks,
    RamaRao R.

  6. #6
    Sencha Premium Member ajaxvador's Avatar
    Join Date
    Nov 2007
    Location
    PARIS, FRANCE
    Posts
    211

    Default

    you use what type of database?
    Vador

  7. #7

    Default

    SQL SERVER

  8. #8
    Sencha Premium Member ajaxvador's Avatar
    Join Date
    Nov 2007
    Location
    PARIS, FRANCE
    Posts
    211

    Default

    I hope that you will agree...

    Paging data with ASP, and SQL SERVER. I used in this code a stored procedure


    1. DEFAULT.ASP

    PHP Code:
    <!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>
    <
    meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <
    title>Document sans nom</title>
    <
    link rel="stylesheet" type="text/css" href="scripts3/resources/css/ext-all.css" />         
     <
    script type="text/javascript" src="scripts3/adapter/ext/ext-base.js"></script>
     <script type="text/javascript" src="scripts3/ext-all.js"></script>
    </head>

    <body>
    <script type="text/javascript">

    Ext.BLANK_IMAGE_URL = "../../resources/images/default/s.gif"
    Ext.onReady(function(){

    var ds = new Ext.data.Store({
            proxy     : new Ext.data.HttpProxy({
            url       : 'paging.asp', method: 'POST' }),
            baseParams:{task: "read"},
            
            reader: new Ext.data.JsonReader({ root: 'results', totalProperty: 'total'},
            
            [    {name: 'id', mapping: 'dist'} 
                ,{name: 'name', mapping: 'name'} 
                ,{name: 'f_name', mapping: 'f_name'} 
                
                ]) });

    var grid = new Ext.grid.GridPanel({
                    renderTo:'grid_div',
                    store: ds,    
                   autoLoad:'local',            
                    border : true,
                    loadMask:'Wait, please ...',    
                    columns: [
                         {id:'g_id',    header: 'id',         width: 30, sortable: true, dataIndex: 'id',hidden:false}  
                        ,{id:'g_name',  header: 'Name',       width: 50, sortable: true, dataIndex: 'name'          }  
                        ,{id:'g_f_name',header: 'First Name', width: 50, sortable: true, dataIndex: 'f_name'        } 
                    ],
                    height:400,
                      bbar: new Ext.PagingToolbar({
                pageSize: 25,
                store: ds,
                displayInfo: true,
                displayMsg: 'Displaying topics {0} - {1} of {2}',
                emptyMsg: "No topics to display" 
            })
                 
                });
          
                ds.load({params:{start:0, limit:25}});   // start page 1
                 

    });    

    </script>
    <div id="grid_div"></div>   
    </body>
    </html> 
    2.PAGING.ASP

    for retrieve this file adodb.inc ( http://www.asp101.com/articles/john/...adovbs.inc.txt )

    PHP Code:


    <!--#include file="ADODB.INC"-->                   

    <%

     
    task request("task")
     
    current_page request("start"
     
    limit_page request("limit"


                       
    strConn =  "driver={SQL Native Client};server=localhost;UID=user123;PWD=pass123;DATABASE=paging_test;"
                       
    Set cmd Server.CreateObject("ADODB.Command")
                        
    With cmd   
                        
    .ActiveConnection =  strConn
                        
    .CommandTimeout 90
                        
    .CommandText "paging"
                        
    .CommandType adCmdStoredProc                
                        
    .Parameters.Append .CreateParameter ("@recct",adInteger,adParamReturnValue)
                        .
    Parameters.Append .CreateParameter ("@datasrc"  ,adVarWChar,adParamInput,200," contact "
                        .
    Parameters.Append .CreateParameter ("@orderBy"  ,adVarWChar,adParamInput,200," id ")                       
                        .
    Parameters.Append .CreateParameter ("@fieldlist",adVarWChar,adParamInput,200," id, name, f_name ")                    
                        .
    Parameters.Append .CreateParameter ("@filter"   ,adVarWChar,adParamInput,200"  where name like '%A' ")
                        .
    Parameters.Append .CreateParameter ("@pageNum"  ,adInteger,adParamInput,,  current_page )                
                        .
    Parameters.Append .CreateParameter ("@PageSize" ,adInteger,adParamInput,, limit_page )               
                        
    Set RS = .Execute
                        RecordCount 
    RS.Fields(0)
                        
    Set RS RS.NextRecordset 
                        
                   End With
                    
                
    if rs.eof true and rs.bof true then
                       response
    .Write("{success: false}")
                       else
                       
    js "({""total"":""" &  RecordCount """,""results"":[" 
                    
    end if 
                     
                    
                    while  
    not RS.eof
                     
     
                    
                    js 
    js "{" 
                    
    js js """id":"   & """"  & rs("id") & ""","   
                    js = js & """
    name":"   """"  rs("name") & ""","   
                    
    js js """f_name"":"    """"  rs("f_name") & """"                   
                   
     
                
                         
    RS.movenext
                         
                         
    if not RS.eof   then 
                         js 
    js "},"
                         
    "},"
                         
    else
                         
    js js "}"
                         
    end if 
                         if 
    rs.eof true then
                                      
                 js 
    js "]})"
                 
    response.Write(js)
                         
    end if 
                         
                    
    wend
                    
                set cmd 
    nothing
                rs
    .Close Set rs Nothing  


    %> 
    3. Stored Procedure

    PHP Code:
    ALTER PROCEDURE [dbo].[Paging]
      @
    datasrc nvarchar(200)
     ,@
    orderBy nvarchar(200)
     ,@
    fieldlist nvarchar(200) = '*'
     
    ,@filter nvarchar(200) = ''
     
    ,@pageNum int 1
     
    ,@pageSize int NULL
    AS
      
    SET NOCOUNT ON
      
    DECLARE
         @
    STMT nvarchar(max)         -- SQL to execute
        
    ,@recct int                  -- total # of records (for GridView paging interface)

      
    IF LTRIM(RTRIM(@filter)) = '' SET @filter '1 = 1'
      
    IF @pageSize IS NULL BEGIN
        SET 
    @STMT =  'SELECT   ' + @fieldlist +
                     
    'FROM     ' + @datasrc +
                     
    ' ' + @filter +
                     
    ' ' + @orderBy
        EXEC 
    (@STMT)                 -- return requested records
      END 
    ELSE BEGIN
        SET 
    @STMT =  'SELECT   @recct = COUNT(*)
                      FROM     ' 
    + @datasrc ' ' 
                       
    + @filter
        EXEC sp_executeSQL 
    @STMT, @params N'@recct INT OUTPUT', @recct = @recct OUTPUT
        SELECT 
    @recct AS recct       -- return the total # of records

        
    DECLARE
          @
    lbound int,
          @
    ubound int

        SET 
    @pageNum ABS(@pageNum)
        
    SET @pageSize ABS(@pageSize)
        IF @
    pageNum 1 SET @pageNum 1
        
    IF @pageSize 1 SET @pageSize 1
        SET 
    @lbound = ((@pageNum 1) * @pageSize)
        
    SET @ubound = @lbound + @pageSize 1
        
    IF @lbound >= @recct BEGIN
          SET 
    @ubound = @recct 1
          SET 
    @lbound = @ubound - (@pageSize 1) -- return the last page of records if                                               -- no records would be on the
                                                  
    -- specified page
        END
        SET 
    @STMT =  'SELECT  ' + @fieldlist '
                      FROM    (
                                SELECT  ROW_NUMBER() OVER(ORDER BY ' 
    + @orderBy ') AS row, *
                                FROM    ' 
    + @datasrc '
                                        ' 
    + @filter '
                              ) AS tbl
                      WHERE
                              row > ' 
    CONVERT(varchar(9), @lbound) + ' AND
                              row < ' 
    CONVERT(varchar(9), @ubound)
        
    EXEC (@STMT)                 -- return requested records
      END 
    Vador

  9. #9

    Default

    Hi Vador,

    Thanks for your time and code.

    I can able to generate data from the paging.asp as shown below:

    ({"total":10,"results":[{"id":"1","name":"Record1","f_name":"P1"},{"id":"2","name":"Record2","f_name":"P2"},{"id":"3","name":"Record3","f_name":"P3"},{"id":"4","name":"Record4","f_name":"P4"},{"id":"5","name":"Record5","f_name":"P5"},{"id":"6","name":"Record6","f_name":"P6"},{"id":"7","name":"Record7","f_name":"P7"},{"id":"8","name":"Record8","f_name":"P8"},{"id":"9","name":"Record9","f_name":"P9"},{"id":"10","name":"Record10","f_name":"P10}]})

    default.asp is showing the grid with column headers and no rows. I could not able to load the data into grid.

    Please let me know are there any changes needed in grid population code.

    Thank you very much.
    -RamaRao R.

  10. #10
    Sencha Premium Member ajaxvador's Avatar
    Join Date
    Nov 2007
    Location
    PARIS, FRANCE
    Posts
    211

    Default

    Please post your javascript 'EXTJS' code.
    Vador

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •