Json + Paginação em asp

[blayck 0]

Pessoal,

 

Gostaria de exemplo de conexão com o banco e paginação feita em asp e utilizando json.

 

Alguém tem algo pronto? Gostaria pra estudar o código.

[xanburzum 169]

olha este exemplo, para retornar dados JSON :

CLASSe para ASP :

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

 

' 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--------------------------------------------------------------------------------------

 

paginação (SQL SERVER) :

 

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