1、前台设计:
<body>
<form id="form1" runat="server">
<div>
<asp:DataList ID="DataList1" runat="server" DataKeyField="sid"
oncancelcommand="DataList1_CancelCommand"
ondeletecommand="DataList1_DeleteCommand" oneditcommand="DataList1_EditCommand"
onupdatecommand="DataList1_UpdateCommand">
<EditItemTemplate>
<table style="width:100%;">
<tr>
<td class="style2">
姓名:</td>
<td>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("sname") %>'></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
性别:</td>
<td>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("sex") %>'></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
年龄:</td>
<td>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("age") %>'></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
<asp:Button ID="Button5" runat="server" CommandArgument='<%# Eval("sid") %>'
CommandName="update" Text="更新" />
</td>
<td>
<asp:Button ID="Button6" runat="server" CommandName="cancel" Text="取消" />
</td>
</tr>
</table>
</EditItemTemplate>
<FooterTemplate>
<asp:Button ID="Button1" runat="server" οnclick="Button1_Click" Text="首页" />
<asp:Button ID="Button2" runat="server" οnclick="Button2_Click" Text="上一页" />
<asp:Button ID="Button3" runat="server" οnclick="Button3_Click" Text="下一页" />
<asp:Button ID="Button4" runat="server" οnclick="Button4_Click" Text="尾页" />
</FooterTemplate>
<ItemTemplate>
sid:
<asp:Label ID="sidLabel" runat="server" Text='<%# Eval("sid") %>' />
<br />
sname:
<asp:Label ID="snameLabel" runat="server" Text='<%# Eval("sname") %>' />
<br />
classid:
<asp:Label ID="classidLabel" runat="server" Text='<%# Eval("classid") %>' />
<br />
sex:
<asp:Label ID="sexLabel" runat="server" Text='<%# Eval("sex") %>' />
<br />
age:
<asp:Label ID="ageLabel" runat="server" Text='<%# Eval("age") %>' />
<br />
<asp:Button ID="Button7" runat="server" CommandName="edit" Text="编辑" />
<asp:Button ID="Button8" runat="server" CommandName="delete" Text="删除" />
<br />
</ItemTemplate>
</asp:DataList>
<asp:HiddenField ID="HiddenField1" runat="server" />
<asp:HiddenField ID="HiddenField2" runat="server" />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:studentConnectionString %>"
SelectCommand="SELECT [sid], [sname], [classid], [sex], [age] FROM [student]">
</asp:SqlDataSource>
</div>
</form>
</body>
2、后台设计:
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";
SqlParameter[] prms = new SqlParameter[]{
new SqlParameter("@pageSize",pageSize),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@pageIndex",pageIndex)
};
prms[1].Direction = ParameterDirection.Output;
DataTable dt = SqlHealper1.ExecuteDataTable(sp_name, CommandType.StoredProcedure, prms);
this.DataList1.DataSource = dt;
this.DataList1.DataBind();
string sql="select count(*) from student";
int pageTotalNumber=(int)sqlHealper1.ExecuteSalar(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 DataList2_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "buy")
{
Response.Write(e.CommandArgument.ToString());
}
}
protected void DataList2_EditCommand(object source, DataListCommandEventArgs e)
{
this.DataList2.EditItemIndex = e.Item.ItemIndex;
this.bindProcure(2,1);
}
protected void DataList2_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=@sname,sex=@sex,age=@age where sid=@sid";
SqlParameter[] prm = new SqlParameter[]
{
new SqlParameter("@sname",name),
new SqlParameter("@sex",sex),
new SqlParameter("@age",age),
new SqlParameter("@sid",e.CommandArgument)
};
SqlHealper1.ExecuteNonQuery(sql, prm);
}
protected void DataList2_CancelCommand(object source, DataListCommandEventArgs e)
{
this.DataList2.EditItemIndex = -1;
this.bindProcure(2,1);
}
protected void DataList2_DeleteCommand(object source, DataListCommandEventArgs e)
{
string sql = "delete from student where sid=@sid";
SqlParameter prm = new SqlParameter("sid", e.CommandArgument);
SqlHealper1.ExecuteNonQuery(sql, prm);
this.bindProcure(2,1);
}
protected void Button1_Click(object sender, EventArgs e)//首页
{
this.BindProduct(2,1);
}
protected void Button2_Click(object sender, EventArgs e)//上一页
{
int index = Convert.ToInt32(this.HiddenField1.Value);
if (index > 1)
{
index--;
}
this.BindProduct(2,index);
}
protected void Button3_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 Button4_Click(object sender, EventArgs e)//尾页
{
this.BindProduct(2,Covert.ToInt32(this.HiddenFiel2.Value));
}
}
}
sqlheaper页的设计:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace _11_28DataSet111
{
public static class SqlHealper1
{
private static readonly string constr1 = ConfigurationManager.ConnectionStrings["studentConnectionString"].ConnectionString;
#region ExecuteNonQuery 执行insert delete update的方法
public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(constr1))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)//说明还传了参数
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
#endregion
public static object ExecuteScalar(string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(constr1))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)//说明还传了参数
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
public static object ExecuteReader(string sql, params SqlParameter[] pms)//返回的参数,params是可能没有这个参数,SqlParameter[]数组的参数
{
SqlConnection con = new SqlConnection(constr1);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)//说明还传了参数
{
cmd.Parameters.AddRange(pms);
}
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
public static DataTable ExecuteDataTable(string sql,CommandType commandtype, params SqlParameter[] pms)//返回的参数,params是可能没有这个参数,SqlParameter[]数组的参数
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(sql, constr1);
adapter.SelectCommand.CommandType = commandtype;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);//添加参数的方法
}
adapter.Fill(dt);
string pageCount=adapter.SelectCommand.Parameters[1].Value.ToSrting();
return dt;
}
}
}
web.config的设计:
<configuration>
<connectionStrings>
<add name="studentConnectionString" connectionString="Data Source=机名;Initial Catalog=数据库名;User ID=数据库登陆id;Password=数据库登陆密码"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
</configuration>