注意:存储过程中的排序一定要有主键,否则分页可能不成功
存储过程1:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--参数说明-------------------------------------------------------------
/**//*
@strTable --要显示的表或多个表的连接
@strField --要查询出的字段列表,*表示全部字段
@intTop --最多读取记录数
@pageSize --每页显示的记录个数
@pageIndex --要显示那一页的记录
@strWhere --查询条件,不需where
@strSortKey --用于排序的主键
@strSortField --用于排序,如:id desc (多个id desc,dt asc)
@strOrderBy --排序,0-顺序,1-倒序
@pageCount --查询结果分页后的总页数
@RecordCount --查询到的总记录数
@UsedTime --耗时测试时间差
*/
Create PROCEDURE [dbo].[ThePagerIndex]
@strTable varchar(1000) = '[dbo].[ttable]',
@strField varchar(1000) = '*',
@intTop int = 5000,
@pageSize int = 20,
@pageIndex int = 1,
@strWhere varchar(1000) = '1=1',
@strSortKey varchar(1000) = 'id',
@strSortField varchar(500) = 'id DESC',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(200),@where1 varchar(200),@where2 varchar(200)
IF @strWhere is null or rtrim(@strWhere)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件
SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
END
--SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
IF @intTop<=0
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
ELSE
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(200)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
--print @sql
EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
IF @pageIndex=1 --第一页
BEGIN
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where2+'ORDER BY '+ @strSortField
END
Else
BEGIN
IF @strOrderBy=0
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+ ' FROM '+
@strTable+@where1+@strSortKey+'>(SELECT MAX('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+
CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+@strSortKey+' FROM '+@strTable+@where2+
'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
ELSE
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where1+
@strSortKey+'<(SELECT MIN('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+
@strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
END
print @sql
EXEC(@sql)
print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End
GO
存储过程二:
CREATE PROCEDURE SP_Pagination
/*
***************************************************************
** 通用分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
@PageCount --查询结果分页后的总页数
@RecordCount --查询到的总记录数
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@PageCount int OUTPUT,
@RecordCount int OUTPUT
)
AS
DECLARE @sql nvarchar(1000), @strWhere nvarchar(1000)
Declare @sqlcount INT
IF @Filter is null or rtrim(@Filter)=''
BEGIN--没有查询条件
SET @strWhere=' '
END
ELSE
BEGIN--有查询条件
SET @strWhere=' WHERE ('+@Filter+') ' --原本没有条件而加上此条件
END
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@Fields+' from '+ @Tables + @strWhere +') As tmptab'
EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @PageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
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
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
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)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
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 = ''
/*执行查询语句*/
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 + '
'
)
GO
使用方法:
先定义一下数据模型:
public class Question_model
{
int qid;
public int Qid
{
get { return qid; }
set { qid = value; }
}
string qtitle;
public string Qtitle
{
get { return qtitle; }
set { qtitle = value; }
}
}
BLL层代码
//本函数使用的是存储过程1的参数,如果使用2的话,直接修改sqlCommand与SqlParameter即可
public IList<Question_model> GetPage(int pageindex, int _pageSize, out int pageCount, out int RecordCount)
{
pageCount = 0;
RecordCount = 0;
IList<Question_model> list = new List<Question_model>();
using (SqlConnection conn = new SqlConnection(PubConstant.ConnectionString))
{
SqlCommand objcmd = new SqlCommand(".ThePagerIndex", conn);
objcmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para ={
new SqlParameter("@strTable",SqlDbType.VarChar,-1),
new SqlParameter("@strField",SqlDbType.VarChar,-1),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@strSortKey",SqlDbType.VarChar,-1),
new SqlParameter("@strSortField",SqlDbType.VarChar,-1),
new SqlParameter("@strOrderBy",SqlDbType.Bit),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@RecordCount",SqlDbType.Int),
new SqlParameter("@inttop",SqlDbType.Int,-1)
};
para[0].Value = "question";
para[1].Value = "*";
para[2].Value = _pageSize;
para[3].Value = pageindex;
para[4].Value = "qid";
para[5].Value = "qtime desc";
para[6].Value = 1;
para[7].Value = pageCount;
para[7].Direction = ParameterDirection.Output;
para[8].Value = RecordCount;
para[8].Direction = ParameterDirection.Output;
para[9].Value = -1;
objcmd.Parameters.AddRange(para);
conn.Open();
using (SqlDataReader reader = objcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
Question_model model = new Question_model();
model.Qid = Convert.ToInt32(reader["qid"]);
model.Qtitle = Convert.ToString(reader["qtitle"]);
list.Add(model);
}
}
RecordCount = Convert.ToInt32(objcmd.Parameters["@RecordCount"].Value);
pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
conn.Close();
conn.Dispose();
}
return list;
}
如果用第二个存储过程,部分修改如下:
SqlCommand objcmd = new SqlCommand(".SP_Pagination", conn);
objcmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para ={
new SqlParameter("@Tables",SqlDbType.VarChar,-1),
new SqlParameter("@PrimaryKey",SqlDbType.VarChar,-1),
new SqlParameter("@Sort",SqlDbType.VarChar,-1),
new SqlParameter("@CurrentPage",SqlDbType.Int),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@Fields",SqlDbType.VarChar,-1),
new SqlParameter("@Filter",SqlDbType.VarChar,-1),
new SqlParameter("@Group",SqlDbType.VarChar,-1),
new SqlParameter("@PageCount",SqlDbType.Int),
new SqlParameter("@RecordCount",SqlDbType.Int)
};
para[0].Value = "DataTable";
para[1].Value = "dataid";
para[2].Value = "NodeData desc";
para[3].Value = pageindex;
para[4].Value = _pageSize;
para[5].Value = "*";
para[6].Value = "";
para[7].Value = "";
para[8].Value = pageCount;
para[8].Direction = ParameterDirection.Output;
para[9].Value = RecordCount;
para[9].Direction = ParameterDirection.Output;
ASPX代码:【aspnetpager可以到http://www.webdiyer.com/
下载】
<div>
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate>
分页测试<br />
</HeaderTemplate>
<ItemTemplate>
<span style="width:100">编号:<%#Eval("qid")%> <%#Eval("qtitle")%></span><br />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblFooterTemplate" runat="server" Text="无相关数据" Visible="<%#bool.Parse((Repeater1.Items.Count==0).ToString())%>"></asp:Label>
</FooterTemplate>
</asp:Repeater>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" OnPageChanged="PageChanged" FirstPageText="首页" LastPageText="尾页"
NextPageText="下一页" PrevPageText="上一页" ShowInputBox="Always" Font-Size="13px" ShowPageIndexBox="Never" PageSize="5">
</webdiyer:AspNetPager>
</div>
最后CS代码:
public partial class Page_Test2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
Bind();
}
}
int currPage = 1;
int PageSize = 5;
public void Bind()
{
int pageCount;
int RecordCount;
Repeater1.DataSource = GetPage(currPage, PageSize, out pageCount, out RecordCount);
Repeater1.DataBind();
this.AspNetPager1.RecordCount = RecordCount;
this.AspNetPager1.CurrentPageIndex = currPage;
this.AspNetPager1.PageSize = PageSize;
}
protected void PageChanged(object sender, EventArgs e)
{
int pageCount;
int RecordCount;
Repeater1.DataSource = GetPage(this.AspNetPager1.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
Repeater1.DataBind();
}
}
分享到:
相关推荐
通用sql分页存储过程,提供12参数可供选择。其中,提供两种分页方案被选择和相关排序方式,支持自定义查询、自定义排序等
sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程
SQL 通用 分页 存储 过程 完整代码
比较通用的sql分页存储过程存储过程 比较通用的sql分页存储过程存储过程
通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
一个通用的sql分页存储过程源代码 数据比较大时此分页存储过程效率相当高
通用的sql分布存储过程,相信不用我多说你懂的!
不错的经典的分页、排序SQL 通用存储过程,可以大大节省Web数据库中大批量记录展现的性能问题。
Sql Server BootStrap Table 分页 通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
用sql写的存储过程,功能简单通用,可以支持返回总数
SQL Server 2000下的通用分页存储过程.sql
对SQL分页的万能存储过程,很全面的分析和描述,请大家支持
通用SQL 分页存储过程 其中一种是拼接字符串
1.查询返回的表、列名以及排序列没有写死,可以根据需要将这些放到存储过程的输入参数中; 2.适用于所有需要分页的单表或多表联合查询。
SQL通用分页存储过程,传入相应的参数即可!
通用分页存储过程 MSSQL通用分页存储过程
通用的存储过程sql分页查询语句,节约您的时间....
sql2005通用分页存储过程.非常方便的分页
好东西,现在不用每个分页都写一个存储过程了,只要调用的时候,把参数设置好就OK了...
SQL Server数据库的通用分页存储过程 非常好用 适用于各种场景开发使用