通用SQL分页存储过程
程序员文章站
2022-06-26 13:30:43
...
注意：存储过程中的排序一定要有主键，否则分页可能不成功
存储过程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
[email protected] 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 ('[email protected]+') AND ' --本来有条件再加上此条件
SET @where2=' WHERE ('[email protected]+') ' --原本没有条件而加上此条件
END
--SET @sql='SELECT @intResult=COUNT(*) FROM '[email protected][email protected]
IF @intTop<=0
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '[email protected]+' from '+ @strTable + @where2 +') As tmptab'
END
ELSE
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(200)) +' '[email protected]+' 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))+' '[email protected]+' FROM '[email protected][email protected]+'ORDER BY '+ @strSortField
END
Else
BEGIN
IF @strOrderBy=0
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '[email protected]+ ' FROM '+
@[email protected][email protected]+'>(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))+' '[email protected]+' FROM '[email protected][email protected]+
'ORDER BY '[email protected]+') t) ORDER BY '[email protected]
ELSE
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '[email protected]+' FROM '[email protected][email protected]+
@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 '[email protected][email protected]+'ORDER BY '[email protected]+') t) ORDER BY '[email protected]+''
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 ('[email protected]+') ' --原本没有条件而加上此条件
END
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '[email protected]+' 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();
}
} 上一篇： Oracle通用分页存储过程