DataList增删改查翻页
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected string connString;
protected int pageNum = -1;
protected int recNum = -1;
protected int pageRecNum = -1;
protected int curPageNO = -1;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
connString = "server=.;database=master;uid=sa;pwd=sa";
if ( !Page.IsPostBack )
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from usertable", conn);
recNum = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
ddlPageRowNum.Items.Clear();
for( int i=1;i<=recNum;i++ )
{
ddlPageRowNum.Items.Add(i.ToString());
}
if ( recNum < 5 )
{
ddlPageRowNum.SelectedIndex = recNum - 1;
}
else
{
ddlPageRowNum.SelectedIndex = 4;
}
pageRecNum = Convert.ToInt32(ddlPageRowNum.SelectedValue);
if ( Convert.ToInt32(recNum % pageRecNum) == 0 )
{
pageNum = Convert.ToInt32(recNum/pageRecNum);
}
else
{
pageNum = Convert.ToInt32(recNum/pageRecNum) + 1;
}
ddlJumpToPageNum.Items.Clear();
for( int i=1;i<=pageNum;i++ )
{
ddlJumpToPageNum.Items.Add(i.ToString());
}
lblPageNum.Text = pageNum.ToString();
ViewState["PageRowNum"] = pageRecNum;
ViewState["RecNum"] = recNum;
ViewState["PageNum"] = pageNum;
curPageNO = 1;
ViewState["CurPageNO"] = curPageNO;
}
pageRecNum = Convert.ToInt32(ViewState["PageRowNum"]);
recNum = Convert.ToInt32(ViewState["RecNum"]);
curPageNO = Convert.ToInt32(ViewState["CurPageNO"]);
pageNum = Convert.ToInt32(ViewState["PageNum"]);
if ( pageRecNum==-1 || recNum==-1 )
{
Response.Write("发生错误!");
Response.End();
}
if ( curPageNO <= 1 )
{
curPageNO = 1;
btnFirst.Enabled = false;
btnPreview.Enabled = false;
}
lblRecNum.Text = recNum.ToString();
lblCurPageNO.Text = curPageNO.ToString();
if ( !Page.IsPostBack )
{
BindData();
}
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.DataList1.ItemCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DataList1_ItemCommand);
this.DataList1.CancelCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DataList1_CancelCommand);
this.DataList1.EditCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DataList1_EditCommand);
this.DataList1.UpdateCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DataList1_UpdateCommand);
this.DataList1.DeleteCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DataList1_DeleteCommand);
this.DataList1.ItemDataBound += new System.Web.UI.WebControls.DataListItemEventHandler(this.DataList1_ItemDataBound);
this.btnFirst.Click += new System.EventHandler(this.btnFirst_Click);
this.btnPreview.Click += new System.EventHandler(this.btnPreview_Click);
this.btnNext.Click += new System.EventHandler(this.btnNext_Click);
this.btnLast.Click += new System.EventHandler(this.btnLast_Click);
this.ddlPageRowNum.SelectedIndexChanged += new System.EventHandler(this.ddlPageRowNum_SelectedIndexChanged);
this.ddlJumpToPageNum.SelectedIndexChanged += new System.EventHandler(this.ddlJumpToPageNum_SelectedIndexChanged);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
#region datelist
private void BindData()
{
string commandText = "select * from usertable";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(commandText, conn);
DataSet ds = new DataSet();
da.Fill(ds,(curPageNO-1) * pageRecNum,pageRecNum,"usertable");
DataList1.DataSource = ds.Tables["usertable"];
DataList1.DataBind();
ds.Clear();
conn.Close();
lblCurPageNO.Text = curPageNO.ToString();
}
private void DataList1_EditCommand(object source, System.Web.UI.WebControls.DataListCommandEventArgs e)
{
DataList1.EditItemIndex = e.Item.ItemIndex;
BindData();
}
private void DataList1_DeleteCommand(object source, System.Web.UI.WebControls.DataListCommandEventArgs e)
{
string typeID = e.CommandArgument.ToString();
string commandText = "Delete From usertable Where uid=@TypeID";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
conn.Open();
SqlCommand cmd = new SqlCommand(commandText, conn);
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("TypeID",typeID);
cmd.Parameters.Add(param[0]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
recNum --;
lblRecNum.Text = recNum.ToString();
ddlPageRowNum.Items.Clear();
for( int i=1;i<=recNum;i++ )
{
ddlPageRowNum.Items.Add(i.ToString());
}
ddlPageRowNum.SelectedValue = ViewState["PageRowNum"].ToString();
pageRecNum = Convert.ToInt32(ddlPageRowNum.SelectedValue);
if ( Convert.ToInt32(recNum % pageRecNum) == 0 )
{
pageNum = Convert.ToInt32(recNum/pageRecNum);
}
else
{
pageNum = Convert.ToInt32(recNum/pageRecNum) + 1;
}
ddlJumpToPageNum.Items.Clear();
for( int i=1;i<=pageNum;i++ )
{
ddlJumpToPageNum.Items.Add(i.ToString());
}
lblPageNum.Text = pageNum.ToString();
lblCurPageNO.Text = pageNum.ToString();
ViewState["PageRowNum"] = pageRecNum;
ViewState["RecNum"] = recNum;
ViewState["PageNum"] = pageNum;
curPageNO = Convert.ToInt32(ViewState["CurPageNO"]);
if ( curPageNO>pageNum )
{
curPageNO = pageNum;
}
ddlJumpToPageNum.SelectedValue = "1";
ViewState["CurPageNO"] = curPageNO;
if ( curPageNO == 1 )
{
btnFirst.Enabled = false;
btnPreview.Enabled = false;
}
if ( curPageNO == pageNum )
{
btnNext.Enabled = false;
btnLast.Enabled = false;
}
DataList1.EditItemIndex = -1;
DataList1.SelectedIndex = -1;
BindData();
}
private void DataList1_ItemDataBound(object sender, System.Web.UI.WebControls.DataListItemEventArgs e)
{
if ( e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem )
{
Button btnDel = (Button)e.Item.FindControl("btnDelete");
btnDel.Attributes.Add("onclick","return confirm('确定要删除[" + DataBinder.Eval(e.Item.DataItem,"uname") + "]吗？')");
e.Item.Attributes.Add("onmouseover","this.style.backgroundColor='#cccc00'");
e.Item.Attributes.Add("onmouseout","this.style.backgroundColor='white'");
}
}
private void DataList1_ItemCommand(object source, System.Web.UI.WebControls.DataListCommandEventArgs e)
{
if ( e.CommandName == "select" )
{
DataList1.SelectedIndex = e.Item.ItemIndex;
BindData();
}
if ( e.CommandName == "Insert" )
{
string typeID = ((TextBox)e.Item.FindControl("txtTypeIDInsert")).Text.ToString();
string typeName = ((TextBox)e.Item.FindControl("txtTypeNameInsert")).Text.ToString();
string commandText = "Insert Into usertable(uid,uname)";
commandText = commandText + " Values(@TypeID,@TypeName)";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
conn.Open();
SqlCommand cmd = new SqlCommand(commandText, conn);
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("TypeName",typeName);
param[1] = new SqlParameter("TypeID", typeID);
cmd.Parameters.Add(param[0]);
cmd.Parameters.Add(param[1]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
recNum ++;
lblRecNum.Text = recNum.ToString();
ddlPageRowNum.Items.Clear();
for( int i=1;i<=recNum;i++ )
{
ddlPageRowNum.Items.Add(i.ToString());
}
ddlPageRowNum.SelectedValue = ViewState["PageRowNum"].ToString();
pageRecNum = Convert.ToInt32(ddlPageRowNum.SelectedValue);
if ( Convert.ToInt32(recNum % pageRecNum) == 0 )
{
pageNum = Convert.ToInt32(recNum/pageRecNum);
}
else
{
pageNum = Convert.ToInt32(recNum/pageRecNum) + 1;
}
ddlJumpToPageNum.Items.Clear();
for( int i=1;i<=pageNum;i++ )
{
ddlJumpToPageNum.Items.Add(i.ToString());
}
lblPageNum.Text = pageNum.ToString();
lblCurPageNO.Text = pageNum.ToString();
ViewState["PageRowNum"] = pageRecNum;
ViewState["RecNum"] = recNum;
ViewState["PageNum"] = pageNum;
curPageNO = pageNum;
ddlJumpToPageNum.SelectedValue = "1";
ViewState["CurPageNO"] = curPageNO;
btnFirst.Enabled = true;
btnPreview.Enabled = true;
btnNext.Enabled = false;
btnLast.Enabled = false;
DataList1.EditItemIndex = -1;
DataList1.SelectedIndex = -1;
BindData();
}
}
private void DataList1_CancelCommand(object source, System.Web.UI.WebControls.DataListCommandEventArgs e)
{
DataList1.SelectedIndex = -1;
DataList1.EditItemIndex = -1;
BindData();
}
private void DataList1_UpdateCommand(object source, System.Web.UI.WebControls.DataListCommandEventArgs e)
{
string typeID = e.CommandArgument.ToString();
string typeName = ((TextBox)e.Item.FindControl("txtTypeName")).Text.ToString();
string commandText = "Update usertable Set uname=@TypeName";
commandText = commandText + " Where uid=@TypeID";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
conn.Open();
SqlCommand cmd = new SqlCommand(commandText, conn);
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("TypeName",typeName);
param[1] = new SqlParameter("TypeID", typeID);
cmd.Parameters.Add(param[0]);
cmd.Parameters.Add(param[1]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
DataList1.EditItemIndex = -1;
BindData();
}
#endregion
private void ddlPageRowNum_SelectedIndexChanged(object sender, System.EventArgs e)
{
#region pageRowNum,pageNum
pageRecNum = Convert.ToInt32(ddlPageRowNum.SelectedValue);
if ( Convert.ToInt32(recNum % pageRecNum) == 0 )
{
pageNum = Convert.ToInt32(recNum/pageRecNum);
}
else
{
pageNum = Convert.ToInt32(recNum/pageRecNum) + 1;
}
ddlJumpToPageNum.Items.Clear();
for( int i=1;i<=pageNum;i++ )
{
ddlJumpToPageNum.Items.Add(i.ToString());
}
lblPageNum.Text = pageNum.ToString();
ViewState["PageRowNum"] = pageRecNum;
ViewState["PageNum"] = pageNum;
if ( curPageNO > pageNum )
{
curPageNO = 1;
ViewState["CurPageNO"] = curPageNO.ToString();
}
btnFirst.Enabled = true;
btnPreview.Enabled = true;
btnNext.Enabled = true;
btnLast.Enabled = true;
if ( curPageNO == 1 )
{
btnFirst.Enabled = false;
btnPreview.Enabled = false;
}
if ( curPageNO == pageNum )
{
btnNext.Enabled = false;
btnLast.Enabled = false;
}
BindData();
#endregion
}
#region 移动页面
private void MoveFirst()
{
curPageNO = 1;
ViewState["CurPageNO"] = curPageNO.ToString();
BindData();
btnFirst.Enabled = false;
btnPreview.Enabled = false;
btnNext.Enabled = true;
btnLast.Enabled = true;
}
private void MovePrevious()
{
curPageNO = curPageNO - 1;
ViewState["CurPageNO"] = curPageNO.ToString();
BindData();
if ( curPageNO == 1 )
{
btnFirst.Enabled = false;
btnPreview.Enabled = false;
}
btnNext.Enabled = true;
btnLast.Enabled = true;
}
private void MoveNext()
{
curPageNO = curPageNO + 1;
ViewState["CurPageNO"] = curPageNO.ToString();
BindData();
btnFirst.Enabled = true;
btnPreview.Enabled = true;
if ( curPageNO == pageNum )
{
btnNext.Enabled = false;
btnLast.Enabled = false;
}
}
private void MoveLast()
{
curPageNO = pageNum;
ViewState["CurPageNO"] = curPageNO.ToString();
BindData();
btnFirst.Enabled = true;
btnPreview.Enabled = true;
btnNext.Enabled = false;
btnLast.Enabled = false;
}
#endregion
private void btnFirst_Click(object sender, System.EventArgs e)
{
MoveFirst();
}
private void btnPreview_Click(object sender, System.EventArgs e)
{
MovePrevious();
}
private void btnNext_Click(object sender, System.EventArgs e)
{
MoveNext();
}
private void btnLast_Click(object sender, System.EventArgs e)
{
MoveLast();
}
private void ddlJumpToPageNum_SelectedIndexChanged(object sender, System.EventArgs e)
{
curPageNO = Convert.ToInt32(ddlJumpToPageNum.SelectedValue);
BindData();
}
}
/*
if exists(select * from sysobjects where name='Usertable')
drop table usertable
go
create table usertable
(
uid int,
uname varchar(12)
)
insert into usertable values(1,'a')
insert into usertable values(2,'b')
insert into usertable values(3,'c')
insert into usertable values(4,'d')
insert into usertable values(5,'e')
insert into usertable values(6,'f')
insert into usertable values(7,'g')
insert into usertable values(8,'h')
select * from usertable
*/
