Click here to Skip to main content
Click here to Skip to main content
Go to top

Row Paging Using Temporary View

, 8 May 2005
Rate this:
Please Sign up or sign in to vote.
Row Paging Using Temporary View

Introduction

Did you ever try to create temporary view for displaying your row page?
You don't have to load large of data.

Here is the stored procedure that you must Create :

CREATE   PROCEDURE sp_GetRowPaging
@SQLSyntax VarChar(8000),
@CurrentPage Int = 1,
@PageSize Int = 10,
@WhereClause varchar(1000),
@OrderBy varchar(1000)
AS
BEGIN
 
Declare @UserView   VarChar(50)
SET @UserView = REPLACE('Temporer' + system_user + 
    Str(DatePart(minute,GetDate()))+'_'+
    Str(DatePart(second,GetDate()))+'_'+
    Str(DatePart(millisecond,GetDate())),' ','')
 
-- Delete Temporary View
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+
   @UserView+']') and OBJECTPROPERTY(id, N'IsView') = 1)
<PRE>   EXEC ('drop view 'view'+@UserView)
EXEC ('CREATE VIEW [dbo].['+@UserView+'] AS '+@SQLSyntax) EXEC ('GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].['+@UserView+'] TO [InternetUsers]') EXEC ('GRANT SELECT ON [dbo].['+@UserView+'] TO [InternetUsers]') DECLARE @SqlStatement varchar(8000), @SqlSelect varchar(8000), @SqlInsertedColumn varchar(1000), @SqlCommand varchar(8000), @ColumnName varchar(30), @ColumnType Varchar(30), @ColumnPrec Int, @ColumnScale Int, @ColumnNullable Int, @NullStatement varchar(12), @StrRowFrom varchar(10), @StrRowTo varchar(10) IF @CurrentPage <=1 SET @CurrentPage = 1 IF @PageSize <=0 SET @PageSize = 10 SET @StrRowFrom = CAST(((@CurrentPage * @PageSize) - (@PageSize-1)) As Varchar) SET @StrRowTo = CAST((@CurrentPage * @PageSize) As Varchar) DECLARE TableTemplate SCROLL CURSOR FOR SELECT c.name, type.name, c.prec, c.scale, c.isnullable FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes type on type.xusertype = c.xtype WHERE o.name = @UserView FOR READ ONLY OPEN TableTemplate FETCH NEXT FROM TableTemplate INTO @ColumnName, @ColumnType, @ColumnPrec, @ColumnScale, @ColumnNullable SET @SqlStatement = 'DECLARE @RetrievedRowCount BigInt ' + CHAR(13) SET @SqlStatement = @SqlStatement + 'DECLARE @DumpingRows TABLE ( ROWID BigInt NOT NULL PRIMARY KEY IDENTITY ,' SET @SqlInsertedColumn = ' INSERT INTO @DumpingRows ( ' SET @SqlSelect = ' SELECT ' WHILE @@FETCH_STATUS = 0 BEGIN IF @ColumnNullable = 1 SET @NullStatement = 'NULL' ELSE SET @NullStatement = 'NOT NULL' SET @SqlStatement = @SqlStatement + LTRIM(RTRIM(@ColumnName)) + SPACE(8) IF LTRIM(RTRIM(Lower(@ColumnType))) IN ('bigint','bit','datetime','float', 'image','int','money','ntext','real','smalldatetime','smallint', 'smallmoney','sql_variant','text','timestamp','tinyint', 'uniqueidentifier') BEGIN SET @SqlStatement = @SqlStatement + LTRIM(RTRIM(Lower(@ColumnType))) + SPACE(8) + @NullStatement END ELSE IF LTRIM(RTRIM(Lower(@ColumnType))) IN ('binary','char','nchar', 'nvarchar','varbinary','varchar') BEGIN SET @SqlStatement = @SqlStatement + LTRIM(RTRIM(Lower(@ColumnType))) + '(' + LTRIM(RTRIM(CAST(@ColumnPrec As varchar))) + ')' + SPACE(8) + @NullStatement END ELSE BEGIN -- 'decimal','numeric' SET @SqlStatement = @SqlStatement + LTRIM(RTRIM(Lower(@ColumnType))) + '(' + LTRIM(RTRIM(CAST(@ColumnPrec As varchar))) + ',' + LTRIM(RTRIM(CAST(@ColumnScale As varchar))) + ')' + SPACE(8) + @NullStatement END SET @SqlInsertedColumn = @SqlInsertedColumn + LTRIM(RTRIM(@ColumnName)) SET @SqlSelect = @SqlSelect + LTRIM(RTRIM(@ColumnName)) FETCH NEXT FROM TableTemplate INTO @ColumnName, @ColumnType, @ColumnPrec, @ColumnScale, @ColumnNullable IF @@FETCH_STATUS = 0 BEGIN SET @SqlStatement = @SqlStatement + ',' + CHAR(13) SET @SqlInsertedColumn = @SqlInsertedColumn + ',' SET @SqlSelect = @SqlSelect + ',' END ELSE BEGIN SET @SqlStatement = @SqlStatement + ')' + CHAR(13) SET @SqlInsertedColumn = @SqlInsertedColumn + ')' SET @SqlSelect = @SqlSelect + ' ' + CHAR(13) END END CLOSE TableTemplate DEALLOCATE TableTemplate SET @SqlSelect = @SqlSelect + ' FROM ' + @UserView IF (@WhereClause Is NOT Null) AND LTRIM(RTRIM(@WhereClause)) != '' SET @SqlSelect = @SqlSelect + ' WHERE ' + @WhereClause IF (@OrderBy Is Not Null) AND LTRIM(RTRIM(@OrderBy)) != '' SET @SqlSelect = @SqlSelect + ' ORDER BY ' + @OrderBy SET @SqlCommand = LTRIM(RTRIM(@SqlStatement)) + ' ' + CHAR(13) SET @SqlCommand = @SqlCommand + LTRIM(RTRIM(@SqlInsertedColumn)) + ' ' + CHAR(13) SET @SqlCommand = @SqlCommand + LTRIM(RTRIM(@SqlSelect)) + ' ' + CHAR(13) SET @SqlCommand = @SqlCommand + ' SET @RetrievedRowCount = @@ROWCOUNT ' + CHAR(13) SET @SqlCommand = @SqlCommand + ' SELECT TotalRows = @RetrievedRowCount, * FROM @DumpingRows WHERE ROWID BETWEEN ' + LTRIM(RTRIM(@StrRowFrom)) + ' AND ' + LTRIM(RTRIM(@StrRowTo)) + CHAR(13) EXEC(@SqlCommand) END -- Delete Temporary View if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+ @UserView+']') and OBJECTPROPERTY(id, N'IsView') = 1) EXEC ('drop view '+@UserView) GO

Now you can execute that stored procedure using this function :

Public Function 
GetSQLData(ByVal SQLScript As String, ByVal CurrentPage As Integer, _<BR>           ByVal PageSize As Integer, ByVal FilterClause As String, _<BR>           ByVal OrderClause As String) As DataTable
Dim oTable As DataTable
Dim SqlParam(4) As SqlParameter

SqlParam(0) = New SqlParameter("@SQLSyntax", SqlDbType.VarChar, 8000, _ <BR>               ParameterDirection.Input, False, 0, 0, "", _<BR>               DataRowVersion.Current, SQLScript)
SqlParam(1) = New SqlParameter("@CurrentPage", SqlDbType.Int, 4, _<BR>               ParameterDirection.Input, False, 0, 0, "", _<BR>               DataRowVersion.Current, CurrentPage)
SqlParam(2) = New SqlParameter("@PageSize", SqlDbType.Int, 4, _<BR>               ParameterDirection.Input, False, 0, 0, "", _<BR>               DataRowVersion.Current, PageSize)
SqlParam(3) = New SqlParameter("@WhereClause", SqlDbType.VarChar, 1000, _<BR>               ParameterDirection.Input, False, 0, 0, "", _<BR>               DataRowVersion.Current, FilterClause)
SqlParam(4) = New SqlParameter("@OrderBy", SqlDbType.VarChar, 1000, _<BR>               ParameterDirection.Input, False, 0, 0, "", _<BR>               DataRowVersion.Current, OrderClause)

Try
oTable = IMAWA_NETEasy.DataAccess.ExecuteDataset(CommandType.StoredProcedure,_<BR>                 "sp_GetRowPaging", SqlParam).Tables(0)
Catch ex As Exception
   Throw ex
   Exit Function
End Try<BR>
Return oTable
End Function
Here is the Example :
dt = GetSQLData("Select * From GL.dbo.PRItem Where (PRNumber='" + <BR>                tbPRNumberChild.Text.Trim + "')", GridCurrentPage, <BR>                GridRowPerPage, SQLWhereClause, SQLOrderByClause)

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

bluechip_asia
Web Developer
Indonesia Indonesia
No Biography provided

Comments and Discussions

 
GeneralLink PinmemberYulianto.8-May-05 22:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 9 May 2005
Article Copyright 2005 by bluechip_asia
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid