------------------------------------
--用途:支持任意排序的分页存储过程
--说明:
------------------------------------
CREATE PROCEDURE [dbo].[UP_GetRecordByPageOrder]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 显示字段名
@OrderfldName varchar(255), -- 排序字段名
@StatfldName varchar(255), -- 统计字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = "" -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @OrderfldName +"] desc"
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @OrderfldName +"] asc"
end
set @strSQL = "select top " + str(@PageSize) + " " + @fldName + " from ["
+ @tblName + "] where [" + @OrderfldName + "]" + @strTmp + "(["
+ @OrderfldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @OrderfldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrder
if @strWhere != ""
set @strSQL = "select top " + str(@PageSize) + " " + @fldName + " from ["
+ @tblName + "] where [" + @OrderfldName + "]" + @strTmp + "(["
+ @OrderfldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @OrderfldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
if @PageIndex = 1
begin
set @strTmp = ""
if @strWhere != ""
set @strTmp = " where " + @strWhere
set @strSQL = "select top " + str(@PageSize) + " " + @fldName + " from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
end
if @IsReCount != 0
set @strSQL = @strSQL+" select count(1) as Total from [" + @tblName + "]"
if @strWhere!=""
set @strSQL = @strSQL+" where " + @strWhere
exec (@strSQL)
------------------------------------
--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = "" -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrder
if @strWhere != ""
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
if @PageIndex = 1
begin
set @strTmp =""
if @strWhere != ""
set @strTmp = " where " + @strWhere
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
end
if @IsReCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"+" where " + @strWhere
exec (@strSQL)
下面是存储过程(sqlserver2000下通过)
--最通用的分页存储过程
-- 获取指定页的数据
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = "*", -- 需要返回的列
@fldName varchar(255)="", -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = "" -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=""
set @strSQL = "select count(*) as Total from ["+ @tblName +"] where "+ @strWhere
else
set @strSQL = "select count(*) as Total from ["+ @tblName +"]"
end
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都
--是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by ["+ @fldName +"] desc"
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by ["+ @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere != ""
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName +"] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName +"] "+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName +"] where [" + @fldName + "]" +
from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from ["+ @tblName +"]" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ""
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName +"] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "]
from ["+ @tblName +"] where " + @strWhere + " " + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec ( @strSQL)
GO
下面是C#的代码
代码如下 using System.Data ;
using System.Data.SqlClient ;
using Microsoft.ApplicationBlocks.Data ;
using System.Web ;
using System.Web.UI ;
namespace RssLayer.PageHelper
{
/**////
/// 分页类PagerHelper 的摘要说明。
///
public class PagerHelper
{
private string connectionString;
public PagerHelper(string tblname,string sortname,bool docount,string connectionString)
{
this.tblName = tblname;
this.fldName = sortname ;
this.connectionString = connectionString ;
this.docount = docount;
}
public PagerHelper(string tblname,bool docount,
string strGetFields, string fldName,int pagesize,
int pageindex,bool ordertype,string strwhere,string connectionString
)
{
this.tblName = tblname ;
this.docount = docount ;
this.strGetFields = strGetFields ;
this.fldName = fldName;
this.pagesize = pagesize ;
this.pageindex = pageindex;
this.ordertype = ordertype ;
this.strwhere = strwhere ;
this.connectionString = connectionString ;
}
/**////
/// 得到记录集的构造函数
///
///
///
///
public PagerHelper(string tblname,string strwhere,string connectionString)
{
this.tblName = tblname;
this.strwhere = strwhere ;
this.docount = true;
this.connectionString = connectionString ;
}
private string tblName;
public string TblName
{
get{return tblName;}
set{tblName =value;}
}
private string strGetFields="*";
public string StrGetFields
{
get{return strGetFields ;}
set{strGetFields =value;}
}
private string fldName=string.Empty;
public string FldName
{
get{return fldName ;}
set{fldName =value;}
}
private int pagesize =10;
public int PageSize
{
get{return pagesize ;}
set{pagesize =value;}
}
private int pageindex =1;
public int PageIndex
{
get{return pageindex ;}
set{pageindex =value;}
}
private bool docount=false;
public bool DoCount
{
get{return docount ;}
set{docount =value;}
}
private bool ordertype=false;
public bool OrderType
{
get{return ordertype ;}
set{ordertype =value;}
}
private string strwhere=string.Empty ;
public string StrWhere
{
get{return strwhere ;}
set{strwhere =value;}
}
public IDataReader GetDataReader()
{
if(this.docount)
{
throw new ArgumentException("要返回记录集,DoCount属性一定为false");
}
// System.Web.HttpContext.Current.Response.Write(pageindex);
return SqlHelper.ExecuteReader(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}
public DataSet GetDataSet()
{
if(this.docount)
{
throw new ArgumentException("要返回记录集,DoCount属性一定为false");
}
return SqlHelper.ExecuteDataset(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}
public int GetCount()
{
if(!this.docount)
{
throw new ArgumentException("要返回总数统计,DoCount属性一定为true");
}
return (int)SqlHelper.ExecuteScalar(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}
}
}
支持向量机_支持任意排序的分页存储过程(asp.net/sqlserver)
http://m.bbyears.com/zhufuduanxin/35257.html
推荐访问:支持5g的手机 支持nfc的手机 支持无线充电的手机 智齿 支持向量机原理 支持英语 支持的英文 支持carplay的车型