1、存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
---------------------------------------------------------------
-- 分页存储过程(使用RowCount) --edit by SiBen
-- summary:
-- 获取表或表集合的分页数据
-- 当多表连接时,sort列必须指定表名
---------------------------------------------------------------
ALTER PROCEDURE [dbo].[Paging_RowCount]
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@RecordCount int = 0 output,
@PageCount int = 0 output --总页数
)
AS
/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<'
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) > 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
ELSE
SET @strSortColumn = @Sort
SET @operator = '>'
END
/* Set PK,Sort name */
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
IF CHARINDEX('.', @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*Get rows count.*/
DECLARE @str_Count_SQL nvarchar(500)
SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
SET @PageCount=CEILING((@RecordCount+0.0)/@PageSize)
/*Execute dynamic query*/
IF @PKTable = @SortTable and @PKName = @SortName
BEGIN
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
END
ELSE
BEGIN
/* Get PK Type */
DECLARE @pktype varchar(100)
DECLARE @pkprec int
SELECT @pktype=t.name, @pkprec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName
IF CHARINDEX('char', @pktype) > 0
SET @pktype = @pktype + '(' + CAST(@pkprec AS varchar) + ')'
/*Execute dynamic query*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
DECLARE @SortNullValue ' + @type + '
DECLARE @PKStartValue ' + @pktype + '
SET @SortNullValue=CAST('''' as '+ @type +')
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
'
)
END
2、调用
2.1 中间层
public static DataSet PageList(string connectionString, string tables, string pk, string sort, int pageNumber, int pageSize,
string fields, string filter, string group, out int recordCount, out int pageCount)
{
SqlParameter[] parameters ={ new SqlParameter("@Tables",SqlDbType.NVarChar,1000),
new SqlParameter("@PK",SqlDbType.NVarChar,100),
new SqlParameter("@Sort",SqlDbType.NVarChar,200),
new SqlParameter("@PageNumber",SqlDbType.Int),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@Fields",SqlDbType.NVarChar,1000),
new SqlParameter("@Filter",SqlDbType.NVarChar,1000),
new SqlParameter("@Group",SqlDbType.NVarChar,1000),
new SqlParameter("@RecordCount",SqlDbType.Int),
new SqlParameter("@PageCount",SqlDbType.Int)};
parameters[0].Value = tables;
parameters[1].Value = pk;
parameters[2].Value = sort;
parameters[3].Value = pageNumber;
parameters[4].Value = pageSize;
parameters[5].Value = fields;
parameters[6].Value = filter;
parameters[7].Value = group;
parameters[8].Direction = ParameterDirection.Output;
parameters[9].Direction = ParameterDirection.Output;
DataSet ds = ExecuteDataset(connectionString, "Paging_RowCount", parameters);
recordCount = (int)parameters[8].Value;
pageCount = (int)parameters[9].Value;
return ds;
}
2.2 dal-----bll
public DataSet PageList(string sort, int pageNumber, int pageSize, string fields, string filter, string group, out int recordCount, out int pageCount)
{
return DbHelperSQL.PageList(DbHelperSQL.ConnectionString, "V_M_Info_List", "InfoId", sort, pageNumber, pageSize, fields, filter, group, out recordCount, out pageCount);
}
2.3 .cs文件
private void LoadData(int pageindex)
{
string Where = GetCondition();
int RecordCount;
int pagecount;
DataSet rs = InfoBll.PageList("infoID DESC", pageindex, 18, "*", Where, "", out RecordCount, out pagecount);
AspNetPager1.RecordCount = RecordCount;
this.gv_List.DataSource = rs.Tables[0];
this.gv_List.DataBind();
}