12-19 DataList使用存储过程实现分页

*************************Demo1.aspx.cs

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 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";
            SqlParameter[] prms = new SqlParameter[]{
            new SqlParameter("@pageSize",pageSize),
            new SqlParameter("@pageCount",SqlDbType.Int),
            new SqlParameter("@pageIndex",pageIndex),
            };
            prms[1].Direction=ParameterDirection.Output;
            DataTable dt = sqlHealpler.sqlHealper.ExecuteTable(sp_name,CommandType.StoredProcedure,prms);
            this.DataList1.DataSource = dt;
            this.DataList1.DataBind();

            string sql = "select count(*) from student";
            int pageTotalNumber=(int) sqlHealpler.sqlHealper.ExecuteScalar(sql);//获取的是总的条数
            if (pageTotalNumber % pageSize != 0)
            {
                this.HiddenField2.Value = (pageTotalNumber / pageSize + 1).ToString();
            }
            else
            {
                this.HiddenField2.Value = (pageTotalNumber / pageSize).ToString();
            }
            this.HiddenField1.Value = pageIndex.ToString();
        }

        protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
        {
            if (e.CommandName == "abc")
            {
                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=@id";
            SqlParameter[] pms = new SqlParameter[]{
            new SqlParameter("@name",name),
            new SqlParameter("@sex",sex),
            new SqlParameter("@age",age),
            new SqlParameter("@id",e.CommandArgument)
            };
            sqlHealpler.sqlHealper.ExecturNonQuery(sql, pms);
           this.bindProduct(2,1);
        }

        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 student where sid=@id";
            SqlParameter pms =new  SqlParameter("@id", e.CommandArgument);
            sqlHealpler.sqlHealper.ExecturNonQuery(sql, pms);
            this.bindProduct(2,1);
        }

        protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void btnfrist_Click(object sender, EventArgs e)
        {
            this.bindProduct(2,1);
        }

        protected void btnshang_Click(object sender, EventArgs e)
        {
            int index = Convert.ToInt32(this.HiddenField1.Value);
            if (index > 1)
            {
                index--;
            }
            bindProduct(2, index);
        }

        protected void btnxia_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));
        }
    }
}

***************************Demo1.aspx

<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeBehind="Demo1.aspx.cs" Inherits="DataList.Demo1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <asp:DataList ID="DataList1" runat="server"
            oncancelcommand="DataList1_CancelCommand"
            ondeletecommand="DataList1_DeleteCommand" oneditcommand="DataList1_EditCommand"
            onitemcommand="DataList1_ItemCommand"
            onupdatecommand="DataList1_UpdateCommand"
            onselectedindexchanged="DataList1_SelectedIndexChanged">
            <EditItemTemplate>
                <table style="width:100%;">
                    <tr>
                        <td>
                            姓名：</td>
                        <td>
                            <asp:TextBox ID="TextBox1" 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="TextBox3" 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="btnCancel" runat="server" 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("sex") %>'></asp:Label>
                <br />
                年龄：<asp:Label ID="Label3" runat="server" Text='<%# Eval("age") %>'></asp:Label>
                <br />
                <asp:Button ID="btnedit" runat="server" CommandName="Edit" Text="编辑" />
                <asp:Button ID="btndelete" runat="server" CommandArgument='<%# Eval("sid") %>'
                    CommandName="delete" Text="删除" />
                <br />
                <br />
          
                <asp:Button ID="加入购物车" runat="server" Text="加入购物车"
                    CommandArgument='<%# Eval("sid") %>' CommandName="abc" />
            </ItemTemplate>
        </asp:DataList>
   
        <br />
        <asp:Button ID="btnfrist" runat="server" οnclick="btnfrist_Click" Text="第一页" />
        <asp:Button ID="btnshang" runat="server" οnclick="btnshang_Click" Text="上一页" />
        <asp:Button ID="btnxia" runat="server" οnclick="btnxia_Click" Text="下一页" />
        <asp:Button ID="btnlast" runat="server" οnclick="btnlast_Click" Text="最后一页" />
        <asp:FormView ID="FormView1" runat="server">
        </asp:FormView>
        <br />
        <br />
        <asp:HiddenField ID="HiddenField1" runat="server" />
        <asp:HiddenField ID="HiddenField2" runat="server" />
   
        <br />
   
    </div>
    </form>
</body>
</html>

*********************************sqlHealper.cs

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

namespace sqlHealpler
{
    public class sqlHealper
    {
        private static readonly string constr4 = ConfigurationManager.ConnectionStrings["student"].ConnectionString;
        public static int ExecturNonQuery(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr4))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                   return cmd.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr4))
            {
                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(constr4);
           
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    return reader;
                }
        }
        public static DataTable ExecuteTable(string sql,CommandType commandtype, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, constr4);
            adapter.SelectCommand.CommandType = commandtype;
            if (pms != null)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            adapter.Fill(dt);
            return dt;
        }

     
    }
}