SQL分页存储过程

来源：互联网发布：手机淘宝收藏降价提醒编辑：程序博客网时间：2024/04/30 01:42

下面是SQL命令在企业管理器里面直接创建一个存储过程然后输入下面内容保存为GetCurrentPageData(这个名字可以自己更改)

CREATE          PROCEDURE [dbo].[GetCurrentPageData]
    @TableName             varchar(1000),          --table name 换成SQL语句
    @KeyFildName           varchar(255),          --key filed name
    @PageIndex               int,                    --page index
    @PageSize                 int,                  --page size
    @TotalRecord            int output            --return recordNum
AS

IF lower(left(ltrim(@TableName),6))='select'
BEGIN
SET @TableName='SELECT TOP 999999999 '+ substring(@TableName,7,8000)
END

declare @strSQL nvarchar(4000)
DECLARE @TopNum int
SET @TopNum=(@PageIndex-1)*@PageSize
SET @strSQL=N'select @TotalRecord=count(*) from ('+@TableName+') AS T '

print(@strSQL)
EXEC SP_EXECUTESQL @strSQL,N'@TotalRecord int OUT',@TotalRecord OUT
IF @TotalRecord is Null
begin
set @TotalRecord=1
end
PRINT(@TotalRecord)

PRINT(@TableName)

SET @strSQL='SELECT TOP '+str(@PageSize)+' *
                         FROM ('+@TableName+') AS T1
             WHERE '+@KeyFildName+ ' NOT IN (SELECT TOP '+str(@TopNum)+' '+@KeyFildName+'
                                                                                     FROM ('+@TableName+') AS T2)'
print (@strSQL)
exec(@strSQL)
GO
以上存储过程建立完成后就开始在C#项目下面建立一个类库,在类库新建一个类,然后在类里面建一个共用方法如下:

/// <summary> 
/// 获取当前页的信息 
/// </summary> 
/// <param name="sql">SQL语句或是视图</param> 
/// <param name="keyFildName">关键字段名(注意:一定要是唯一性的字段)</param> 
/// <param name="pageIndex">当前页数</param> 
/// <param name="pageSize">每页大小</param> 
/// <param name="totalRecord">记录总数</param> 
/// <returns></returns> 
public DataTable GetCurrentPageDataForSql(string sql, string keyFildName,int pageIndex, int pageSize, out int totalRecord)
        {
            totalRecord = 0;
            SqlParameter[] sqlPar = new SqlParameter[6];
            sqlPar[0] = new SqlParameter("@TableName", SqlDbType.VarChar);
            sqlPar[0].Direction = ParameterDirection.Input;
            sqlPar[0].Value = sql;
            sqlPar[1] = new SqlParameter("@KeyFildName", SqlDbType.VarChar);
            sqlPar[1].Direction = ParameterDirection.Input;
            sqlPar[1].Value = keyFildName;
            sqlPar[2] = new SqlParameter("@PageIndex", SqlDbType.Int);
            sqlPar[2].Direction = ParameterDirection.Input;
            sqlPar[2].Value = pageIndex;
            sqlPar[3] = new SqlParameter("@PageSize", SqlDbType.Int);
            sqlPar[3].Direction = ParameterDirection.Input;
            sqlPar[3].Value = pageSize;
            sqlPar[4] = new SqlParameter("@TotalRecord", SqlDbType.Int);
            sqlPar[4].Direction = ParameterDirection.Output;
            DataTable dt = DataAccess.GetDataTable_Proc("GetCurrentPageDatal", sqlPar);    //这方法要自己去写一个, 
                                                                                                                                                   //只是一个调用存储过程的方法 
            if (sqlPar[4].Value != DBNull.Value && sqlPar[4].Value != null)
            {
                totalRecord = Convert.ToInt32(sqlPar[4].Value);
            }
            return dt;
        }
所有代码就如上了