C#和SQL数据浏览分页

C#和SQL数据浏览分页

如果需要考虑如时间的过滤、其他条件的加入，可以在SQL语句进行编辑，普通的网站，下面的数据浏览分页

就可以了。

aspx代码：

1@ Page language="c#" Codebehind="StockOrderFormBrower.aspx.cs" AutoEventWireup="false" Inherits="GSP.StockOrderFormBrower" 

 1<html>
 2<head>
 3<title>   
 4用C#和SQL结合进行数据浏览分页   
 5</title>
 6<link href="css/main.css" rel="stylesheet" type="text/css"/>
 7<meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>
 8<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR"/>
 9<meta content="C#" name="CODE_LANGUAGE"/>
10<meta content="JavaScript" name="vs_defaultClientScript"/>
11</head>
12<body ms_positioning="GridLayout">
13<form id="form1" method="post" runat="server">
14<table align="center" border="0" cellpadding="0" cellspacing="0" id="ShowData">

ShowData();

1</table>
2<table align="right">
3<tr>
4<td>

PageLoad_Count();

 1<input id="first" name="first" runat="server" type="button" value=" |&lt; "/> <!--第一页-->
 2<input id="prior" name="prior" runat="server" type="button" value=" &lt; "/> <!--上一页-->
 3<input id="next" name="next" runat="server" type="button" value=" &gt; "/> <!--下一页-->
 4<input id="last" name="last" runat="server" type="button" value=" &gt;| "/> <!--最后一页-->
 5</td>
 6</tr>
 7</table>
 8</form>
 9</body>
10</html>

aspx.cs代码：

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace ASWBLM
{
///

1<summary>   
2///   
3/// </summary>

public class UnionInfo : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlInputButton first;
protected System.Web.UI.HtmlControls.HtmlInputButton prior;
protected System.Web.UI.HtmlControls.HtmlInputButton last;
protected System.Web.UI.HtmlControls.HtmlInputButton next;

protected static int CurrentPage = 1;//初始化开始页面
protected static int RowCount = 0 ;//本页有多少条
private static bool IsPrior = false;//有“前一页”
private static bool IsNext = false;//有“下一页”
private static bool IsLast = false;//有“最后一页”
protected static int not_shown_records=0;//计算未显示记录数
private static string startID = "";//设置上一页开始ID
private static string endID = "";//设置下一页结束ID

private static int page_count = 10;//初始化页面记录数

private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
i f (!IsPostBack)
{
this.CountRecord().ToString();// 记录总数
this.Page_Count().ToString();//分页总数

Init_Brower();//初始化浏览
}
}

#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}

///

1<summary>   
2/// 设计器支持所需的方法 - 不要使用代码编辑器修改   
3/// 此方法的内容。   
4/// </summary>

private void InitializeComponent()
{
this.first.ServerClick += new System.EventHandler(this.first_ServerClick);
this.prior.ServerClick += new System.EventHandler(this.prior_ServerClick);
this.next.ServerClick += new System.EventHandler(this.next_ServerClick);
this.last.ServerClick += new System.EventHandler(this.last_ServerClick);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

///

1<summary>   
2/// 显示数据   
3/// </summary>

protected void ShowData()
{
DataSet ds = new DataSet(); //数据集
ASWBLM.Include.UnionInfo_Provider _uip = new ASWBLM.Include.UnionInfo_Provider();
string vSQL = "";
vSQL = GetSQLCommond(vSQL,startID,endID);

ds = _uip.ShowAllUnionInfo(vSQL); //取得全部数据的数据集

try
{
Response.Write("

 1<p align="center">");   
 2foreach(DataRow dr in ds.Tables["Table"].Rows)   
 3{   
 4Response.Write("<tr '#cccccc'\"="" =="" align="center" onmouseout="\&quot;this.bgColor='';\&quot;" onmouseover='\"this.bgColor'>");   
 5  
 6Response.Write("<td align='\"left\"' width='\"60%\"'><font color='\"#00309C\"'>");   
 7Response.Write("<a href='\"UnionInfo_Read.aspx?id="+dr["Id"].ToString()+"\"' target='\"_self\"'>");   
 8Response.Write(dr["Title"].ToString()); 
 9
10Response.Write("</a>");   
11Response.Write("</font></td>"); 
12
13Response.Write("<td align='\"right\"'>");   
14Response.Write("<font color='\"#999999\"'>");   
15Response.Write("( "+dr["SummaryDateTime"].ToString()+" )");   
16Response.Write(" ( 已阅读"+dr["ReadTimes"].ToString()+"次 )");   
17Response.Write("</font>");   
18Response.Write("</td>"); 
19
20Response.Write("</tr>");   
21}   
22Response.Write("</p>

");
startID = ds.Tables["Table"].Rows[0].ItemArray[0].ToString(); //通过数组，取第一个数据，得到开始号 “startID”
RowCount = ds.Tables["Table"].DefaultView.Count;//得到表的行数
endID = ds.Tables["Table"].Rows[RowCount-1].ItemArray[0].ToString (); //通过数组，取最后一个数据，得到结束号“endID”
}
catch(SqlException e)
{
Response.Write(e.Message);
}
}

///

1<summary>   
2/// 计算未显示记录数   
3/// </summary>

///

1<returns></returns>

protected void NotShownRecords()
{
not_shown_records = this.CountRecord() /查询总记录数/ - (CurrentPage /当前页/ - 1) * page_count /每页记录数/ ;
}

///

1<summary>   
2/// 进行输出信息   
3/// </summary>

protected void PageLoad_Count()
{
this.NotShownRecords();
Response.Write("总共"+this.CountRecord()+"条记录 ");
Response.Write("共有"+this.Page_Count()+"页 ");
Response.Write("第"+CurrentPage.ToString()+"页 ");
Response.Write("本页共有"+RowCount.ToString()+"条记录 ");
}

///

1<summary>   
2/// 获得总记录总数   
3/// </summary>

///

1<returns>时间条件范围内记录总数intCount</returns>

protected int CountRecord()
{
int intCount = 0;
SqlConnection SqlCon = new SqlConnection(Common._DBConnStr);
SqlCon.Open ();

//找到条件范围内的记录总数
string strCount = "select count(*) from UnionInfo";

//找到符合条件的第一个记录
//string strNum = "select top 1 Id from UnionInfo";

SqlCommand MyComm = new SqlCommand(strCount,SqlCon);
SqlDataReader dr = MyComm.ExecuteReader();//读取数据流
if(dr.Read())
{
intCount = Int32.Parse(dr[0].ToString());
}
else
{
intCount = 0;
}
dr.Close();
SqlCon.Close();
return intCount;
}

///

1<summary>   
2/// 总分页数   
3/// </summary>

///

1<returns>分页总数</returns>

protected int Page_Count()
{
int pageSum = 0;//分页总数
pageSum = this.CountRecord() / page_count; ///记录总数/分页的页数
if ((this.CountRecord() % page_count) > 0) pageSum++;
return pageSum;
}

///

1<summary>   
2/// 取得SQL语句   
3/// </summary>

///

1<param name="vCmd"/>

返回命令行
///

1<returns></returns>

private string GetSQLCommond(string vCommond,string startID,string endID)
{
this.NotShownRecords(); //执行未显示的行

vCommond = "SELECT TOP "+page_count+" {0},{1},{2},{3} FROM [UnionInfo]";

if(IsPrior) //判断“上一页”
{

}

if(IsNext) //判断“下一页”
{

}

if (IsLast) //判断“最后一页”
{

}

vCommond = string.Format(vCommond,"Id","Title","SummaryDateTime","ReadTimes"); //这个是数据表的字段
return vCommond;
}

///

1<summary>   
2/// 输入按钮的状态，进行是否可用   
3/// </summary>

///

1<param name="first"/>

第一页的状态
///

1<param name="prior"/>

上一页的状态
///

1<param name="next1"/>

下一页的状态
///

1<param name="last"/>

最后一页的状态
protected void SetButtonState(bool first_,bool prior_,bool next_,bool last_)
{
if (CurrentPage==1)//到“第一页”
{
first.Disabled = true;//第一页状态
prior.Disabled = true;//上一页状态
next.Disabled = false; //下一页状态
last.Disabled = false; //最后一页状态
}
else if (CurrentPage==this.Page_Count())//到“最后一页”
{
first.Disabled = false;//第一页状态
prior.Disabled = false;//上一页状态
next.Disabled = true; //下一页状态
last.Disabled = true; //最后一页状态
}
else
{
first.Disabled = first_;//第一页状态
prior.Disabled = prior_;//上一页状态
next.Disabled = next_; //下一页状态
last.Disabled = last_; //最后一页状态
}
}

///

1<summary>   
2/// 第一页按钮   
3/// </summary>

///

1<param name="sender"/>

///

1<param name="e"/>

private void first_ServerClick(object sender, System.EventArgs e)
{
CurrentPage = 1;
this.SetButtonState(true,true,false,false);
startID = "";
endID = "";
RowCount = '0';
IsLast = false;
IsPrior = false;
IsNext = false;
}

///

1<summary>   
2/// 上一页按钮   
3/// </summary>

///

1<param name="sender"/>

///

1<param name="e"/>

private void prior_ServerClick(object sender, System.EventArgs e)
{
if( CurrentPage == 1)//判断“当前页”是否为1
{
this.SetButtonState(true,true,false,false);
}
else
{
CurrentPage=CurrentPage - 1;//“当前页”自减
this.SetButtonState(false,false,false,false);
}
IsPrior = true;
IsNext = false;
IsLast = false;
}

///

1<summary>   
2/// 最后一页   
3/// </summary>

///

1<param name="sender"/>

///

1<param name="e"/>

private void last_ServerClick(object sender, System.EventArgs e)
{
CurrentPage=this.Page_Count(); //到最后一页
this.SetButtonState(false,false,true,true);
IsLast = true;
IsPrior = false;
IsNext = false;
}

///

1<summary>   
2/// 下一页   
3/// </summary>

///

1<param name="sender"/>

///

1<param name="e"/>

private void next_ServerClick(object sender, System.EventArgs e)
{
if(CurrentPage == this.Page_Count()) //判断“当前页”是否为“分页总数”
{
this.SetButtonState(false,false,true,true);
}
else
{
CurrentPage=CurrentPage + 1; //“当前页”自加
this.SetButtonState(false,false,false,false);
}
IsNext = true;
IsLast = false;
IsPrior = false;
}

///

1<summary>   
2/// 初始浏览按钮   
3/// </summary>

///

1<param name="sender"/>

///

1<param name="e"/>

private void Init_Brower()
{
CurrentPage = 1; //肯定是从第一页开始
if ((CurrentPage == 1) && (this.Page_Count() == 1))
{
first.Disabled = true; //第一页状态
prior.Disabled = true; //上一页状态
next.Disabled = true; //下一页状态
last.Disabled = true; //最后一页状态
}
else
{
first.Disabled = true; //第一页状态
prior.Disabled = true; //上一页状态
next.Disabled = false; //下一页状态
last.Disabled = false; //最后一页状态
}
startID = ""; //开始号
endID = ""; //结束号
IsLast = false;
IsPrior = false;
IsNext = false;
}
}
}

本文没有列出SQL语句，是希望我和我的好朋友们的劳动成果已经用于商业用途了，还有就是希望各位自己动手写写，应该没有问题的了

Published At
Categories with Web编程
Tagged with
comments powered by Disqus