---前台-------
<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"> <EditItemTemplate> <table style="width: 100%;"> <tr> <td> 姓名：</td> <td> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("sname") %>'></asp:TextBox> </td> </tr> <tr> <td> 性别：</td> <td> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("sex") %>'></asp:TextBox> </td> </tr> <tr> <td> 年龄：</td> <td> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("age") %>'></asp:TextBox> </td> </tr> <tr> <td> <asp:Button ID="BtnUpdate" runat="server" CommandArgument='<%# Eval("sid") %>' CommandName="Update" Text="更新" /> </td> <td> <asp:Button ID="BtnCancle" runat="server" CommandArgument='<%# Eval("sid") %>' CommandName="cancel" Text="取消" /> </td> </tr> </table> </EditItemTemplate> <ItemTemplate> <br /> 姓名：<asp:Label ID="Label1" runat="server" Text='<%# Eval("sname") %>'></asp:Label> <br /> 年龄：<asp:Label ID="Label2" runat="server" Text='<%# Eval("age") %>'></asp:Label> <br /> 性别：<asp:Label ID="Label3" runat="server" Text='<%# Eval("sex") %>'></asp:Label> <br /> <br /> <asp:Button ID="BtnEdit" runat="server" CommandName="edit" Text="编辑" />  <asp:Button ID="BtnDelete" runat="server" CommandName="delete" Text="删除" CommandArgument='<%# Eval("sid") %>' /> <br /> <br /> <asp:Button ID="Button1" runat="server" Text="放入购物车" CommandArgument='<%# Eval("sid") %>' CommandName="Buy" /> <br /> <br /> </ItemTemplate> </asp:DataList> <asp:Button ID="BtnFirst" runat="server" οnclick="BtnFirst_Click" Text="首页" /> <asp:Button ID="BtnPrev" runat="server" οnclick="BtnPrev_Click" Text="前一页" /> <asp:Button ID="BtnNext" runat="server" οnclick="BtnNext_Click" Text="下一页" /> <asp:Button ID="BtnLast" runat="server" οnclick="BtnLast_Click" Text="尾页" /> <asp:HiddenField ID="HiddenField1" runat="server" /> <asp:HiddenField ID="HiddenField2" runat="server" /> <br /> </div> </form></body>--------后台---------using System;
using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using DataSet和SqlHelper;using System.Data.SqlClient;namespace DataList
{ public partial class Demo1 : System.Web.UI.Page { 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"; //@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.ExecuteScalar(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)
{ //Response.Write("edit.command"); this.DataList1.EditItemIndex = e.Item.ItemIndex; this.bindProduct(2,1); }protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{ string name = (e.Item.FindControl("TextBox3") as TextBox).Text; string sex = (e.Item.FindControl("TextBox2") as TextBox).Text; string age = (e.Item.FindControl("TextBox1") as TextBox).Text; string sql = "update student set where "; SqlParameter[] pms = new SqlParameter[]{ new SqlParameter("@name",name), new SqlParameter("@sex",sex), new SqlParameter("@age",age), new SqlParameter("@sid",e.CommandArgument),
};
sqlhelper.ExectueNonQuery(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 "; SqlParameter p1 = new SqlParameter("sid",e.CommandArgument); sqlhelper.ExectueNonQuery(sql,p1); this.bindProduct(2,1); }protected void BtnFirst_Click(object sender, EventArgs e)
{ this.bindProduct(2,1);}
protected void BtnPrev_Click(object sender, EventArgs e)
{ int index=Convert.ToInt32(this.HiddenField1.Value); if (index > 1) { index--; } 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++; } bindProduct(2, index); }protected void BtnLast_Click(object sender, EventArgs e)
{ this.bindProduct(2,Convert.ToInt32(this.HiddenField2.Value));}
}}
-----------sqlhelper.cs----------
using System;
using System.Collections.Generic;using System.Linq;using System.Web;using System.Configuration;using System.Data.SqlClient;using System.Data;namespace DataSet和SqlHelper
{ public static class sqlhelper { private static readonly string constr1 = ConfigurationManager.ConnectionStrings["studentConnectionString"].ConnectionString; //执行insert update delete的方法(要执行的sql语句（一定是select查询语句），sql语句中的参数) //执行查询select,返回查询结果中的第一行第一列的数据，这数据可能是int/string/datatime等等 public static int ExectueNonQuery(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();}
}}
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 SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms) {SqlConnection con = new SqlConnection(constr1);//不使用using，不关闭连接
using (SqlCommand cmd = new SqlCommand(sql, con)) {if (pms != null)//说明还传了参数
{ cmd.Parameters.AddRange(pms); } con.Open(); SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//不使用using ，不关闭dataReader return reader;}
} public static DataTable ExecuteDataTable(string sql, CommandType commandtype, params SqlParameter[] pms) { 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); return dt; } } }---------Web.config---------
<configuration>
<connectionStrings> <add name="studentConnectionString" connectionString="Data Source=LOVE-PC\SQLEXPRESSPC;Initial Catalog=student;User ID=sa;Password=admin" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.0" /> </system.web></configuration>