Simple learning of. NET paging control

2021-08-16 23:41:00
OfStack

These days, I accidentally saw a post about paging, and I thought it was quite good. About these things, I only know the principle, but I didn't really do it, so I studied the principle of paging and wrote a very simple paging program with 10 points. I share it with you here.

This program uses ado. net to fetch data. First, create a new class PageDAl to fetch data


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

namespace page.DAL
{
  public class PageDal
  {
    public DataTable GetUserList(out int totalCount, int pageIndex = 1, int pagesize = 10)
    {
      using (
        SqlConnection coon =
          new SqlConnection(ConfigurationManager.ConnectionStrings["userConnection"].ConnectionString))
      {
        coon.Open();
        string sqlCount = "select count(F_Id) from Sys_User";
        SqlCommand cmd = new SqlCommand(sqlCount, coon);
        totalCount = int.Parse(cmd.ExecuteScalar().ToString());
        string sql = "select F_Account,F_RealName from (select *,Row_Number() over(order by F_Account) r from Sys_User)as w where r>{0} and r<={1};";
        SqlDataAdapter ad = new SqlDataAdapter(String.Format(sql, (pageIndex - 1) * pagesize, (pageIndex * pagesize)), coon);
        DataTable dt = new DataTable();
        ad.Fill(dt);

        return dt;
      }
    }
  }
}

Then remember to modify 1 webconfig inside the connection database string, the database itself to build a casual line.

Next is the ashx1-like handler, where the html page passes the requirements, and he is fetching the data from PageDal.


using page.DAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;

namespace page
{
  /// <summary>
  /// WebHandler  Summary description of 
  /// </summary>
  public class WebHandler : IHttpHandler
  {
    public void ProcessRequest(HttpContext context)
    {
      try
      {
        int pageIndex = int.Parse(context.Request.Form["pageindex"]);
        int pageSize = int.Parse(context.Request.Form["pagesize"]);
        PageDal pd = new PageDal();
        int totalCount;
        DataTable dt = pd.GetUserList(out totalCount, pageIndex, pageSize);
        string json = ToJson(dt, "data", totalCount);
        context.Response.ContentType = "text/plain";
        context.Response.Write(json);
      }
      catch
      {
        context.Response.Write("error");
      }
    }

    public bool IsReusable
    {
      get
      {
        return false;
      }
    }

    /// <summary>
    /// DataTable Convert to Json
    /// </summary>
    public static string ToJson(DataTable dt, string jsonName, int count)
    {
      StringBuilder Json = new StringBuilder();
      if (string.IsNullOrEmpty(jsonName))
        jsonName = dt.TableName;
      Json.Append("{\"" + jsonName + "\":[");
      if (dt.Rows.Count > 0)
      {
        for (int i = 0; i < dt.Rows.Count; i++)
        {
          Json.Append("{");
          for (int j = 0; j < dt.Columns.Count; j++)
          {
            Type type = dt.Rows[i][j].GetType();
            Json.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + StringFormat(dt.Rows[i][j].ToString(), type));
            if (j < dt.Columns.Count - 1)
            {
              Json.Append(",");
            }
          }
          Json.Append("}");
          if (i < dt.Rows.Count - 1)
          {
            Json.Append(",");
          }
        }
      }
      Json.Append("],");
      Json.Append("\"count\":" + count + "}");
      return Json.ToString();
    }

    /// <summary>
    ///  Formatted character type, date type, Boolean type 
    /// </summary>
    /// <param name="str"></param>
    /// <param name="type"></param>
    /// <returns></returns>
    private static string StringFormat(string str, Type type)
    {
      if (type == typeof(string))
      {
        str = String2Json(str);
        str = "\"" + str + "\"";
      }
      else if (type == typeof(DateTime))
      {
        str = "\"" + str + "\"";
      }
      else if (type == typeof(bool))
      {
        str = str.ToLower();
      }
      else if (type != typeof(string) && string.IsNullOrEmpty(str))
      {
        str = "\"" + str + "\"";
      }
      return str;
    }

    /// <summary>
    ///  Filter special characters 
    /// </summary>
    /// <param name="s"> String </param>
    /// <returns>json String </returns>
    private static string String2Json(String s)
    {
      StringBuilder sb = new StringBuilder();
      for (int i = 0; i < s.Length; i++)
      {
        char c = s.ToCharArray()[i];
        switch (c)
        {
          case '\"':
            sb.Append("\\\""); break;
          case '\\':
            sb.Append("\\\\"); break;
          case '/':
            sb.Append("\\/"); break;
          case '\b':
            sb.Append("\\b"); break;
          case '\f':
            sb.Append("\\f"); break;
          case '\n':
            sb.Append("\\n"); break;
          case '\r':
            sb.Append("\\r"); break;
          case '\t':
            sb.Append("\\t"); break;
          default:
            sb.Append(c); break;
        }
      }
      return sb.ToString();
    }
  }
}

Finally, the html code


<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <script src="../Content/jquery/jquery-1.11.1.min.js"></script>
  <title></title>
</head>
<body>
  <table id="datatable"></table>
  <div id="pageination" style="width: 100%">
    <a href="javascript:void(0);" onclick="GoFirst()"> Home page </a>&nbsp;<a href="javascript:void(0);" onclick="GoPre()"> Upper 1 Page </a>&nbsp;&nbsp;<span> Current <input id="pageindex" type="text" style="width:20px" value="1" disabled="disabled" /> Pages, total <input id="totalcount" type="text" style="width:20px" value="" disabled="disabled" /> Items of data, in total <input id="pagecount" type="text" style="width:20px" value="" disabled="disabled" /> Page </span>&nbsp;&nbsp;<a href="javascript:void(0);" onclick="GoNext()"> Under 1 Page </a>&nbsp;<a href="javascript:void(0);" onclick="GoLast()"> End page </a>
  </div>
</body>
</html>
<script>
  var pageindex = 1;
  var pagesize = 15;
  $(function () {
    search();
  });
  function search() {
    $.ajax({
      url: "../WebHandler.ashx",
      type: "post",
      data: {
        "pagesize": pagesize,
        "pageindex": pageindex
      },
      success: function (daa) {
        var json = JSON.parse(daa);
        var tab = $("#datatable");
        tab.empty();
        for (var i = 0; i < json.data.length ; i++) {
          tab.append("<tr><td>" +
            json.data[i].F_Account +
            "</td><td>" +
            json.data[i].F_RealName +
            "</td></tr>");

        }
        var count = json.count;
        var pagecount = Math.ceil(count / pagesize);
        $("#totalcount").val(count);
        $("#pagecount").val(pagecount);
      }
    });
  };
  function GoFirst() {
    pageindex = 1;
    $("#pageindex").val(pageindex);
    search();
  };
  function GoLast() {
    var pageindex = $("#pagecount").val();
    $("#pageindex").val(pageindex);
    search();
  };
  function GoPre() {
    if (pageindex > 1) {
      pageindex = pageindex - 1;
      $("#pageindex").val(pageindex);
      search();
    } else {
      alert(" This is the first 1 Page! ");
    }
  };
  function GoNext() {
    var pagecount = $("#pagecount").val();
    if (pageindex < pagecount) {
      pageindex = pageindex + 1;
      $("#pageindex").val(pageindex);
      search();
    } else {
      alert(" This is the last 1 Page! ");
    }
  };
</script>

After reading that post, I looked at the paging made by the company and summarized it. I didn't seriously think about paging before, and I thought it was quite difficult. However, after a summary, I found that as long as you make it clear at 1 point and 1 point in order, it is not difficult to have a clear idea.

First take the data, divided into two parts, one is the number of all the data out of the other is to take the data of the current page number, this is very simple, I write the relevant sql statement, sql statement writing a lot, I take the current page number with the statement is

select F_Account,F_RealName from (select *,Row_Number() over(order by F_Account) r from Sys_User)as w where r > (pageIndex - 1) * pagesize and r < =pageIndex* pagesize
After the data is taken out, the page columns are spelled together, and the relevant methods are written, such as the first page, the first page, the next page, the last page, etc. After that, the total number of data is transmitted, and the number of pages is calculated to be OK.


<div id="pageination" style="width: 100%">
    <a href="javascript:void(0);" onclick="GoFirst()"> Home page </a>&nbsp;<a href="javascript:void(0);" onclick="GoPre()"> Upper 1 Page </a>&nbsp;&nbsp;<span> Current <input id="pageindex" type="text" style="width:20px" value="1" disabled="disabled" /> Pages, total <input id="totalcount" type="text" style="width:20px" value="" disabled="disabled" /> Items of data, in total <input id="pagecount" type="text" style="width:20px" value="" disabled="disabled" /> Page </span>&nbsp;&nbsp;<a href="javascript:void(0);" onclick="GoNext()"> Under 1 Page </a>&nbsp;<a href="javascript:void(0);" onclick="GoLast()"> End page </a>
  </div>


function GoFirst() {
    pageindex = 1;
    $("#pageindex").val(pageindex);
    search();
  };
  function GoLast() {
    var pageindex = $("#pagecount").val();
    $("#pageindex").val(pageindex);
    search();
  };
  function GoPre() {
    if (pageindex > 1) {
      pageindex = pageindex - 1;
      $("#pageindex").val(pageindex);
      search();
    } else {
      alert(" This is the first 1 Page! ");
    }
  };
  function GoNext() {
    var pagecount = $("#pagecount").val();
    if (pageindex < pagecount) {
      pageindex = pageindex + 1;
      $("#pageindex").val(pageindex);
      search();
    } else {
      alert(" This is the last 1 Page! ");
    }
  };