ALTER PROCEDURE [dbo].[ProcDataPaging]
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fieldKey nvarchar(150), ----主表的主键
@fieldNameShow nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 1, ----每页显示的记录个数
@pageCurrentSize int = 10, ----要显示那一页的记录
@fieldOrder nvarchar(200) = null, ----排序字段列表或条件
@Order bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@fieldWhere nvarchar(1000) = null, ----查询条件,不需where,以And开始
@fieldDistinct bit = 0 , ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@strSql nvarchar(1000) = '' output -----最后返回的SQL语句
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾fieldKey的查询语句
Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则B
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
if @fieldDistinct = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@fieldKey+')'
end
if @Order=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @fieldWhere is null or @fieldWhere='' --没有设置显示条件
begin
set @sqlTmp = @fieldNameShow + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fieldNameShow + 'From ' + @tblName + ' where (1>0) ' + @fieldWhere
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @fieldWhere
set @strID = ' From ' + @tblName + ' where (1>0) ' + @fieldWhere
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
if @pageCurrentSize>@pageCount
set @pageCurrentSize=@pageCount
--
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @fieldWhere is null or @fieldWhere='' --没有设置显示条件
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fieldNameShow+' from '+@tblName
+' where '+@fieldKey+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@pageCurrentSize-1) as Varchar(20)) +' '+ @fieldKey +' from '+@tblName
+' order by '+ @fieldOrder +' '+ @strFSortType+')'
+' order by '+ @fieldOrder +' '+ @strFSortType
end
else --有查询条件
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fieldNameShow +' from '+@tblName
+' where '+@fieldKey+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@pageCurrentSize-1) as Varchar(20)) +' '+ @fieldKey +' from '+@tblName
+' Where (1>0) ' + @fieldWhere + ' order by '+ @fieldOrder +' '+ @strFSortType+')'
+' ' + @fieldWhere + ' order by '+ @fieldOrder +' '+ @strFSortType
end
------返回查询结果-----
set @strSql = @strTmp
exec sp_executesql @strTmp
--print @strTmp
二、asp.net利用aspnetpager组件实现分页的后台代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Wuqi.Webdiyer;
public partial class Paging_FenYe : System.Web.UI.Page
{
public static string connectionString = @"Server=;UID=sa;PWD=sa;DataBase=DataName";
private string procName = "ProcDataPaging";//存储过程名字
private string tblName = "hm_news"; //表名
private string fieldKey = "ID"; //主键字段
private string fieldNameShow = null; //显示需要字段
public int PageSize = 10; //每页显示的记录数
private int pageIndex = 1; //当前页的页码
private string fieldOrder = "InfoTitle"; //以逗号分隔的排序字段列表,可以指定在字段后面指定CreatedOn DESC/UserName ASC,用于指定排序顺序 --程序传参如：' SortA Asc,SortB Desc,SortC '
private bool Order = true; //排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记,且由Order来排序))
private string fieldWhere = ""; //查询条件,不需where,以And开始
private bool fieldDistinct = false; //是否添加查询字段的 DISTINCT 默认0不添加/1添加
public int PageCount; //查询结果分页后的总页数
public int Counts; //查询到的记录数
private string OutSelectSql; //最后返回的SQL语句
protected void Page_Load(object sender, EventArgs e)
{
BindDataForControl(GridView1, AspNetPager1);
}
public void BindDataForControl(GridView gridView, AspNetPager aspnetPager)
{
pageIndex = aspnetPager.CurrentPageIndex;
if (pageIndex <= 0) pageIndex = 1;
DataSet ds = ReturnPageList(procName, tblName, fieldKey, fieldNameShow, PageSize, pageIndex, fieldOrder, Order, fieldWhere, fieldDistinct, out PageCount, out Counts, out OutSelectSql);
gridView.DataSource = ds.Tables[0];
gridView.DataBind();
aspnetPager.PageSize = 10;
aspnetPager.RecordCount = Counts;
}
/// <summary>
/// 分页存储过程 返回DataSet数据集
/// </summary>
/// <param name="procName"></param>
/// <param name="tblName">表名</param>
/// <param name="fieldKey">主键字段名</param>
/// <param name="fieldNameShow">显示需要字段</param>
/// <param name="pageSize">每页显示的记录数</param>
/// <param name="pageIndex">当前页的页码</param>
/// <param name="fieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定CreatedOn DESC/UserName ASC,用于指定排序顺序 --程序传参如：' SortA Asc,SortB Desc,SortC '</param>
/// <param name="Order">排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记,且由Order来排序))</param>
/// <param name="fieldWhere">查询条件,不需where,以And开始</param>
/// <param name="Distinct">是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
/// <param name="pageCount">查询结果分页后的总页数</param>
/// <param name="Counts">查询到的记录数</param>
/// <param name="strSql">最后返回的SQL语句</param>
/// <returns>返回DataSet数据集</returns>
public static DataSet ReturnPageList(string procName, string tblName, string fieldKey, string fieldNameShow, int pageSize, int pageIndex,
string fieldOrder, bool Order, string fieldWhere, bool Distinct, out int pageCount, out int Counts, out string strSql)
{
SqlParameter[] commandParameters = new SqlParameter[]
{
new SqlParameter("@tblName", SqlDbType.NVarChar),
new SqlParameter("@fieldKey", SqlDbType.NVarChar),
new SqlParameter("@fieldNameShow", SqlDbType.NVarChar),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageCurrentSize", SqlDbType.Int),
new SqlParameter("@fieldOrder", SqlDbType.NVarChar),
new SqlParameter("@Order", SqlDbType.Bit),
new SqlParameter("@fieldWhere", SqlDbType.NVarChar,1000),
new SqlParameter("@fieldDistinct", SqlDbType.Bit),
new SqlParameter("@pageCount", SqlDbType.Int,4),
new SqlParameter("@Counts", SqlDbType.Int,4),
new SqlParameter("@strSql", SqlDbType.NVarChar,1000)
};
commandParameters[0].Value = tblName;
commandParameters[1].Value = fieldKey;
commandParameters[2].Value = (fieldNameShow == null) ? "*" : fieldNameShow;
commandParameters[3].Value = (pageSize == 0) ? 10 : pageSize;
commandParameters[4].Value = pageIndex;
commandParameters[5].Value = fieldOrder;
commandParameters[6].Value = Order;
commandParameters[7].Value = (fieldWhere == null) ? "" : fieldWhere;
commandParameters[8].Value = Distinct;
commandParameters[9].Direction = ParameterDirection.Output;
commandParameters[10].Direction = ParameterDirection.Output;
commandParameters[11].Direction = ParameterDirection.Output;
DataSet set1 = RunProcedure(procName, commandParameters, tblName);
pageCount = (int)commandParameters[9].Value;
Counts = (int)commandParameters[10].Value;
strSql = commandParameters[11].Value.ToString();
return set1;
}
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
this.AspNetPager1.CurrentPageIndex = e.NewPageIndex;
BindDataForControl(GridView1, AspNetPager1);
}
}