我的ASP.NET才没有那麼難系列第一彈 - 小阿娟的分頁查詢

Tweet

俺のASP.NETがこんなに難しいわけがない




古巴比倫王頒布了漢摩拉比法典，刻在黑色的玄武岩，距今已經三千七百多年
但是MSIT87成果發表距今才幾天，所以OK的
故事發生在本組的論壇首頁
小阿娟表示：GRIDVIEW神馬的最討厭惹，人家不要用
然後啪啦啪啦的寫出了這個驚世巨作


各位觀眾，沒有GRIDVIEW！只有一個Literal！




但是執行起來...

究竟小阿娟是使用了什麼樣的魔法呢？就讓我們看下去









好的，十分強大十分牛逼，嫻熟的職業PG
成為了真正的PG，不虧是PG的驕傲，PG巴萊
我們感謝小阿娟精彩的演出




有任何疑問請找小阿娟

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.Text;

using System.Text.RegularExpressions;

namespace PagingWithoutGridview {

    public partial class PagingWithoutGridview : System.Web.UI.Page {

        int eachItem = 5;           //每頁顯示5筆

        int nowType = 1;            //目前分類

        int nowPage = 0;            //目前頁數

        string nowKey = "";         //關鍵字

        string strCmdGridView, strCmdCount = ""; //下拉選項的select語法

        string strConn = ConfigurationManager.ConnectionStrings["finance_keeperConnectionString"].ConnectionString;

        const string constInteger = @"^-?[1-9]\d*|0$";

        public bool isInteger(string s) {

            Regex regex = new Regex(constInteger);

            return regex.IsMatch(s);

        }

        protected void Page_Load(object sender, EventArgs e) {

            if (!IsPostBack) {

                //處理 QueryString

                if (!string.IsNullOrEmpty(Request.QueryString["Page"]) && isInteger(Request.QueryString["Page"])) nowPage = (int.Parse(Request.QueryString["Page"]) - 1) * eachItem;

                if (!string.IsNullOrEmpty(Request.QueryString["Type"]) && isInteger(Request.QueryString["Type"])) nowType = int.Parse(Request.QueryString["Type"]);

                if (!string.IsNullOrEmpty(Request.QueryString["Key"])) nowKey = Server.UrlDecode(Request.QueryString["Key"]);

                strCmdGridView = string.Format(

                    @"select top {0} inf_name,clicks,hot,information.datetime,from_id,inf_id,inf_type_name,nickname,account.uid from information

                    join inf_type on information.inf_type_id=inf_type.inf_type_id

                    join account on information.uid=account.uid

                    where inf_id=from_id

                    and information.remove=0

                    and inf_name like '%{2}%'  

                    {3}                                                                 

                    and information.datetime not in (

                        select top {1} information.[datetime] from information

                        join inf_type on information.inf_type_id=inf_type.inf_type_id

                        where inf_id = from_id

                        and information.remove=0

                        and inf_name like '%{2}%'

                        {3}

                        order by information.[datetime] desc)

                    order by information.datetime desc", eachItem, nowPage, nowKey, (nowType == 0) ? "" : "and information.inf_type_id=" + nowType);

                strCmdCount = string.Format(

                    @"select COUNT(*) from information

                    where inf_id=from_id

                    and information.remove=0

                    and inf_name like '%{0}%'

                    {1}", nowKey, (nowType == 0) ? "" : "and information.inf_type_id=" + nowType);

                //下拉選單繫結分類名稱

                using (SqlConnection conn = new SqlConnection(strConn))

                using (SqlCommand cmd = new SqlCommand("select inf_type_id, inf_type_name from inf_type", conn)) {

                    conn.Open();

                    using (SqlDataReader dr = cmd.ExecuteReader()) {

                        ddlTypeName.DataSource = dr;

                        ddlTypeName.DataTextField = "inf_type_name";

                        ddlTypeName.DataValueField = "inf_type_id";

                        ddlTypeName.DataBind();

                    }

                }

                ddlTypeName.Items.Insert(0, new ListItem("全部", "0"));

                //保留使用者輸入，不因 PostBack 變成預設值               

                ddlTypeName.SelectedValue = Convert.ToString(nowType);

                txtQuery.Text = nowKey;

                StringBuilder sb = new StringBuilder();

                //頁籤

                using (SqlConnection conn = new SqlConnection(strConn))

                using (SqlCommand cmd = new SqlCommand("select inf_type_id,inf_type_name from inf_type", conn)) {

                    conn.Open();

                    using (SqlDataReader dr = cmd.ExecuteReader()) {

                        string css;

                        sb.Append("<div id='TabArea'>");

                        sb.Append("<ul id='TypeTab'>");

                        while (dr.Read()) {

                            css = (!string.IsNullOrEmpty(Request.QueryString["Type"]) && Convert.ToInt32(dr["inf_type_id"]) == nowType) ? "class='currType'" : "class='NonCurrType'";

                            sb.Append("<li " + css + " ><a href='" + Request.Url.AbsolutePath + "?Type=" + dr["inf_type_id"] + "'>" + dr["inf_type_name"] + "</a></li>");

                        }

                        sb.Append("</ul>");

                        sb.Append("<div style='clear:both;'></div>");

                        sb.Append("</div>");

                    }

                }

                //內容

                using (SqlConnection conn = new SqlConnection(strConn))

                using (SqlCommand cmd = new SqlCommand(strCmdGridView, conn)) {

                    conn.Open();

                    using (SqlDataReader dr = cmd.ExecuteReader()) {

                        if (dr.HasRows) {

                            sb.Append("<div id='ViewArea'>");

                            sb.Append("<table id='InfoTab' cellpadding='0' cellspacing='0'>");

                            sb.Append("<tbody>");

                            sb.Append("<tr id='tableHeader'><th class='col1' colspan='2'>標題</th><th class='col2'>點閱</th><th class='col2'>推薦</th><th class='col3'>發表時間</th></tr>");

                            while (dr.Read()) {

                                string infcontent = Convert.ToString(dr["inf_name"]);

                                //關鍵字變色

                                if (!string.IsNullOrEmpty(Request.QueryString["Key"])) {

                                    string qs = Convert.ToString(Request.QueryString["Key"]);

                                    infcontent = infcontent.Replace(qs, "<span style='color:red'>" + qs + "</span>");

                                }

                                sb.Append("<tr>");

                                sb.Append(string.Format("<td class='col4'>{0}</td>", dr["inf_type_name"]) + Environment.NewLine);

                                sb.Append(string.Format("<td  style='font-size:14px;'> <a href='information.aspx?inf_id={0}'>{1}</a></td>", dr["inf_id"], infcontent) + Environment.NewLine);

                                sb.Append(string.Format("<td class='col2'>{0}</td>", dr["clicks"]) + Environment.NewLine);

                                sb.Append(string.Format("<td class='col2'>{0}</td>", dr["hot"]) + Environment.NewLine);

                                sb.Append(string.Format("<td class='col3'>{0}</br><a href='/account.aspx?uid={2}'>{1}</a></td>", dr["datetime"], dr["nickname"], dr["uid"]) + Environment.NewLine);

                                sb.Append("</tr>");

                            }

                            sb.Append("</tbody>");

                            sb.Append("</table>");

                            sb.Append("</div>");

                        } else {

                            sb.Append("<div style='width:727px;border:1px solid gray;text-align:center;'>");

                            sb.Append("</br></br></br>");

                            sb.Append("很抱歉,無法搜尋到有關");

                            sb.Append("<span style='color:red'>" + nowKey + "</span>");

                            sb.Append("的資料,返回<a href='forum.aspx'>主題列表</a>");

                            sb.Append("</br></br></br>");

                            sb.Append("</div>");

                        }

                    }

                }

                //分頁

                int Pagers = 0;

                using (SqlConnection conn = new SqlConnection(strConn))

                using (SqlCommand comm = new SqlCommand(strCmdCount, conn)) {

                    conn.Open();

                    using (SqlDataReader dr = comm.ExecuteReader()) {

                        while (dr.Read()) {

                            Pagers = Convert.ToInt32(dr[0]);

                        }

                    }

                }

                Pagers = Convert.ToInt16(Math.Ceiling((double)Pagers / eachItem)); //記錄資料有幾頁，Math.Ceiling無條件進位              

                for (int i = 1; i <= Pagers; i++) {

                    if (Convert.ToString(Request.QueryString["page"]) != i.ToString()) {

                        sb.Append(string.Format("<span class='pagerstyle'><a href='{3}?Page={0}&Key={1}&Type={2}'>{0}</a></span>", i, nowKey, nowType, Request.Url.AbsolutePath));

                    } else {

                        sb.Append(string.Format("<span class='pagerstyle'>{0}</span>", i));

                    }

                }

                myLiteral.Text = Convert.ToString(sb);

            }

        }

        protected void btnSearch_Click(object sender, EventArgs e) {

            //透過 QueryString 來查詢，故按下查詢鈕時只加上 QueryString 轉跳網址，所有的 SQL 查詢都在 Page_Load 中執行。

            if (string.IsNullOrEmpty(txtQuery.Text.Trim())) {

                ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('請輸入關鍵字');", true);

            } else {

                Response.Redirect(string.Format("{0}?Type={1}&Key={2}", Request.Url.AbsolutePath, Server.UrlEncode(ddlTypeName.SelectedValue), Server.UrlEncode(txtQuery.Text.Trim())));

            }

        }

    }

}