DataList

            

         下面代码是用DataList控与数据库连用，操作的，更具有实用性，可以根据自己的需求设计自己理想的界面！还有分页功能。是通过button按钮来实现的！

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
 {
 if(!IsPostBack)
 BindProduct("1");
 }

private void BindProduct(string pageindex)
 {
 string str = ConfigurationManager.ConnectionStrings["studentCnn"].ConnectionString;
 using (SqlConnection sqlCnn = new SqlConnection(str))
 {
 SqlDataAdapter da = new SqlDataAdapter("sp_Student_Select_by_Page_rowNumber", sqlCnn);
 da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageindex);
 da.SelectCommand.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
 da.SelectCommand.Parameters.AddWithValue("@pageSize", 2);
 da.SelectCommand.CommandType = CommandType.StoredProcedure;
 DataSet ds = new DataSet();
 da.Fill(ds);
 this.DataList1.DataSource = ds.Tables[0].DefaultView;
 this.DataList1.DataBind();
 this.HiddenField1.Value = pageindex;
 this.HiddenField2.Value = da.SelectCommand.Parameters["@pageCount"].Value.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(this.HiddenField1.Value);
 } //编辑
 protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
 {
 this.DataList1.EditItemIndex = -1;
 this.BindProduct(this.HiddenField1.Value);
 } //取消
 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 str = ConfigurationManager.ConnectionStrings["studentCnn"].ConnectionString;
 using (SqlConnection sqlCnn = new SqlConnection(str))
 {
 SqlCommand sqlcmm = sqlCnn.CreateCommand();
 sqlcmm.CommandText = "update student setsname=@sname,sex=@sex,age=@age wheresid=@sid";
 sqlcmm.Parameters.AddWithValue("@sname", name);
 sqlcmm.Parameters.AddWithValue("@sex", sex);
 sqlcmm.Parameters.AddWithValue("@age", age);
 sqlcmm.Parameters.AddWithValue("@sid", e.CommandArgument);
 sqlCnn.Open();
 sqlcmm.ExecuteNonQuery();

}
 this.DataList1.EditItemIndex = -1;
 this.BindProduct(this.HiddenField1.Value);
 } //更新
 protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
 {
 string str = ConfigurationManager.ConnectionStrings["studentCnn"].ConnectionString;
 using (SqlConnection sqlCnn = new SqlConnection(str))
 {
 SqlCommand sqlcmm = sqlCnn.CreateCommand();
 sqlcmm.CommandText = "delete from student wheresid=@sid";
 sqlcmm.Parameters.AddWithValue("@sid", e.CommandArgument);
 sqlCnn.Open();
 sqlcmm.ExecuteNonQuery();
 }
 this.BindProduct(this.HiddenField1.Value);
 } //删除
 protected void Button6_Click(object sender, EventArgs e)
 {
 this.BindProduct("1");
 } //首页
 protected void Button9_Click(object sender, EventArgs e)
 {
 int count = Convert.ToInt32(this.HiddenField2.Value);
 this.BindProduct(count.ToString());
 } //尾页
 protected void Button7_Click(object sender, EventArgs e)
 {
 int index = Convert.ToInt32(this.HiddenField1.Value);
 if (index > 1)
 index--;
 this.BindProduct(index.ToString());
 } //上一页
 protected void Button8_Click(object sender, EventArgs e)
 {
 int index = Convert.ToInt32(this.HiddenField1.Value);
 int count = Convert.ToInt32(this.HiddenField2.Value);

 if (index < count)
 index++;
 this.BindProduct(index.ToString());
 }
} //下一页

存储过程的创建：

ALTER PROCEDURE sp_Student_Select_by_Page_rowNumber
 @pageSize int, --每页记录数量
 @pageCount int output, --总页数
 @pageIndex int --当前页索引号

AS
BEGIN
declare @totalRecords int
select @totalRecords = count(sid) from student
if(@totalRecords % @pageSize = 0)
 set @pageCount = @totalRecords / @pageSize;
else
 set @pageCount = @totalRecords / @pageSize +1;
with temp as (select row_number() over (order by sid) as id,* from student)
select * from temp where id between (@pageIndex -1)*@pageSize +1 and @pageIndex * @pageSize
return @totalRecords
end