使用临时视图进行行分页

• 下载源代码文件 - 2 Kb

引言

你是否尝试过创建临时视图来显示你的行分页？
你不需要加载大量数据。

以下是你必须创建的存储过程

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

现在你可以使用这个函数来执行该存储过程

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

这是一个例子

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