ASP.NET三层+SQL存储过程海量分页

40 篇文章 0 订阅
11 篇文章 0 订阅
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entry;
using System.Data.SqlClient;
namespace DAL //DAL层
{
    public class BookDataAccess
    {
        public PageInfo SelectBooksByPager(int pageIndex, int pageSize)
        {
            Book book = null;  //书本实体类
            List<Book> books = new List<Book>();
            MSSConnection conn = new MSSConnection();//连接数据库的类
            conn.OpenConn();//打开数据库连接
            SqlParameter paraPageIndex = new SqlParameter("@pageIndex", System.Data.SqlDbType.Int);//new出第一个参数(代表的页码)
            paraPageIndex.Value = pageIndex;
            SqlParameter paraPageSize = new SqlParameter("@pageSize", System.Data.SqlDbType.Int);//new出第二个参数(代表的每页显示行数)
            paraPageSize.Value = pageSize;
            SqlParameter paraTotalPages = new SqlParameter("@totalPages", System.Data.SqlDbType.Int);//new出第三个参数(代表总页数)
            paraTotalPages.Direction = System.Data.ParameterDirection.Output; //总页数是要输出的参数
            SqlDataReader reader = conn.ExecuteProcSelect("books_pager",new SqlParameter[]{paraPageIndex,paraPageSize,paraTotalPages});
            while (reader.Read()) {
                book = new Book();
                book.Id = (int)reader["bookId"];
                book.Title = reader["Title"].ToString();
                book.Author = reader["Author"].ToString();
                book.ISBN = reader["ISBN"].ToString();
                book.UnitPrice = (decimal)reader["UnitPrice"];
                books.Add(book);
            }
            conn.CloseConn();//关闭数据库连接
            //我不仅仅要返回书的信息,而且,我还要返回总页数
            //当前,每页显示的行数
            PageInfo pagerInfo = new PageInfo();
            pagerInfo.Books = books;  //页面信息的书本集合赋值
            pagerInfo.PageIndex = pageIndex;  //页码
            pagerInfo.PageSize = pageSize;  //每页显示行数
            pagerInfo.TotalPage = (int)paraTotalPages.Value; //总页数
            return pagerInfo;
        }
        
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace DAL //DAL层
{
    
    public class MSSConnection
    {
        private SqlConnection conn;
        private SqlCommand command;
        //构造函数
        public MSSConnection(){
            conn = new SqlConnection("Data Source=.;Initial Catalog=MyBookShop;Integrated Security=True");
            command = new SqlCommand("", conn);
        }
        //打开数据库连接
        public void OpenConn() {
            conn.Open();
        }
        //关闭数据库连接
        public void CloseConn(){
            conn.Close();   
        }
        //获取数据库存储过程
        private void PrepareCommand(string procName, SqlParameter[] paras) {
            command.CommandType = System.Data.CommandType.StoredProcedure;//执行样式是存储过程
            command.CommandText = procName;//执行存储过程
            if (paras != null) {
                command.Parameters.AddRange(paras);
            }
        }
        //查询数据库中的存储过程(按照存储过程的名字查询)
        public SqlDataReader ExecuteProcSelect(string procName, SqlParameter[] parameters) {
            PrepareCommand(procName, parameters);
            return command.ExecuteReader();
        }
    }
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Entry //Entity层
{
   public class Book
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        private string title;

        public string Title
        {
            get { return title; }
            set { title = value; }
        }
        private string author;

        public string Author
        {
            get { return author; }
            set { author = value; }
        }
        private string iSBN;

        public string ISBN
        {
            get { return iSBN; }
            set { iSBN = value; }
        }
        private DateTime publisherTime;  //出版日期

        public DateTime PublisherTime
        {
            get { return publisherTime; }
            set { publisherTime = value; }
        }
        private int clicks;              //点击次数

        public int Clicks
        {
            get { return clicks; }
            set { clicks = value; }
        }
        private int orderNumber;         //数量

        public int OrderNumber
        {
            get { return orderNumber; }
            set { orderNumber = value; }
        }
        private int wordsCount;

        public int WordsCount
        {
            get { return wordsCount; }
            set { wordsCount = value; }
        }
        private decimal unitPrice;

        public decimal UnitPrice
        {
            get { return unitPrice; }
            set { unitPrice = value; }
        }

        private string contentDescriptionmp;

        public string ContentDescriptionmp
        {
            get { return contentDescriptionmp; }
            set { contentDescriptionmp = value; }
        }
        private string aurhorDescription;

        public string AurhorDescription
        {
            get { return aurhorDescription; }
            set { aurhorDescription = value; }
        }
        private string editorComment;

        public string EditorComment
        {
            get { return editorComment; }
            set { editorComment = value; }
        }
        private string tOC;

        public string TOC
        {
            get { return tOC; }
            set { tOC = value; }
        }
    }
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Entry //Entity层
{
   public class PageInfo
    {
        private List<Book> books; //集合

        public List<Book> Books
        {
            get { return books; }
            set { books = value; }
        }
        private int totalPage; //总页数

        public int TotalPage
        {
            get { return totalPage; }
            set { totalPage = value; }
        }

        private int pageIndex; //页码

        public int PageIndex
        {
            get { return pageIndex; }
            set { pageIndex = value; }
        }


        private int pageSize;  //每页显示的数据量大小(数据行数)

        public int PageSize
        {
            get { return pageSize; }
            set { pageSize = value; }
        }

  
    }
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using Entry;


namespace Logic //BLL层
{
   public class BookLogin
    {
       /// <summary>
       /// 分页
       /// </summary>
       /// <param name="pageIndex">当前页码</param>
       /// <param name="pageSize">每页显示的行数</param>
       /// <returns></returns>
       public PageInfo GetBooksByPager(int pageIndex, int pageSize) {
           return new BookDataAccess().SelectBooksByPager(pageIndex, pageSize);
       }
    }
}

//界面UI
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowBookInfo.aspx.cs" Inherits="ShowBookInfo" %>

<!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 id="Head1" runat="server">
    <title></title>
    <style type="text/css">
        *{ margin:0px; padding:0px;}
        body{ font-size:12px; font-family:@Adobe 仿宋 Std R;}
        .img_bookcover{ width:130px; height:160px;}
        dt{ border:1px solid #ccc; width:180px; height:160px; text-align:center; float:left;}
        dd{ margin-left:10px; float:left;}
        dd p{ padding:9px 0px;}
        .p_description{ text-indent:20px;}
        .div_booklist{ width:700px; margin:0px auto;}
        .div_layout{ width:700px; margin:0px auto;}
        .p_price{ text-align:right;}
        .p_price a{ margin:0px 10px;}
        dl{ margin:10px 0px; height:160px;}
        .lblPageIndex{ color:Blue;}
        .lblTotalPage{ color:Blue;}
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div class="div_layout">
        <asp:ListView ID="lsvBooks" runat="server">
            <LayoutTemplate>
                <div class="div_booklist">
                    <dl id="itemPlaceholder" runat="server"></dl>
                </div>
            </LayoutTemplate>
            <ItemTemplate>
                <dl>
                    <dt><img class="img_bookcover" src='BookCovers/<%# Eval("ISBN") %>.jpg' alt='<%# Eval("Title") %>' /></dt>
                </dl>
                <dd>
                    <p><a href="#">C语言在线</a></p>
                    <p><span><%#Eval("Author") %>著</span></p>
                    <p class="p_description">
                         三爷,广告界的创意大佬,因为迷恋星座,创办了由12 星座、12生肖组成的星时代广告公司,这位“ 职场高手”,在广告圈把创意和业务玩转得淋漓尽致,但面对层层陷阱与背叛,是否同样从容?这位“ 情...
                    </p>
                    <p class="p_price"><span>¥<%#Eval("UnitPrice") %>元</span><a href="#">购买</a><a href="#">收藏</a></p>
                </dd>
            </ItemTemplate>
        </asp:ListView>
        <asp:Button ID="btnFirst" runat="server" Text="第一页"  οnclick="btnFirst_Click" />
        <asp:Button ID="btnPre" runat="server" Text="上一页" οnclick="btnPre_Click" />
        <asp:Button ID="btnNext" runat="server" Text="下一页" οnclick="btnNext_Click" />
        <asp:Button ID="btnLast" runat="server" οnclick="btnLast_Click" Text="尾页" />
        <span>共</span><asp:Label ID="lblTotalPager" CssClass="lblTotalPage" runat="server" Text=""></asp:Label><span>页</span><span>/</span>
        <span>第</span><asp:Label ID="lblPageIndex"  CssClass="lblPageIndex" runat="server" Text=""></asp:Label><span>页</span>
    </div>
    </form>
</body>
</html>
    
//界面后台
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Entry;
using Logic;

public partial class ShowBookInfo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    //预备发送事件
    protected void Page_PreRender(object sender, EventArgs e)
    {
        if (ViewState["pageIndex"] == null)
        {
            ViewState["pageIndex"] = 1;
        }
        //当前页的信息要放在viewState中保存
        PageInfo pageInfo = new BookLogin().GetBooksByPager((int)ViewState["pageIndex"], 3);//每页显示三行数据
        //绑定
        lsvBooks.DataSource = pageInfo.Books;
        lsvBooks.DataBind();
        if ((int)ViewState["pageIndex"] == 1)
        {
            btnPre.Enabled = false; //如果当前页码等于1的话上一页的按钮不可点
        }
        else
        {
            btnPre.Enabled = true;//否则可点
        }
        if ((int)ViewState["pageIndex"] == pageInfo.TotalPage)
        {
            btnNext.Enabled = false;//如果等于最后一页的话下一页不可点
        }
        else
        {
            btnNext.Enabled = true;//否则可点
        }
        lblPageIndex.Text = pageInfo.PageIndex.ToString();//当前页码
        lblTotalPager.Text = pageInfo.TotalPage.ToString();//总页数
    }
    //上一页
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (ViewState["pageIndex"] == null)
        {
            ViewState["pageIndex"] = 1; //默认为第一页
        }
        int pageIndex = (int)ViewState["pageIndex"];
        ViewState["pageIndex"] = pageIndex - 1;//当前页码减1
    }
    //下一页
    protected void btnNext_Click(object sender, EventArgs e)
    {
        if (ViewState["pageIndex"] == null)
        {
            ViewState["pageIndex"] = 1; //默认为第一页
        }
        int pageIndex = (int)ViewState["pageIndex"];
        ViewState["pageIndex"] = pageIndex + 1;//当前页码减1
    }
    //最后一页
    protected void btnLast_Click(object sender, EventArgs e)
    {
        if (ViewState["pageIndex"] == null)
        {
            ViewState["pageIndex"] = 1; //默认为第一页
        } 
        int lastPageIndex =Convert.ToInt32(lblTotalPager.Text); //总页数
        ViewState["pageIndex"] = lastPageIndex;                 //最后一页
    }
    //第一页
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        if (ViewState["pageIndex"] == null)
        {
            ViewState["pageIndex"] = 1; //默认为第一页
        }
        ViewState["pageIndex"] = 1;     //第一页
    }
}



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

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值