Row Paging Using Temporary View

• Download source files - 2 Kb

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)