其实原理也很简单,存储过程里面主要是根据传入的参数来取总记录数,和需要查询的数据,而不是一次把全部数据都取出来,提高了执行速度。
存储过程代码如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/****
使用帮助
首先查询表总行数,再查询分页数据
查询行数传入参数:@doCount,@tblName
查询分页传入参数:@tblName,@PageSize,@PageIndex,@fldName
*以上不带查询条件的查询
带条件的加参数:@strWhere
*分页查询可以使用排序
参数:@OrderType
****/
ALTER PROCEDURE [dbo].[Sp_Conn_Sort]
(
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 40, -- 页尺寸
@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 + '' + @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
end
exec (@strSQL)
下面是后台代码,这个部分写出了怎么样调用存储过程,以及构建翻页按钮,在写法上有一些乱,可以参考一下算法。
int ToatalCountRecord;//总记录数
int PageItem = 10;//每页显示的条数
int CurrentPage = 1;//当前页数
protected void Page_Load(object sender, EventArgs e)
{
if (!this.Page.IsPostBack)
{
if (Request.QueryString["page"] != null)
{
if (!Int32.TryParse(Request.QueryString["page"].ToString(), out CurrentPage))
{
Response.Write("请输入分页参数!");
Response.End();
return;
}
}
this.BuidGrid();
}
}
private void BuidGrid()
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["constr"]);
con.Open();
SqlCommand cmd = new SqlCommand("Sp_Conn_Sort", con);
SqlParameter sp5 = new SqlParameter("@doCount", 1);
SqlParameter sp6 = new SqlParameter("@tblName", "num_s");
cmd.Parameters.Add(sp5);
cmd.Parameters.Add(sp6);
cmd.CommandType = CommandType.StoredProcedure;
ToatalCountRecord = Convert.ToInt32(cmd.ExecuteScalar());
SqlDataAdapter sda = new SqlDataAdapter("Sp_Conn_Sort", con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter sp1 = new SqlParameter("@tblName", "num_s");
SqlParameter sp2 = new SqlParameter("@PageSize", PageItem);
SqlParameter sp3 = new SqlParameter("@PageIndex", CurrentPage);
SqlParameter sp4 = new SqlParameter("@fldName", "id");
sda.SelectCommand.Parameters.Add(sp1);
sda.SelectCommand.Parameters.Add(sp2);
sda.SelectCommand.Parameters.Add(sp3);
sda.SelectCommand.Parameters.Add(sp4);
DataSet ds = new DataSet();
sda.Fill(ds);
int s = ds.Tables[0].Rows.Count;
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
BuildPages();
}
private void BuildPages()
{
int LeftStep = 3;//左偏移量
int RightStep = 2;//右偏移量
int LeftNum = 0;//左界限
int RightNum = 0;//右界限
string PageUrl = Request.FilePath;
int PageCount = (int)Math.Ceiling((double)(ToatalCountRecord) / PageItem);
if (CurrentPage < 5)
{
LeftNum = 1;
RightStep = 6 - CurrentPage;
}
else
{
if (PageCount - CurrentPage < 2)
{
RightNum = PageCount;
LeftStep = 5 - (PageCount - CurrentPage);
}
LeftNum = CurrentPage - LeftStep;
}
if (PageCount - CurrentPage > 1)
RightNum = CurrentPage + RightStep;
//上面代码有点混乱,重读需琢磨。
StringBuilder stringbuilder = new StringBuilder();
if (CurrentPage > 1)
{
string OutPut = " <a href='" + PageUrl + "?page=" + (CurrentPage - 1) + "'>" + "上一页" + " </a>";
stringbuilder.Append(OutPut);
}
for (int i = LeftNum; i <= RightNum; i++)
{
if (i == CurrentPage)
{
string OutPut = " <font color=red>" + " " + "[" + i.ToString() + "]" + "" + " </font>";
stringbuilder.Append(OutPut);
}
else
{
string OutPut = " <a href='" + PageUrl + "?page=" + i.ToString() + "'>" + " " + "[" + i.ToString() + "]" + " " + " </a>";
stringbuilder.Append(OutPut);
}
}
if (PageCount - CurrentPage > 2)
{
string OutPut = " <a href='" + PageUrl + "?page=" + PageCount + "'>..." + PageCount + "</a>";
stringbuilder.Append(OutPut);
}
if (CurrentPage < PageCount)
{
string OutPut = " <a href='" + PageUrl + "?page=" + (CurrentPage + 1) + "'>" + "下一页" + " </a>";
stringbuilder.Append(OutPut);
} this.PageInfo.InnerHtml = stringbuilder.ToString();
}
前台代码如下:
<div>
<div id="PageInfo" runat="server"></div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id"
Height="303px">
<Columns>
<asp:BoundField DataField="id" HeaderText="编号" />
<asp:BoundField DataField="num" HeaderText="号码" />
</Columns>
</asp:GridView>
</div>