到网上搜分页存储过程一大把。但是有些需求的话，对于一般的存储过程不适用。经过一天多的摸索。终于写出了属于自己的分页存储过程（里面包含全文索引的查询哦） 希望对大家有一点点帮助！
效果如图
本人开发的环境是c#2008+sqlserver2008，将要查询的字段填充好全文索引后。便可以展开工作了。
用三层模式开发、 专门新建了一个Querry类，在接口层定义好方法：
/// <summary>
/// 安全规范的分页查询
/// </summary>
/// <param name="q">所需参数</param>
/// <returns>返回一个SStand的集合</returns>
IList<SStand> GetAllBySStand(QuerryContent q);
/// <summary>
/// 得到查询单总数
/// </summary>
/// <param name="q"></param>
/// <returns></returns>
int GetCount(QuerryContent q);
因为查询的页面有很多。并且查询的表也不相同，所以把分页存储过程做成了通用的（废话了）进入主题： 存储过程
第一次查询一个新的东西的时候，先得到这个查询集合的总数（这也是本存储过程不足的）
CreateProcedure Popular_PageListCount
@ContentOne nvarchar(20), --查询关键字
@ContentTwo nvarchar(20),
@ContentThree nvarchar(20),
@TempTypeID int, --所属分类
@TempTypeIDName nvarchar(50), --所属分类名 与上对应 两个是一起的
@fieldOne nvarchar(20), --要查询的字段 强规
@fieldTwo nvarchar(20), --一般规
@Table nvarchar(20), --要查询的表
@TempIDName nvarchar(20) --要排序的ID
AS
declare @sql nvarchar(300)
declare @opreat nvarchar(20)
if @ContentTwo is null or @ContentTwo=''
set @opreat='or'
else
set @opreat='and'
set @sql='select count(*) from '+@Table+'
inner join
containstable('+@Table+',(['+@fieldOne+'],['+@fieldTwo+']),''("'+@ContentOne+'" '+@opreat+' "'+@ContentTwo+'" or "'+@ContentThree+'")'',50) as k
on '+@TempIDName+'=k.[key]
where '+@TempTypeIDName+'='+CONVERT(nvarchar,@TempTypeID)+''
exec sp_executeSQL @sql
正式分页存储过程
Create Procedure Popular_PageList
@ContentOne nvarchar(20), --查询关键字
@ContentTwo nvarchar(20),
@ContentThree nvarchar(20),
@CurrentCount int, ----当前的索引，假设每页5条数据，第二页的话，currencount为10
@num int, --每页的条数
@TempTypeID int, --所属分类
@TempTypeIDName nvarchar(50), --所属分类名 与上对应 两个是一起的
@fieldOne nvarchar(20), --要查询的字段 强规
@fieldTwo nvarchar(20), --一般规
@TempIDName nvarchar(20), --要排序的ID
@Table nvarchar(20) --要查询的表
AS
declare @Sql nvarchar(300)
declare @opreat nvarchar(20)
if @ContentTwo is null or @ContentTwo=''
set @opreat='or'
else
set @opreat='and'
set @Sql='with cte as(select top '+CONVERT(nvarchar,@CurrentCount)+' *,row_number() over(order by '+@TempIDName+' asc) as num from '+@Table+'
inner join containstable('+@Table+',(['+@fieldOne+'],['+@fieldTwo+']),''("'+@ContentOne+'") '+@opreat+' ("'+@ContentTwo+'")or("'+@ContentThree+'")'',50) as k
on '+@TempIDName+'=k.[key]
where '+@TempTypeIDName+'='+CONVERT(nvarchar,@TempTypeID)+'
order by '+@TempIDName+' asc)
select * from cte where num>'+CONVERT(nvarchar,@num)+''
exec sp_executeSQL @sql
页面前台：
View Code
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tr>
<td width="90px">规范名称：</td>
<td>
<asp:TextBox ID="txtSstand" runat="server" Width="249px"></asp:TextBox></td>
<td>
<asp:Button ID="btnQuerry" runat="server" Text="搜 索" Width="80px"
UseSubmitBehavior="False" onclick="btnQuerry_Click" /></td>
</tr>
<tr>
<td width="90px">安全规范：</td>
<td colspan="2">
<asp:DropDownList ID="ddlSStand" runat="server" Width="160px"
AutoPostBack="true"
style=" width:expression(this.offsetWidth<=100?'100px':'');">
</asp:DropDownList>
</td>
</tr><tr>
<td></td>
<td colspan="2" align="center"> 此项必选</td>
</tr>
</table>
</td>
<td>
<asp:Image ID="Image1" runat="server" Width="64px" Height="64px"
ImageUrl="~/image/green.jpg" ImageAlign="Middle" /> <asp:Image ID="Image2" runat="server" Width="64px" Height="64px"
ImageUrl="~/image/green.jpg" ImageAlign="Middle" /> <asp:Image
ID="Image3" runat="server" Width="64px" Height="64px"
ImageUrl="~/image/green.jpg" ImageAlign="Middle"/>
</td>
</tr>
<tr>
<td colspan="2">
<table width="100%">
<tr>
<td>
<asp:TextBox ID="txtContentOne" runat="server"></asp:TextBox>—
</td>
<td> <asp:TextBox ID="txtContentTwo" runat="server"></asp:TextBox>—</td>
<td>
<asp:TextBox ID="txtContentThree" runat="server"></asp:TextBox>—</td>
<td align="center" valign="top">
<asp:Button ID="btnContentQuerry" runat="server" Text="搜 索" Width="100px"
UseSubmitBehavior="False" onclick="btnContentQuerry_Click" />
</td>
</tr>
<tr>
<td colspan="4" align="center" style=" height:160px;" valign="top">
<asp:GridView ID="gdvContent" runat="server" AutoGenerateColumns="False"
DataKeyNames="SStandID" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="100%" AllowPaging="True" PageSize="5" valign="top"
onrowcommand="gdvContent_RowCommand">
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField DataField="SStandID" HeaderText="编号"
InsertVisible="False" ReadOnly="True" SortExpression="SStandID" />
<asp:BoundField DataField="SStandProID" HeaderText="SStandProID"
SortExpression="SStandProID" />
<asp:BoundField DataField="SStandTypeID" HeaderText="SStandTypeID"
SortExpression="SStandTypeID" />
<asp:BoundField DataField="SStandTchildID" HeaderText="SStandTchildID"
SortExpression="SStandTchildID" />
<asp:BoundField DataField="SStandTLchildID" HeaderText="SStandTLchildID"
SortExpression="SStandTLchildID" />
<asp:BoundField DataField="SStandFRContent" HeaderText="强制性规范"
SortExpression="SStandFRContent" />
<asp:BoundField DataField="SStandGRContent" HeaderText="一般性规范"
SortExpression="SStandGRContent" />
<asp:BoundField DataField="FRCorse" HeaderText="FRCorse"
SortExpression="FRCorse" />
<asp:BoundField DataField="GRCorse" HeaderText="GRCorse"
SortExpression="GRCorse" />
<asp:BoundField DataField="QuerryCount" HeaderText="QuerryCount" SortExpression="QuerryCount" />
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
CommandName="Y" Text="查看详情"
CommandArgument='<%#Eval("SStandID") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</td>
</tr>
<tr><td colspan="4" align="center">
<div>
<asp:Label ID="Label1" runat="server" Text="总条数："></asp:Label><asp:Label ID="lblCount"
runat="server" Text=""></asp:Label> 总页数：<asp:Label ID="CurrentCount" runat="server"
Text=""></asp:Label>/<asp:Label ID="CountPage" runat="server" Text=""></asp:Label>
<asp:LinkButton ID="FirstPage" runat="server" CommandArgument="FirstPage" oncommand="LastPage_Command">首 页</asp:LinkButton> <asp:LinkButton
ID="PrevePage" runat="server" CommandArgument="PrevePage" oncommand="LastPage_Command">上一页</asp:LinkButton> <asp:LinkButton
ID="NextPage" runat="server" CommandArgument="NextPage" oncommand="LastPage_Command">下一页</asp:LinkButton> <asp:LinkButton
ID="LastPage" runat="server" CommandArgument="LastPage"
oncommand="LastPage_Command">末 页</asp:LinkButton>
</div>
</td></tr>
</table>
页面后台
定义一个查询方法：
View Code
private void QuerryContent(int CurrentCount, int Num)
{
BQuerryContent b = new BQuerryContent();
QuerryContent q1 = new QuerryContent();
IList<SStand> item = new List<SStand>();
q1.ContentOne = txtContentOne.Text;
q1.ContentTwo = txtContentTwo.Text;
q1.ContentThree = txtContentThree.Text;
q1.CurrentCount = CurrentCount;
q1.Num = Num;
q1.TempTypeID = int.Parse(ddlSStand.SelectedValue);
q1.TempTypeIDName = "SStandTypeID";
q1.FieldOne = "SStandFRContent"; //要匹配的字段
q1.FieldTwo = "SStandGRContent";
q1.Table = "SStand"; //表名
q1.TempIDName = "SStandID";
item = b.GetAllBySStand(q1);
gdvContent.DataSource = GridViewSet(item);
gdvContent.DataBind();
}
//调用：
protected void LastPage_Command(object sender, CommandEventArgs e)
{
string Command = e.CommandArgument.ToString();
int Count = int.Parse(lblCount.Text);
if (Command == "FirstPage")
{
QuerryContent(5, 0);
CurrentCount.Text = "1";
if (int.Parse(CurrentCount.Text) == 1)
{
PrevePage.Enabled = false;
NextPage.Enabled = true;
LastPage.Enabled = true;
FirstPage.Enabled = false;
}
}
else if (Command == "PrevePage")
{
int CurrentCountP = (int.Parse(CurrentCount.Text) - 1) * 5;
int NumP = CurrentCountP - 5;
QuerryContent(CurrentCountP, NumP);
CurrentCount.Text = (int.Parse(CurrentCount.Text) - 1).ToString();
if (int.Parse(CurrentCount.Text) == 1)
{
PrevePage.Enabled = false;
FirstPage.Enabled = false;
LastPage.Enabled = true;
NextPage.Enabled = true;
}
else
{
PrevePage.Enabled = true;
NextPage.Enabled = true;
LastPage.Enabled = true;
FirstPage.Enabled = true;
}
}
else if (Command == "NextPage")
{
int CurrentCountN = (int.Parse(CurrentCount.Text) + 1) * 5;
int NumN = CurrentCountN - 5;
QuerryContent(CurrentCountN, NumN);
CurrentCount.Text = (int.Parse(CurrentCount.Text) + 1).ToString();
if (int.Parse(CurrentCount.Text) == int.Parse(CountPage.Text))
{
PrevePage.Enabled = true;
NextPage.Enabled = false;
LastPage.Enabled = false;
FirstPage.Enabled = true;
}
else
{
PrevePage.Enabled = true;
NextPage.Enabled = true;
LastPage.Enabled = true;
FirstPage.Enabled = true;
}
}
else if (Command == "LastPage")
{
CurrentCount.Text = CountPage.Text;
int CurrentCountL = int.Parse(CountPage.Text) * 5;
int Num = CurrentCountL - 5;
QuerryContent(CurrentCountL, Num);
PrevePage.Enabled = true;
NextPage.Enabled = false;
LastPage.Enabled = false;
FirstPage.Enabled = true;
}
}
因本人是菜鸟，所以里面定义的一些东西不够规范，在存储分页上还有不足，要先得到它的总数，意思就是要连接数据库2次。 这个地方可以改进！ 望高人看到，可以给一点指点。 在此特别鸣谢，爱瞎想的猪，和不是NB的人给了我在存储过程上的一点指示。 谢谢