1.建立用户控件Pager.ascx
1.1 html
<script language="javascript"> function callButtonEvent() { var keycode =window.event.keyCode; if(keycode==13) { if(check()==true) { event.cancelBubble=true; event.returnValue=false; document.getElementById('<%=btnGo.ClientID%>').click(); } } } function check() { var count = parseInt(document.getElementById('<%=lblTotal.ClientID%>').outerText); var txt = document.getElementById('<%=txtCurrentPage.ClientID%>').value; var cur = parseInt(txt); if ((cur | NaN) ==0) { alert('Input page must format as integer.'); event.cancelPostBack=true; return false; } if (cur > count || cur < 1) { alert('Input page no out of range.'); event.cancelPostBack=true; return false; } } </script> <TABLE ID="Table1" CELLSPACING="0" CELLPADDING="0" WIDTH="100%" BORDER="0"> <colgroup> <col width="400"> <col width="50"> <col width="50"> <col width="40"> <col width="20"> <col width="40"> <col width="40"> <col width="50"> <col width="70"> </colgroup> <TR align="right"> <td></td> <TD><asp:LinkButton id="btnFirstPage" runat="server" CommandArgument="First">第一页</asp:LinkButton></TD> <TD><asp:LinkButton id="btnPrevPage" runat="server" CommandArgument="Prev">上一页</asp:LinkButton></TD> <TD><ASP:TEXTBOX ID="txtCurrentPage" RUNAT="server" MAXLENGTH="3" Width="40">0</ASP:TEXTBOX></TD> <TD><ASP:LABEL ID="labOf" RUNAT="server">of</ASP:LABEL></TD> <TD><ASP:LABEL ID="lblTotal" RUNAT="server">0</ASP:LABEL></TD> <TD><ASP:BUTTON ID="btnGo" RUNAT="server" TEXT="转到" COMMANDARGUMENT="Go" ToolTip="转到"></ASP:BUTTON></TD> <TD><asp:LinkButton id="btnNextPage" runat="server" CommandArgument="Next">下一页</asp:LinkButton></TD> <TD><asp:LinkButton id="btnLastPage" runat="server" CommandArgument="Last">最后一页</asp:LinkButton></TD> </TR> </TABLE>
1.2 cs代码
public class Pager : System.Web.UI.UserControl { protected System.Web.UI.WebControls.Label lblTotal; protected System.Web.UI.WebControls.Label labOf; protected System.Web.UI.WebControls.TextBox txtCurrentPage; protected System.Web.UI.WebControls.Button btnGo; protected System.Web.UI.WebControls.LinkButton btnFirstPage; protected System.Web.UI.WebControls.LinkButton btnPrevPage; protected System.Web.UI.WebControls.LinkButton btnNextPage; protected System.Web.UI.WebControls.LinkButton btnLastPage; int size=10;//可以在web.config中配置 public event System.EventHandler NavigationClick; private void Page_Load(object sender, System.EventArgs e) { this.txtCurrentPage.Attributes.Add("onkeypress","callButtonEvent();"); this.btnGo.Attributes.Add("onclick","check();"); if(!this.IsPostBack) { SetStyle(); SetEnable(); } } Web Form Designer generated code btnGo_Click SetStyle SetEnable Property Property }
2.建立DataGridPage.aspx 3.copy如下html代码
<HTML> <HEAD> <title>DataGridPage</title> <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR"> <meta content="C#" name="CODE_LANGUAGE"> <meta content="JavaScript" name="vs_defaultClientScript"> <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"> </HEAD> <body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> <asp:datagrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 632px; POSITION: absolute; TOP: 40px" runat="server"></asp:datagrid><uc1:pager id="Pager1" runat="server"></uc1:pager><asp:datalist id="DataList1" style="Z-INDEX: 102; LEFT: 264px; POSITION: absolute; TOP: 40px" runat="server"> <ItemTemplate> <table> <tr> <td>用户ID:</td> <td><%# DataBinder.Eval(Container.DataItem, "UserID") %></td> <td>用户名:</td> <td><%# DataBinder.Eval(Container.DataItem, "UserName") %></td> </tr> </table> </ItemTemplate> </asp:datalist> <asp:repeater id="Repeater1" runat="server"> <HEADERTEMPLATE> <table cellpadding="0" cellspacing="0" border="0"> <tr> <td>用户ID</td> <td>用户名:</td> </tr> </HEADERTEMPLATE> <ITEMTEMPLATE> <tr> <td> <%# DataBinder.Eval(Container.DataItem, "UserID")%> </td> <td><%# DataBinder.Eval(Container.DataItem, "UserName") %></td> </tr> </ITEMTEMPLATE> <FOOTERTEMPLATE> </table> </FOOTERTEMPLATE> </asp:repeater></form> </body> </HTML>
4.拖入用户控件Pager.ascx 5.copy如下cs代码
public class DataGridPage : System.Web.UI.Page { protected System.Web.UI.WebControls.DataGrid DataGrid1; protected UserControl.Pager Pager1;//定义用户控件,根据用户控件所在目录做适当的调整 protected System.Web.UI.WebControls.DataList DataList1; protected System.Web.UI.WebControls.Repeater Repeater1; public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; private void Page_Load(object sender, System.EventArgs e) { if (!this.IsPostBack) { BindData(); } } Bind Data ExecSPDataSet GetList property Web Form Designer generated code private void Pager1_NavigationClick(object sender, EventArgs e) { BindData(); } }
6.测试表结构
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestGrid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TestGrid] GO CREATE TABLE [dbo].[TestGrid] ( [UserID] [int] NOT NULL , [UserName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [State] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Enabled] [bit] NULL ) ON [PRIMARY] GO
7.测试存储过程
create PROCEDURE tp_Fetch_List( @page_num INT, @row_in_page INT, @order_column VARCHAR(50), @row_total INT OUTPUT, @comb_condition VARCHAR(500) ) AS BEGIN SET NOCOUNT ON DECLARE @jcc_status INT, @sql NVARCHAR(4000), @row_ahead INT SET @jcc_status = 0 SET @row_ahead = (@page_num-1) * @row_in_page SET @sql='SELECT TOP '+ cast(@row_in_page as varchar(255)) + ' * FROM ( ' SET @sql = @sql + 'SELECT * FROM TestGrid ) as A where 1=1' IF LEN(@comb_condition)>0 SET @sql = @sql + ' AND (' + @comb_condition + ')' SET @sql = @sql + 'and UserID not in ( select UserID from (' SET @sql = @sql + 'SELECT TOP ' + cast(@row_ahead as varchar(255)) + ' * From (' SET @sql = @sql + 'SELECT * FROM TestGrid ) as A where 1=1' IF LEN(@comb_condition)>0 SET @sql = @sql + ' AND ( ' + @comb_condition + ' )' IF LEN(@order_column)>0 BEGIN SET @sql = @sql + ' ORDER BY ' + @order_column + ' ) AS B )' END ELSE BEGIN SET @sql = @sql + ' ) AS B )' END IF LEN(@order_column)>0 BEGIN SET @sql = @sql + ' ORDER BY ' + @order_column END print @sql EXEC (@sql) SET @sql= N'SELECT @row_total=COUNT(*) FROM (' SET @sql = @sql + 'SELECT * FROM TestGrid ) as A where 1=1' IF LEN(@comb_condition)>0 SET @sql = @sql + ' AND (' + @comb_condition + ')' print @sql EXEC sp_executesql @sql,N'@row_total INT OUT',@row_total OUT IF @@ERROR != 0 BEGIN SELECT @jcc_status = -98 END exit_bk: -- exit with MS SQL Server error IF @jcc_status = -98 BEGIN RAISERROR ('MS SQL Server error, please contact your system administrator.',16,1)WITH NOWAIT RETURN (@jcc_status) END -- normal exit RETURN (0) END GO -- declare @aa int -- exec tp_Fetch_List 1,10,'',@aa out ,'1=1' -- select @aa
8.源代码下载/Files/singlepine/DataGridPage.rar