HTML code
<% @ Page Language = " C# " %> <% @ Import Namespace = " System.Data.SqlClient " %> <% @ Import Namespace = " System.Data " %> <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" > < script runat ="server" > private string ConnectionString = @ " server=.;uid=sa;pwd=000000;database=NorthWind " ; private System.Text.StringBuilder output = new StringBuilder(); private DataSet BindPageData( int CurrentPage, int PageSize, string SqlCommandText) { using (SqlConnection conn = new SqlConnection(ConnectionString)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = " SYS_PageDate " ; cmd.Parameters.AddWithValue( " @sqlstr " , SqlCommandText).SqlDbType = SqlDbType.NVarChar; cmd.Parameters.AddWithValue( " @currentpage " , CurrentPage).SqlDbType = SqlDbType.Int; cmd.Parameters.AddWithValue( " @pagesize " , PageSize).SqlDbType = SqlDbType.Int; DataSet ds = new DataSet(); using (SqlDataAdapter dapter = new SqlDataAdapter(cmd)) { dapter.Fill(ds); } return ds; } } void SelectPager( int PageIndex, int PageCount, int TotalRecord) { int previous = 1 ; int next = 0 ; int step = 5 ; int start, end; if (PageIndex < PageCount) { next = PageIndex + 1 ; } else { PageIndex = PageCount; previous = PageCount - 1 ; next = PageCount; } start = 1 ; end = PageIndex + step; if (PageIndex > step) { start = PageIndex - step; } if (end > PageCount) { end = PageCount; } else { end = end - 1 ; } output.Append( " <div class=/"selectpage/"> " ); if (PageIndex > start) // 如果不是首页 { previous = PageIndex - 1 ; output.AppendFormat( " <a href=/"{0}?page=1/">首页</a> " , Request.FilePath); output.AppendFormat( " <a href=/"{0}?page={1}/">上一页</a> " , Request.FilePath, previous); } for ( int i = start; i <= end; i ++ ) { if (PageIndex == i) { output.AppendFormat( " <span class=/"selectpageindex/"> {0} </span> " , i); } else { output.AppendFormat( " <a href=/"{0}?page={1}/" class=/"pageindex/"> {1} </a> " , Request.FilePath, i); } } if (PageIndex < end) { output.AppendFormat( " <a href=/"{0}?page={1}/">下一页</a> " , Request.FilePath, next); output.AppendFormat( " <a href=/"{0}?page={1}/">尾页</a> " , Request.FilePath, PageCount); } output.AppendFormat( " 当前第<font color=red>{0}</font>页 共<font color=red>{1}</font>页 <font color=red>{2}</font>条数据 " , PageIndex, PageCount, TotalRecord); output.Append( " </div> " ); } string ShowData( int PageIndex) { string Sql = @ " select * from Customers " ; DataSet ds = BindPageData(PageIndex, 15 , Sql); DataTable dt; if (ds.Tables.Count == 0 ) { dt = ds.Tables[ 0 ]; } else { dt = ds.Tables[ 2 ]; if (dt.Rows.Count == 0 && PageIndex > 1 ) // 如果输入的当前页数超出总页数 { ds = BindPageData(Convert.ToInt32(ds.Tables[ 1 ].Rows[ 0 ][ 0 ]), 15 , Sql); dt = ds.Tables[ 2 ]; } } dt.Columns.Remove( " ROWSTAT " ); SelectPager(PageIndex, Convert.ToInt32(ds.Tables[ 1 ].Rows[ 0 ][ 0 ]), Convert.ToInt32(ds.Tables[ 1 ].Rows[ 0 ][ 1 ])); output.Append( " <table class=/"tb/" border=/"1/" cellpadding=/"0/" cellspacing=/"0/" > " ); output.Append( " <tr class=/"tb_Header/"> " ); foreach (DataColumn column in dt.Columns) { output.Append( " <td> " + column.ColumnName + " </td> " ); } output.Append( " </tr> " ); foreach (DataRow row in dt.Rows) { output.Append( " <tr class=/"tb_Content/"> " ); for ( int i = 0 ; i < dt.Columns.Count; i ++ ) { output.Append( " <td> " ); output.Append(row[dt.Columns[i].ColumnName]); output.Append( " </td> " ); } output.Append( " </tr> " ); } output.Append( " </table> " ); return output.ToString(); } protected override void OnUnload(EventArgs e) { this .output = null ; } </ script > < html xmlns ="http://www.w3.org/1999/xhtml" > < head runat ="server" > < title > 未命名頁面 </ title > < style type ="text/css" > a:link { font-size : 9pt ; color : #3E94F1 ; text-decoration : none ; } a:visited { color : #000fff ; text-decoration : none ; } a:hover { color : red ; text-decoration : underline } a:active { text-decoration : none } .selectpage { font-size : 9pt ; color : #3E94F1 ; width : 100% ; text-align : center } .selectpageindex { color : #fff ; background-color : #3E94F1 ; padding : 1px ; width : 20px ; margin-right : 5px } .pageindex { border : solid 1px #ccc ; padding : 1px ; margin-right : 7px } .tb { width : 100% ; border-collapse : collapse } .tb_Header { font-size : 12px ; font-weight : bold ; color : #3E94F1 ; texta-align : center } .tb_Content { font-size : 9pt ; color : #3E9345 } body { margin : 0px } ul { margin : 0px } ul li { margin : 0px ; display : inline ; } </ style > </ head > < body > < form id ="form1" runat ="server" > <% = ShowData(Request.QueryString[ " page " ] == null ? 1 : Convert.ToInt32(Request.QueryString[ " page " ])) %> </ form > </ body > </ html >
--SQL code
CREATE PROCEDURE SYS_PageDate
(
@sqlstr nvarchar(4000),
@currentpage int, --頁次
@pagesize int --行數
)
AS
set nocount on
DECLARE
@P1 int, --P1是游標的id
@rowcount int
SET @sqlstr=replace(replace(replace(replace(replace(replace(replace(replace(@sqlstr,'exec ',''),'insert ',''),'delete ',''),'update ',''),'drop ',''),'alter ',''),'chr(',''),'mid(','')
exec sp_cursoropen @P1 output ,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
-- 頁數 行數
SELECT ceiling(1.0*@rowcount/@pagesize) AS PageCount,@rowcount AS RowCounts --as ?行?,@currentpage as
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
GO
875
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
