在ASP.NET高级中运用DataList使用存储过程实现分页

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>
        

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值