DropDownList使用存储过程实现分页

aspx文件：

1.DataList使用存储过程实现分页

aspx文件：

<div>
   
        <asp:DataList ID="DataList1" runat="server"
            Xoncancelcommand="DataList1_CancelCommand"
            Xondeletecommand="DataList1_DeleteCommand" Xonitemcommand="DataList1_ItemCommand"
            Xonupdatecommand="DataList1_UpdateCommand"
            Xoneditcommand="DataList1_EditCommand">

        <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("age") %>'></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            性别：</td>
                        <td>
                            <asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("sex") %>'></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <asp:Button ID="btnGenxin" runat="server" CommandArgument='<%# Eval("sid") %>'
                                CommandName="update" Text="更新" />
                        </td>
                        <td>
                            <asp:Button ID="btnCancel" 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 />
                <br />
                年龄：<asp:Label ID="Label2" runat="server" Text='<%# Eval("age") %>'></asp:Label>
                <br />
                <br />
                性别：<asp:Label ID="Label3" runat="server" Text='<%# Eval("sex") %>'></asp:Label>
                <br />
                <br />
                <asp:Button ID="btnedit" runat="server" CommandName="edit" Text="编辑" />
                &nbsp;&nbsp;
                <asp:Button ID="btndelete" runat="server" CommandName="delete" Text="删除" />
                <br />
                <br />
                <asp:Button ID="Button1" runat="server" CommandArgument='<%# Eval("sid") %>'
                    CommandName="Buy" Text="加入购物车" />
                <br />
                <br />
                <br />
            </ItemTemplate>
        </asp:DataList>
     <asp:Button ID="btnfirst" runat="server"  Text="首页" Xοnclick="btnfirst_Click" />
        <asp:Button ID="btnprew" runat="server" Text="前一页" Xοnclick="btnprew_Click" />
        <asp:Button ID="btnnext" runat="server" Text="后一页" Xοnclick="btnnext_Click" />
        <asp:Button ID="btnlast" runat="server"  Text="最后一页" Xοnclick="btnlast_Click" />
        <br />
        <br />
        <asp:HiddenField ID="HiddenField1" runat="server" />
        <asp:HiddenField ID="HiddenField2" runat="server" />
    </div>

aspx.cs文件：

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                bindProduct(3,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 = 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_CancelCommand(object source, DataListCommandEventArgs e)
        {
            this.DataList1.EditItemIndex = -1;
            this.bindProduct(3,1);
        }

        protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
        {
            string sql = "delete from student where sid=@sid";
            SqlParameter pms = new SqlParameter("@sid", e.CommandArgument);
            SqlHelper.ExecuteNoQuery(sql, pms);
            this.bindProduct(3,1);
        }

        protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
        {
            string name = (e.Item.FindControl("TextBox1") as TextBox).Text;
            string sex = (e.Item.FindControl("TextBox3") as TextBox).Text;
            string age = (e.Item.FindControl("TextBox2") as TextBox).Text;
            string sql = "update student set sname=@name,age=@age,sex=@sex 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.ExecuteNoQuery(sql, pms);
        }

        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(3,1);
        }

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

        protected void btnprew_Click(object sender, EventArgs e)
        {
           int index= Convert.ToInt32(this.HiddenField1.Value);
           if (index>1)
           {
                index--;
           }
            this.bindProduct(3, 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(3, index);
        }

        protected void btnlast_Click(object sender, EventArgs e)
        {
            this.bindProduct(2, Convert.ToInt32(this.HiddenField2.Value));
        }

2.repeater实现样式设置，并添加删除按钮，参考课上老师做的案例。

aspx文件;

<style type="text/css">
    .big_img img
    {
        border:1px solid #ccc;
        width:100px;
        height:100px;
        }
    .big_img:hover img
    {
        height:200px;
        width:200px;
        display:inline;
        border:1px solid navy;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="Repeater1" runat="server"
            Xonitemcommand="Repeater1_ItemCommand">
        <HeaderTemplate>
        <table border="" width="100%">
        <tr><th>姓名</th><th>性别</th><th>年龄</th><th>照片</th><th>操作</th></tr>
        </HeaderTemplate>
        <ItemTemplate>
         <tr class="center" align="center">
          <td><%#Eval("sname")%></td><td><%#Eval("sex")%></td><td><%#Eval("age")%></td>
          <div style=" display:inline; position:relative;">
          <td><a href="#" class="big_img"><img src='<%#"images/"+Eval("photo")%>' /></a></td>
          <td>
              <asp:LinkButton ID="btnDelete" runat="server" Text="删除" CommandName="delete" CommandArgument='<%#Eval("sid")%>'>删除</asp:LinkButton>
          </td>

</div>
         </tr>
        </ItemTemplate>
        <FooterTemplate></table></FooterTemplate>

        <AlternatingItemTemplate>
        <tr class="center" align="center" style=" background-color:#e8e8e8;">
          <td><%#Eval("sname")%></td><td><%#Eval("sex")%></td><td><%#Eval("age")%></td>
          <div style=" display:inline; position:relative;">
          <td><a href="#" class="big_img"><img src='images/<%#Eval("photo")%>' /></a></td>
          </div>
          <td>
              <asp:LinkButton ID="btnDelete" runat="server" Text="删除" CommandName="delete" CommandArgument='<%#Eval("sid")%>'>删除</asp:LinkButton>
          </td>

</tr>
        </AlternatingItemTemplate>
        </asp:Repeater>
    </div>

aspx.cs文件：

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                 BingUsers();
            }
          
        }

        private void BingUsers()
        {
            string sql = "select * from student";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text);
            this.Repeater1.DataSource = dt;

            this.Repeater1.DataBind();
        }

        protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            if (e.CommandName=="delete")
         {
                string sql = "delete from student where sid=@sid";
                SqlParameter p1 = new SqlParameter("@sid", e.CommandArgument.ToString());
                SqlHelper.ExecuteNoQuery(sql, p1);
                this.BingUsers();

         }

3.数据表添加字段status，使用repeater实现禁用与启用。

aspx文件：

<style type="text/css">
    .big_img img
    {
        border:1px solid #ccc;
        width:100px;
        height:100px;
        }
    .big_img:hover img
    {
        height:200px;
        width:200px;
        display:inline;
        border:1px solid navy;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="Repeater1" runat="server"
            Xonitemcommand="Repeater1_ItemCommand">
        <HeaderTemplate>
        <table border="" width="100%">
        <tr><th>姓名</th><th>性别</th><th>年龄</th><th>照片</th><th>操作</th></tr>
        </HeaderTemplate>
        <ItemTemplate>
         <tr class="center" align="center">
          <td><%#Eval("sname")%></td><td><%#Eval("sex")%></td><td><%#Eval("age")%></td>
          <div style=" display:inline; position:relative;">
          <td><a href="#" class="big_img"><img src='<%#"images/"+Eval("photo")%>' /></a></td>

<td>
              <asp:LinkButton ID="shifouyong" runat="server" Text='<%#Convert.ToBoolean( Eval("status"))?"禁用":"启用"%>' CommandName='<%#Convert.ToBoolean( Eval("status"))?"false":"true"%>' CommandArgument='<%#Eval("sid")%>'><%#Convert.ToBoolean( Eval("status"))?"禁用":"启用"%></asp:LinkButton>
          </td>      
          </div>
         </tr>
        </ItemTemplate>
        <FooterTemplate></table></FooterTemplate>

        <AlternatingItemTemplate>
        <tr class="center" align="center" style=" background-color:#e8e8e8;">
          <td><%#Eval("sname")%></td><td><%#Eval("sex")%></td><td><%#Eval("age")%></td>
          <div style=" display:inline; position:relative;">
          <td><a href="#" class="big_img"><img src='images/<%#Eval("photo")%>' /></a></td>
          </div>

<td>
              <asp:LinkButton ID="shifouyong" runat="server" Text='<%#Convert.ToBoolean( Eval("status"))?"禁用":"启用"%>' CommandName='<%#Convert.ToBoolean( Eval("status"))?"false":"true"%>' CommandArgument='<%#Eval("sid")%>'><%#Convert.ToBoolean( Eval("status"))?"禁用":"启用"%></asp:LinkButton>
          </td>
         </tr>
        </AlternatingItemTemplate>
        </asp:Repeater>
    </div>

aspx.cs文件：

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                 BingUsers();
            }
          
        }

        private void BingUsers()
        {
            string sql = "select * from student";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text);
            this.Repeater1.DataSource = dt;

            this.Repeater1.DataBind();
        }

  protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
           if (e.CommandName == "false" || e.CommandName == "true")
            {
                string sql = "update student set status=@status where sid=@sid";
                SqlParameter[] prm = new SqlParameter[]
                {
                    new SqlParameter("@status",e.CommandName),
                    new SqlParameter("@sid",e.CommandArgument.ToString())
                };

                SqlHelper.ExecuteNoQuery(sql, prm);
                this.BingUsers();

            }

 aspx.cs文件：

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

namespace _12._18zuoye
{
    public partial class DataLlist : 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["studentConnectionString"].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_UpdateCommand(object source, DataListCommandEventArgs e)
        {
            string name = (e.Item.FindControl("TextBox1") as TextBox).Text;
            string sex = (e.Item.FindControl("TextBox3") as TextBox).Text;
            string age = (e.Item.FindControl("TextBox2") as TextBox).Text;
            string sql = "update student set sname=@name,age=@age,sex=@sex 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.ExecuteNoQuery(sql, pms);
        }

        protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
        {
            this.DataList1.EditItemIndex = -1;
            this.bindProduct(this.HiddenField1.Value);
        }

        protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
        {
            string sql = "delete from student where sid=@sid";
            SqlParameter pms = new SqlParameter("@sid", e.CommandArgument);
            SqlHelper.ExecuteNoQuery(sql,pms);
            this.bindProduct(this.HiddenField1.Value);
        }

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

        protected void Button2_Click1(object sender, EventArgs e)
        {
            int index = Convert.ToInt32(this.HiddenField1.Value);

            if (index > 1)

                index--;

            this.bindProduct(index.ToString());
        }

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

        protected void Button4_Click1(object sender, EventArgs e)
        {
            int count = Convert.ToInt32(this.HiddenField2.Value);

            this.bindProduct(count.ToString());
        }

    }
}

存储过程：

ALTER PROCEDURE [dbo].[sp_Student_Select_by_Page]
 -- Add the parameters for the stored procedure here
 @pageSize int,  --每页记录数量
 @pageCount int output,  --总页数
 @pageIndex int,  --当前页索引号
 @strWhere   varchar(8000)=''  --查询条件
 
AS
BEGIN
 declare @strSQL   varchar(8000);
 declare @strTmp   varchar(100);
 declare @strSelect varchar(4000);
declare @strOrder varchar(6000)
set @strOrder='order by sid '  --排序条件
set @strSelect=' * '
 if @PageIndex = 1
     begin
         if @strWhere != ''
             set @strSQL = 'select top ' + str(@PageSize) + @strSelect+'  from student where ' + @strWhere + ' ' + @strOrder
         else
             set @strSQL = 'select top ' + str(@PageSize) + @strSelect+'   from student '+ @strOrder
     end
 else
     begin

         set @strSQL = 'select top ' + str(@PageSize)  + @strSelect+'   from student
                 where sid>(select max(sid) from (select top ' + str((@PageIndex-1)*@PageSize) + ' sid from student ' + @strOrder + ') as tblTmp) '+ @strOrder
         if @strWhere != ''
             set @strSQL = 'select top ' + str(@PageSize)  + @strSelect+'     from student
                 where sid>(select max(sid) from
                 (select top ' + str((@PageIndex-1)*@PageSize) + ' sid from  Student where ' + @strWhere + ' '
                 + @strOrder + ') as tblTmp) and  ' + @strWhere + ' ' + @strOrder
     end
declare @recordCount int
 select @recordCount=count(sid) from student
 set @pageCount= @recordCount / @pageSize
 if @recordCount % @pageSize > 0
  set @pageCount=@pageCount+1

print @strSQL
  exec (@strSQL)
 end

 

SqlHelper中代码：

private static readonly string constr = ConfigurationManager.ConnectionStrings["studentConnectionString"].ConnectionString;

 public static DataTable ExecuteDataTable(string sql,CommandType commandtype, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, constr);//得到适配器

            adapter.SelectCommand.CommandType = commandtype;
           
            if (pms != null)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }

            adapter.Fill(dt);
            return dt;
        }

 public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;

                    foreach (SqlParameter parameter in pms)
                    {
                        cmd.Parameters.Add(parameter);
                    }

                    return cmd.ExecuteScalar();
                }

            }
        }

public static int ExecuteNoQuery(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {

                    if (pms != null)  //说明传了参数
                    {
                        cmd.Parameters.AddRange(pms);
                    }

                    con.Open();
                    return cmd.ExecuteNonQuery();

                }
            }
        }