此例子使用的是NorthWind数据库,里面用了分页存储过程,具体实现根据你设置的 DataGrid的每页的大小,每次从数据库中取出相对的记录,具体效果如图。
下面还把常用的几种删除记录的方式也写了一下(1.根据选中的CheckBox,一次删除多条记录,2.属性生成器里添加的删除按钮列删除,3.在模板列中放入Button按钮,每次删除之前都会有提示框,代码如下)
说明:由于这个程序我用到了数据访问层,所以数据库访问层的代码没有放上来,自己可以写一下,用到地方下面的代码会有说明
关于删除的方法,由于NorthWind表是SQLSERVER自带的,所以用了一个方法代替一下,并没有真正的删除记录,代码我注释掉了,不过逻辑是一样的。
关于页面的跳转,我用了DropDownList控件和TextBox控件,用DropDownList控件看的清楚,使用方便,但是一旦记录很多,页数增加,那就要每次都清除,每次加载一次,可能会影响效率,所以我用了两种方法,供其选择。
希望这个例子对大家有所帮助,也希望能够多提意见!
以下为存储过程,一个是分页存储过程,一个是得到总记录的存储过程
分页存储过程:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE Test_GetOrders
(@PAGESIZE INT, --页面大小,如每页存储20条记录
@PAGEINDEX INT --当前页码
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @INDEXTABLE TABLE(ID INT IDENTITY(1,1),NID INT) --定义表变量
DECLARE @PAGELOWERBOUND INT --定义此页的底码
DECLARE @PAGEUPPERBOUND INT --定义此页的顶码
SET @PAGELOWERBOUND=(@PAGEINDEX-1)*@PAGESIZE
SET @PAGEUPPERBOUND=@PAGELOWERBOUND+@PAGESIZE
SET ROWCOUNT @PAGEUPPERBOUND
INSERT INTO @INDEXTABLE(NID) SELECT OrderID FROM Orders ORDER BY OrderID DESC
SELECT ORDERID,CUSTOMERID,CONVERT(CHAR(10),ORDERDATE,120) AS ORDERDATE,
SHIPCITY,SHIPCOUNTRY,ISNULL(SHIPREGION,'无') AS SHIPREGION
FROM ORDERS A
INNER JOIN @INDEXTABLE T
ON A.ORDERID=T.NID
WHERE T.ID>@PAGELOWERBOUND AND T.ID<=@PAGEUPPERBOUND
ORDER BY T.ID
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
得到总记录的存储过程:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC GetOrderCount
AS
--得到ORDERS表的总记录数
SELECT COUNT(*) FROM ORDERS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
前台的代码:
<%@ Page language='c#' Codebehind='OrdersPage.aspx.cs' AutoEventWireup='false' Inherits='NetTest.OrdersPage' %>
<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.0 Transitional//EN' >
<HTML>
<HEAD>
<title>NorthWind数据库Orders表的分页例子</title>
<meta name='GENERATOR' Content='Microsoft Visual Studio .NET 7.1'>
<meta name='CODE_LANGUAGE' Content='C#'>
<meta name='vs_defaultClientScript' content='JavaScript'>
<link href='Css/BasicLayout.css' rel='stylesheet' type='text/css'>
<meta name='vs_targetSchema' content='http://schemas.microsoft.com/intellisense/ie5'>
</HEAD>
<body MS_POSITIONING='GridLayout'>
<script language='javascript'>
function SelectAll()
{
var len=document.Form1.elements.length;
var i;
for (i=0;i<len;i++)
{
if (document.Form1.elements[i].type=='checkbox')
{
document.Form1.elements[i].checked=true;
}
}
}
function UnSelectAll()
{
var len=document.Form1.elements.length;
var i;
for (i=0;i<len;i++)
{
if (document.Form1.elements[i].type=='checkbox')
{
document.Form1.elements[i].checked=false;
}
}
}
</script>
<form id='Form1' method='post' runat='server'>
<table align='center' border='0' width='60%'>
<tr>
<td align='right'>
<asp:RegularExpressionValidator id='RegularExpressionValidator1' runat='server' ErrorMessage='页数只能为数字' Display='None'
ControlToValidate='txtPage' ValidationExpression='/d{0,10}'></asp:RegularExpressionValidator>
<asp:ValidationSummary id='ValidationSummary1' runat='server' ShowMessageBox='True' ShowSummary='False'></asp:ValidationSummary></td>
</tr>
<TR>
<TD align='center'><font style='FONT-SIZE: 10pt'>共</font>
<asp:label id='lbTotalPage' runat='server' Css<font style='FONT-SIZE: 10pt'>页/共
<asp:label id='lbTotalCount' runat='server' Css记录 当前</font>
<asp:label id='lbCurrentPage' runat='server' Css<font style='FONT-SIZE: 10pt'>页</font>
<font style='FONT-SIZE: 10pt'>跳转到</font>
<asp:dropdownlist id='ddlPage' runat='server' AutoPostBack='True' Width='65px'></asp:dropdownlist>
<font style='FONT-SIZE: 10pt'>页</font>
<asp:TextBox id='txtPage' runat='server' Width='43px'></asp:TextBox>
<asp:Button id='btnGo' runat='server' CssWidth='34px' Text='GO'></asp:Button><FONT size='2'>
</FONT>
</TD>
</TR>
<tr>
<td align='right'><INPUT type='button' value='全部选择' οnclick='SelectAll()'><FONT face='宋体'>
</FONT><INPUT type='button' value='全部取消' οnclick='UnSelectAll()'><FONT face='宋体'>
</FONT>
<asp:Button id='btnDelete' runat='server' Text='删除'></asp:Button><FONT face='宋体'>
</FONT>
</td>
</tr>
<tr>
<td>
<asp:DataGrid id='dbOrders' runat='server' AutoGenerateColumns='False' Width='100%' BorderStyle='None'
BorderColor='#93BEE2' PageSize='20' AllowCustomPaging='True' AllowPaging='True' DataKeyField='OrderID'>
<ItemStyle Font-Size='X-Small' HorizontalAlign='Center' Height='25px' BackColor='#CCFFFF'></ItemStyle>
<HeaderStyle Font-Size='X-Small' Font-Bold='True' HorizontalAlign='Center' Height='25px' BackColor='#6699FF'></HeaderStyle>
<Columns>
<asp:TemplateColumn>
<ItemTemplate>
<FONT face='宋体'>
<asp:CheckBox id='cbFlag' runat='server'></asp:CheckBox></FONT>
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField='OrderID' HeaderText='订单编号'></asp:BoundColumn>
<asp:BoundColumn DataField='CustomerID' HeaderText='客户编号'></asp:BoundColumn>
<asp:BoundColumn DataField='OrderDate' HeaderText='订单日期'></asp:BoundColumn>
<asp:BoundColumn DataField='ShipCity' HeaderText='运输城市'></asp:BoundColumn>
<asp:BoundColumn DataField='ShipCountry' HeaderText='运输国家'></asp:BoundColumn>
<asp:BoundColumn DataField='ShipRegion' HeaderText='运输区域'></asp:BoundColumn>
<asp:ButtonColumn Text='删除' CommandName='Delete'></asp:ButtonColumn>
<asp:TemplateColumn>
<ItemTemplate>
<FONT face='宋体'>
<asp:Button id='btnADelete' runat='server' CssCommandName='Delete' Text='删除'></asp:Button></FONT>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<PagerStyle Visible='False'></PagerStyle>
</asp:DataGrid>
</td>
</tr>
<tr>
<TD align='center' height='30'><B><asp:linkbutton id='FirstPage' runat='server' CssText='[First Page]' CommandName='First'
OnCommand='NavigationLink_Click'>第一页</asp:linkbutton>
<asp:linkbutton id='PreviousPage' runat='server' CssText='[Previous Page]' CommandName='Prev'
OnCommand='NavigationLink_Click'>上一页</asp:linkbutton>
<asp:linkbutton id='NextPage' runat='server' CssText='[Next Page]' CommandName='Next'
OnCommand='NavigationLink_Click'>下一页</asp:linkbutton>
<asp:linkbutton id='LastPage' runat='server' CssText='[Last Page]' CommandName='Last'
OnCommand='NavigationLink_Click'>末一页</asp:linkbutton></B>
</TD>
</tr>
</table>
</form>
</body>
</HTML>
后台代码:
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 DataBaseClass;
using System.Data.SqlClient;
namespace NetTest
{
/// <summary>
/// OrdersPage 的摘要说明。
/// </summary>
public class OrdersPage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label lbTotalPage;
protected System.Web.UI.WebControls.Label lbTotalCount;
protected System.Web.UI.WebControls.Label lbCurrentPage;
protected System.Web.UI.WebControls.DropDownList ddlPage;
protected System.Web.UI.WebControls.LinkButton FirstPage;
protected System.Web.UI.WebControls.LinkButton PreviousPage;
protected System.Web.UI.WebControls.LinkButton NextPage;
protected System.Web.UI.WebControls.DataGrid dbOrders;
protected System.Web.UI.WebControls.LinkButton LastPage;
private ClassDataGrid CDG=new ClassDataGrid();//数据访问层的类,定义的的一个实例
protected System.Web.UI.WebControls.Button btnGo;
protected System.Web.UI.WebControls.TextBox txtPage;
protected System.Web.UI.WebControls.ValidationSummary ValidationSummary1;
protected System.Web.UI.WebControls.RegularExpressionValidator RegularExpressionValidator1;
protected System.Web.UI.WebControls.Button btnDelete;
protected Int32 CurrentPageNumber = 1;//初始化,设置当前页为1,随时都要使用
private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
BindData();
btnDelete.Attributes ['onclick']='javascript:return confirm('您确认要删除吗?');';
}
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.ddlPage.SelectedIndexChanged += new System.EventHandler(this.ddlPage_SelectedIndexChanged);
this.btnGo.Click += new System.EventHandler(this.btnGo_Click);
this.dbOrders.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dbOrders_ItemCommand);
this.dbOrders.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dbOrders_DeleteCommand);
this.dbOrders.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dbOrders_ItemDataBound);
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public Int32 GetCountOrders()
{
Int32 Tcount=Int32.Parse(CDG.GetOrderCount());//此处用到了数据访问层,得到总记录数,代码要自己写一下
return Tcount;
}
protected void NavigationLink_Click ( Object sender, CommandEventArgs e )//此事件为HTML代码里注册(事件为OnCommand)
{
switch ( e.CommandName )//根据设置的CommandName属性判断
{
case 'First':
CurrentPageNumber = 1;
break;
case 'Last':
CurrentPageNumber = Int32.Parse ( lbTotalPage.Text );
break;
case 'Next':
CurrentPageNumber = Int32.Parse ( lbCurrentPage.Text ) + 1;
break;
case 'Prev':
CurrentPageNumber = Int32.Parse ( lbCurrentPage.Text ) - 1;
break;
}
BindData();
}
public void BindData()
{
lbCurrentPage.Text = CurrentPageNumber.ToString();
int PageSize=int.Parse(dbOrders.PageSize.ToString());
//绑定DATAGRID,此处用到数据访问层,只要可以绑定就可以了(DataSet,DataTable都可以),这里的代码要自己写一下
SqlDataReader dr=CDG.GetOrders(PageSize,CurrentPageNumber);
dbOrders.DataSource=Tools.ConvertDataReaderToDataTable(dr);
dbOrders.DataBind();
Double TotalPages = 1;//初始化,总页数为1
Double ModePages=0;//取模余数(判断总页数是否要加1)
Int32 TotalRecords = GetCountOrders();//调用上面的得到总记录数的方法
TotalPages = TotalRecords / PageSize;//得到总页数(总记录数除以每页的记录数)
ModePages=TotalRecords%PageSize;//得到取模的余数(总记录数取模每页的记录数)
if(ModePages>0)//如果取模数不等于0,则把总页数加1
{
TotalPages+=1;
}
if(ModePages==0)//如果取模数等于0,不做任何事(也可以不用判断)
{
}
lbTotalPage.Text = TotalPages.ToString();//显示页面上的总页数
lbTotalCount.Text=TotalRecords.ToString();//显示页面上的总记录数
//以下为判断点击的按钮(第一页,上一页,下一页,末一页)是否可以用
if ( CurrentPageNumber == 1 )
{
PreviousPage.Enabled = false;
FirstPage.Enabled=false;
if ( TotalPages > 1 )
{
NextPage.Enabled = true;
LastPage.Enabled=true;
}
else
{
NextPage.Enabled = false;
LastPage.Enabled=false;
}
}
else
{
PreviousPage.Enabled = true;
FirstPage.Enabled=true;
if ( CurrentPageNumber == TotalPages )
{
NextPage.Enabled = false;
LastPage.Enabled=false;
}
else
{
NextPage.Enabled = true;
LastPage.Enabled=true;
}
}
ddlPage.Items.Clear();//清楚跳转的页数(如果不清除,里面的记录将会循环增加)
int PCount=int.Parse(lbTotalPage.Text);//得到总页数,为了循环
for(int i=1;i<=PCount;i++)
{
ddlPage.Items.Add(i.ToString());
}
ddlPage.Items.FindByText(CurrentPageNumber.ToString()).Selected=true;//把当前页显示在列表框的第一个
}
private void ddlPage_SelectedIndexChanged(object sender, System.EventArgs e)
{
int PageSize=int.Parse(ddlPage.SelectedValue.ToString());
int PageCount=Int32.Parse(lbTotalPage.Text.Trim().ToString());
if(PageSize<1)
{
CurrentPageNumber=1;
}
else if(PageSize>PageCount)
{
CurrentPageNumber=PageCount;
}
else
{
CurrentPageNumber=PageSize;
}
BindData();
}
private void btnGo_Click(object sender, System.EventArgs e)
{
int PageSize=int.Parse(txtPage.Text.Trim().ToString());
int PageCount=Int32.Parse(lbTotalPage.Text.Trim().ToString());
if(PageSize<1)
{
CurrentPageNumber=1;
}
else if(PageSize>PageCount)
{
CurrentPageNumber=PageCount;
}
else
{
CurrentPageNumber=PageSize;
}
BindData();
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
try
{
string SelectedString = '';//定义一个变量
foreach(DataGridItem data in dbOrders.Items)
{
for(int i=0;i<data.Cells[0].Controls.Count;i++)//循环DataGrid的第一个单元格
{
if(data.Cells[0].Controls[i].GetType().ToString()=='System.Web.UI.WebControls.CheckBox')//判断类型是否为CheckBox
{
if(((CheckBox)data.Cells[0].Controls[i]).Checked==true)//如果CheckBox被选中
{
SelectedString += dbOrders.DataKeys[data.ItemIndex].ToString() + ',';
//因为设置了DataGrid的DataKeyField属性(编号),所以可以根据Datakeys得到选中CheckBox的那行所对应的那条记录的编号
//以逗号分隔,加入同一个字符串中
}
}
}
}
if(SelectedString!='')
{
bool result=true;
SelectedString = SelectedString.Substring(0,SelectedString.Length-1);//截取最后一个逗号
string[] Arr = SelectedString.Split(',');//分割字符串,放入数组中
for(int i=0;i<Arr.Length;i++)//循环数组
{
if(Arr[i].Trim()!='')//如果值不为空
{
//result=CDG.DeleteOrder(Int32.Parse(arrselected[i]));//删除一条记录
//这里的DeleteUser是我写的数据层的方法,返回的是bool,如果删除成功,返回true;
//此处的删除方法可以根据自己的需求写,所以这里就不写了
}
}
if(result)
{
Response.Write('<script>alert('用户删除成功')</script>');
}
}
BindData();
}
catch
{
Response.Write('<script>alert('过程出现错误,删除失败,请查找原因')</script>');
}
}
private void dbOrders_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType==ListItemType.Item)//这是判断属性生成器里添加的删除,弹出一个删除对话框
{
if(e.Item.ItemType==ListItemType.Item)//这个删除的事件在DeleteCommand中执行
{
LinkButton btn=(LinkButton)e.Item.Cells[7].Controls[0];
btn.Attributes.Add ('onclick',
'return confirm (/'确定要删除此项记录吗?/');');
}
}
if(e.Item.ItemType==ListItemType.Item || e.Item.ItemType==ListItemType.AlternatingItem)//这是判断模板列中里添加的删除按钮(Button,ImageButton也可以),弹出一个删除对话框
{
Button button = (Button) e.Item.FindControl('btnADelete');//这个事件在ItemCommand事件中执行
//ImageButton button = (ImageButton) e.Item.FindControl('Imagebutton1');
button.Attributes.Add ('onclick',
'return confirm (/'确定要删除此项记录吗?/');');
}
}
private void dbOrders_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
//写你的删除事件,下面是举例,具体根据自己的实际情况而定
int OrderID=int.Parse(dbOrders.DataKeys[e.Item.ItemIndex].ToString());//得到该行的对应的编号
// bool result=CDG.DeleteOrder(OrderID);
// if(result)
// {
// Response.Write('<script>alert('用户删除成功')</script>');
// }
}
private void dbOrders_ItemCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
int OrderID=int.Parse(dbOrders.DataKeys[e.Item.ItemIndex].ToString());//得到该行的对应的编号
if(e.CommandName.Equals('Delete'))//此属性为设置的控件的CommandName,刚才设置的是Delete,表示删除(其他也可以)
{
//写你的删除事件,下面是举例,具体根据自己的实际情况而定
//bool result=CDG.DeleteOrder(OrderID);
// if(result)
// {
// Response.Write('<script>alert('用户删除成功')</script>');
// }
}
}
}
}