.NET 使用存储过程分页
+展开
-SQL
CREATE PROCEDURE PageCut
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' ,-- 查询条件 (注意: 不要加 where)
@Counts int out -- 返回记录总数, 非 0 值则返回 这里作运行后返回的记录总数分页用
AS
set nocount on
declare @strSQL varchar(5000) -- 主语句
declare @CountSQL nVarchar(4000)
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
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 + ']' + @strTmp + '(['+ @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
if @strWhere != ''
set @CountSQL='select @Counts=count(*) from [' + @tblName + '] where ' + @strWhere +''
else
set @CountSQL='select @Counts=count(*) from [' + @tblName + '] '
exec sp_executesql @CountSQL, N'@Counts int out ',@Counts out
exec ( @strSQL)
set nocount off
GO
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' ,-- 查询条件 (注意: 不要加 where)
@Counts int out -- 返回记录总数, 非 0 值则返回 这里作运行后返回的记录总数分页用
AS
set nocount on
declare @strSQL varchar(5000) -- 主语句
declare @CountSQL nVarchar(4000)
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
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 + ']' + @strTmp + '(['+ @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
if @strWhere != ''
set @CountSQL='select @Counts=count(*) from [' + @tblName + '] where ' + @strWhere +''
else
set @CountSQL='select @Counts=count(*) from [' + @tblName + '] '
exec sp_executesql @CountSQL, N'@Counts int out ',@Counts out
exec ( @strSQL)
set nocount off
GO
++++++++++++++++++++++++++++++++++
后台调用
+展开
-C#
protected void LinkButton_Click(object sender, EventArgs e)
{
Common.Basic.Client Basic = new Common.Basic.Client();
int Page_Index ;
if (Request.QueryString["PageIndex"] != null)
{
if (Basic.IsNumeric(Request.QueryString["PageIndex"].ToString()))
{
Page_Index = Convert.ToInt16(Request.QueryString["PageIndex"].ToString());
}
else
{
Page_Index = 1;
}
}
else
{
Page_Index = 1;
}
string arg = ((LinkButton)sender).CommandArgument.ToString();
switch (arg)
{
case "Up":
if (Page_Index > 1) Response.Redirect("PageCut.aspx?PageIndex=" + (Page_Index - 1));
break;
case "Down":
Response.Redirect("PageCut.aspx?PageIndex=" +(Page_Index + 1));
break;
}
DataBind();
}
protected void DataBind()
{
Common.Basic.Client Basic = new Common.Basic.Client();
int Page_Index ;
if (Request.QueryString["PageIndex"]!=null)
{
if (Basic.IsNumeric(Request.QueryString["PageIndex"].ToString()))
{
Page_Index = Convert.ToInt16(Request.QueryString["PageIndex"].ToString());
if (Page_Index < 1) Page_Index = 1;
}
else
{ Page_Index = 1; }
}
else
{
Page_Index = 1;
}
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(Common.SQLserver.SqlHelper.Con))
{
SqlDataAdapter MyCommand = new SqlDataAdapter("PageCut", conn);
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tblName = new SqlParameter("@tblName", SqlDbType.VarChar);
tblName.Value = "Hyx_Info";//查询的表名
MyCommand.SelectCommand.Parameters.Add(tblName);
SqlParameter strGetFields = new SqlParameter("@strGetFields", SqlDbType.VarChar);
strGetFields.Value = "Title";//字段
MyCommand.SelectCommand.Parameters.Add(strGetFields);
SqlParameter fldName = new SqlParameter("@fldName", SqlDbType.VarChar);
fldName.Value = "id";//排序字段
MyCommand.SelectCommand.Parameters.Add(fldName);
SqlParameter PageSize = new SqlParameter("@PageSize", SqlDbType.Int);
PageSize.Value = 2;//每页大小
MyCommand.SelectCommand.Parameters.Add(PageSize);
SqlParameter PageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
PageIndex.Value = Page_Index;//当前页
MyCommand.SelectCommand.Parameters.Add(PageIndex);
SqlParameter OrderType = new SqlParameter("@OrderType", SqlDbType.Int);
OrderType.Value = 1;//desc?asc?
MyCommand.SelectCommand.Parameters.Add(OrderType);
SqlParameter strWhere = new SqlParameter("@strWhere", SqlDbType.VarChar);
strWhere.Value = "";//条件
MyCommand.SelectCommand.Parameters.Add(strWhere);
SqlParameter Counts = new SqlParameter("@Counts", SqlDbType.Int);
Counts.Value = 1;//总记录数
Counts.Direction = ParameterDirection.Output;//因为这里要返回,所以要设置一下,返回计算过后的结果
MyCommand.SelectCommand.Parameters.Add(Counts);
MyCommand.Fill(ds);
int count = (int)Counts.Value;
Response.Write(count);
}
list.DataSource = ds.Tables[0];
list.DataBind();
{
Common.Basic.Client Basic = new Common.Basic.Client();
int Page_Index ;
if (Request.QueryString["PageIndex"] != null)
{
if (Basic.IsNumeric(Request.QueryString["PageIndex"].ToString()))
{
Page_Index = Convert.ToInt16(Request.QueryString["PageIndex"].ToString());
}
else
{
Page_Index = 1;
}
}
else
{
Page_Index = 1;
}
string arg = ((LinkButton)sender).CommandArgument.ToString();
switch (arg)
{
case "Up":
if (Page_Index > 1) Response.Redirect("PageCut.aspx?PageIndex=" + (Page_Index - 1));
break;
case "Down":
Response.Redirect("PageCut.aspx?PageIndex=" +(Page_Index + 1));
break;
}
DataBind();
}
protected void DataBind()
{
Common.Basic.Client Basic = new Common.Basic.Client();
int Page_Index ;
if (Request.QueryString["PageIndex"]!=null)
{
if (Basic.IsNumeric(Request.QueryString["PageIndex"].ToString()))
{
Page_Index = Convert.ToInt16(Request.QueryString["PageIndex"].ToString());
if (Page_Index < 1) Page_Index = 1;
}
else
{ Page_Index = 1; }
}
else
{
Page_Index = 1;
}
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(Common.SQLserver.SqlHelper.Con))
{
SqlDataAdapter MyCommand = new SqlDataAdapter("PageCut", conn);
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tblName = new SqlParameter("@tblName", SqlDbType.VarChar);
tblName.Value = "Hyx_Info";//查询的表名
MyCommand.SelectCommand.Parameters.Add(tblName);
SqlParameter strGetFields = new SqlParameter("@strGetFields", SqlDbType.VarChar);
strGetFields.Value = "Title";//字段
MyCommand.SelectCommand.Parameters.Add(strGetFields);
SqlParameter fldName = new SqlParameter("@fldName", SqlDbType.VarChar);
fldName.Value = "id";//排序字段
MyCommand.SelectCommand.Parameters.Add(fldName);
SqlParameter PageSize = new SqlParameter("@PageSize", SqlDbType.Int);
PageSize.Value = 2;//每页大小
MyCommand.SelectCommand.Parameters.Add(PageSize);
SqlParameter PageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
PageIndex.Value = Page_Index;//当前页
MyCommand.SelectCommand.Parameters.Add(PageIndex);
SqlParameter OrderType = new SqlParameter("@OrderType", SqlDbType.Int);
OrderType.Value = 1;//desc?asc?
MyCommand.SelectCommand.Parameters.Add(OrderType);
SqlParameter strWhere = new SqlParameter("@strWhere", SqlDbType.VarChar);
strWhere.Value = "";//条件
MyCommand.SelectCommand.Parameters.Add(strWhere);
SqlParameter Counts = new SqlParameter("@Counts", SqlDbType.Int);
Counts.Value = 1;//总记录数
Counts.Direction = ParameterDirection.Output;//因为这里要返回,所以要设置一下,返回计算过后的结果
MyCommand.SelectCommand.Parameters.Add(Counts);
MyCommand.Fill(ds);
int count = (int)Counts.Value;
Response.Write(count);
}
list.DataSource = ds.Tables[0];
list.DataBind();
http://hi.baidu.com/isbx/blog/item/51a4d00968ba8d8ad0581b0d.html
加支付宝好友偷能量挖...