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)
   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, _
           ByVal PageSize As Integer, ByVal FilterClause As String, _
           ByVal OrderClause As String) As DataTable
Dim oTable As DataTable
Dim SqlParam(4) As SqlParameter

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

Try
oTable = IMAWA_NETEasy.DataAccess.ExecuteDataset(CommandType.StoredProcedure,_
                 "sp_GetRowPaging", SqlParam).Tables(0)
Catch ex As Exception
   Throw ex
   Exit Function
End Try

Return oTable
End Function

Here is the Example :

dt = GetSQLData("Select * From GL.dbo.PRItem Where (PRNumber='" + 
                tbPRNumberChild.Text.Trim + "')", GridCurrentPage, 
                GridRowPerPage, SQLWhereClause, SQLOrderByClause)