PDA

View Full Version : Grid server side paging with ASP



ramaraorouthu
2 Nov 2009, 10:37 PM
Hello,

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

Thanks in advance.
-RamaRao R.

ajaxvador
3 Nov 2009, 12:35 AM
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

ramaraorouthu
3 Nov 2009, 1:08 AM
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.

ajaxvador
3 Nov 2009, 2:08 AM
you want to extract lots of data or do pagination?

for big data use :

http://www.extjs.com/forum/showthread.php?t=17791&highlight=livegrid

ramaraorouthu
3 Nov 2009, 2:51 AM
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.

ajaxvador
3 Nov 2009, 2:52 AM
you use what type of database?

ramaraorouthu
3 Nov 2009, 2:59 AM
SQL SERVER

ajaxvador
3 Nov 2009, 5:36 AM
I hope that you will agree...

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


1. DEFAULT.ASP


<!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/adovbs.inc.txt )





<!--#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 & "},"
v = "},"
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


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

ramaraorouthu
4 Nov 2009, 5:22 AM
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.

ajaxvador
4 Nov 2009, 3:58 PM
Please post your javascript 'EXTJS' code.

ramaraorouthu
4 Nov 2009, 8:09 PM
I used ExtJS code which you have sent. Please take a look :)


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


});

kishmech
24 Dec 2009, 8:05 AM
Can you help me with the asp page with an access database query?