DataList使用存储过程实现分页 .
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 46px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataList ID="DataList1" runat="server"
onitemcommand="DataList1_ItemCommand"
oncancelcommand="DataList1_CancelCommand"
ondeletecommand="DataList1_DeleteCommand" oneditcommand="DataList1_EditCommand"
onupdatecommand="DataList1_UpdateCommand" style="margin-right: 0px"
>
<EditItemTemplate>
<table style="width:100%;">
<tr>
<td class="style1">
姓名</td>
<td>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("sname") %>'></asp:TextBox>
</td>
</tr>
<tr>
<td class="style1">
性别</td>
<td>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("sex") %>'></asp:TextBox>
</td>
</tr>
<tr>
<td class="style1">
年龄</td>
<td>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("age") %>'></asp:TextBox>
</td>
</tr>
<tr>
<td class="style1">
<asp:Button ID="Button4" runat="server" CommandArgument='<%# Eval("sid") %>'
CommandName="update" Text="更新" />
</td>
<td>
<asp:Button ID="Button5" runat="server" CommandArgument='<%# Eval("sid") %>'
CommandName="cancel" Text="取消" />
</td>
</tr>
</table>
</EditItemTemplate>
<ItemTemplate>
姓名：<asp:Label ID="Label1" runat="server" Text='<%# Eval("sname") %>'></asp:Label>
<br />
性别：<asp:Label ID="Label2" runat="server" Text='<%# Eval("sex") %>'></asp:Label>
<br />
年龄：<asp:Label ID="Label3" runat="server" Text='<%# Eval("age") %>'></asp:Label>
<br />
<br />
<asp:Button ID="Button2" runat="server" CommandName="edit"
Text="编辑" CommandArgument='<%# Eval("sid") %>' />
<asp:Button ID="Button3" runat="server" CommandName="delete" Text="删除"
CommandArgument='<%# Eval("sid") %>' />
<br />
<br />
<asp:Button ID="Button1" runat="server" CommandArgument='<%# Eval("sid") %>'
CommandName="Buy" Text="放入购物车" />
</ItemTemplate>
</asp:DataList>
<br />
<asp:Button ID="btnfirst" runat="server" Text="第一页" onclick="btnfirst_Click" />
<asp:Button ID="next" runat="server" Text="上一页" onclick="next_Click" />
<asp:Button ID="btnNEXT" runat="server" Text="下一页" onclick="btnNEXT_Click" />
<asp:Button ID="btnlast" runat="server" Text="莫一页" onclick="btnlast_Click" />
<br />
<asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>
<br />
<br />
<asp:HiddenField ID="HiddenField1" runat="server" />
<asp:HiddenField ID="HiddenField2" runat="server" />
</div>
</form>
</body>
</html>
/////////////////////////////////////////////////////////
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindProduct(2,1);
}
}
private void BindProduct(int pageSize, int pageIndex)
{
string sp_name="sp_Student_Select_by_Page_rowNumber";
// string sql = "select * from student";
// @pageSize int, --每页记录数量
//@pageCount int output, --总页数
//@pageIndex int --当前页索引号
SqlParameter[] prms = new SqlParameter[] {
new SqlParameter("@pageSize",pageSize),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@pageIndex",pageIndex)
};
prms[1].Direction = ParameterDirection.Output;
DataTable dt = SqlHelper.ExecuteDataTable(sp_name,CommandType.StoredProcedure,prms);
this.DataList1.DataSource = dt;
this.DataList1.DataBind();
string sql = "select count(*) from student";
int pagetotalNumber = (int)SqlHelper.ExecteScalar(sql);
int pageCount;
if (pagetotalNumber % pageSize!= 0)
{
pageCount = pagetotalNumber / pageSize + 1;
}
else
{
pageCount = pagetotalNumber / pageSize;
}
this.HiddenField2.Value = pageCount.ToString();
this.HiddenField1.Value = pageIndex.ToString();
}
protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
if(e.CommandName=="Buy")
{
Response.Write(e.CommandArgument.ToString());
}
}
protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
{
this.DataList1.EditItemIndex = e.Item.ItemIndex;
this.BindProduct(2,1);
}
protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
string name = (e.Item.FindControl("TextBox1") as TextBox).Text;
string sex = (e.Item.FindControl("TextBox2") as TextBox).Text;
string age = (e.Item.FindControl("TextBox3") as TextBox).Text;
string sql = "update student set sname=@name,sex=@sex,age=@age where sid=@sid";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@name",name),
new SqlParameter("@sex",sex),
new SqlParameter("@age",age),
new SqlParameter("@sid",e.CommandArgument)
};
SqlHelper.ExecuteNonQuery(sql, pms);
}
protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
{
this.DataList1.EditItemIndex = -1;
this.BindProduct(2,1);
}
protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
{
string sql = "delete from student where sid=@sid";
SqlParameter pm = new SqlParameter("@sid",e.CommandArgument);
SqlHelper.ExecuteNonQuery(sql,pm);
this.BindProduct(3,1);
}
protected void btnfirst_Click(object sender, EventArgs e)
{
this.BindProduct(2,1);
}
protected void next_Click(object sender, EventArgs e)
{
int index = Convert.ToInt32(this.HiddenField1.Value);
if(index>1)
{
index--;
}
this.BindProduct(2,index);
}
protected void btnNEXT_Click(object sender, EventArgs e)
{
int index = Convert.ToInt32(this.HiddenField1.Value);
if (index<Convert.ToInt32(this.HiddenField2.Value))
{
index++;
}
this.BindProduct(2,index);
}
protected void btnlast_Click(object sender, EventArgs e)
{
this.BindProduct(2,Convert.ToInt32(this.HiddenField2.Value));
}
/////////////////////////////////////////////
public static object ExecteScalar(string sql, params SqlParameter[] pms)
{
using (SqlConnection conn = new SqlConnection(constr2))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
if (pms != null)///说明还传了参数
{
cmd.Parameters.AddRange(pms);
}
conn.Open();
return cmd.ExecuteScalar();
}
}
}
public static DataTable ExecuteDataTable(string sql,CommandType commandtype, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(sql,constr2);
adapter.SelectCommand.CommandType = commandtype;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
return dt;
}
}
}
