C# asp.net 分页数据源

记得大学还未毕业就去一家公司实习，第一个工作任务就是做列表的分页。随着时间的流逝加上对C#的理解不断的加深，这个分页的控件以不断的更换着版本。今天闲来无事跟大家分享下看看还有什么残缺的地方需要补。

分页控件基本组成：

1.数据提取：分页的算法、查询数据条数、分多少页。

2.页面操作：首页、上一页、下一页、尾页、列排序（个人认为比较鸡肋）、XX页GO。

数据提取第一步

对于分页算法我选择一个比较中庸的方法，效率不是很高但也不是很差。 ROW_NUMBER() OVER(ORDER BY ) 相信很多人对这个应该很熟悉了。目前项目所用到的数据库sql server 和 oracle，数据库版本也都支持这个东东。做的时候也充分考虑到要换用其他数据分页算法，所以把句子写的很灵活只要替换相关句子就可以满足其他特殊项目的需要。也就是说算法随你换。

列表肯定会有对数据的查询，查询也肯定会有特殊符号之类的东西。所以为了适应这点不得不每次都生成Sql语句进行查询，而不是用存储过程来做（如果哪位大大有更好的办法给我留言哦，我会万分感谢的）。

对于数据源的提取并不是仅仅列表的地方会用到，其他地方也要用到。所以先做个最基本，就是取数据源中A行到B行的数据咯。

   1:      /// <summary>

   2:      /// 用于获取数据源中依照某个字段排序后某行到某行的数据集

   3:      /// </summary>

   4:      public class PagingBase

   5:      {

   6:          /// <summary>

   7:          /// 获取数据集的语句结构

   8:          /// </summary>

   9:          protected string SqlDate = ""

  10:          + "\r\n select * from "

  11:          + "\r\n ( "

  12:          + "\r\n     select ROW_NUMBER() OVER(ORDER BY {6}) AS NB,{0} "

  13:          + "\r\n     from {1} "

  14:          + "\r\n     where 1=1{2} and 1=1 {3} "

  15:          + "\r\n ) t "

  16:          + "\r\n where NB > {4} and NB < {5} ";

17:

  18:          protected string conn;

19:

  20:          /// <summary>

  21:          /// 数据库连接串

  22:          /// </summary>

  23:          public string Conn

  24:          {

  25:              get { return string.IsNullOrEmpty(conn) ? DBConfig.ConfigName(): conn; }

  26:              set { conn = value; }

  27:          }

28:

  29:          /// <summary>

  30:          /// 用于排序的字段

  31:          /// </summary>

  32:          public string Order { get; set; }

33:

  34:          /// <summary>

  35:          /// 语句的Select部分

  36:          /// </summary>

  37:          public string Select { get; set; }

38:

  39:          /// <summary>

  40:          /// 语句From部分

  41:          /// </summary>

  42:          public string From { get; set; }

43:

  44:          /// <summary>

  45:          /// 语句where部分

  46:          /// </summary>

  47:          public string Where { get; set; }

48:

  49:          /// <summary>

  50:          /// 语句参数生成后的集合

  51:          /// </summary>

  52:          protected Dictionary<string, object> ColumnValue = new Dictionary<string, object>();

53:

  54:          /// <summary>

  55:          /// 开始行数

  56:          /// </summary>

  57:          public int RowStar { get; set; }

58:

  59:          /// <summary>

  60:          /// 结束行数

  61:          /// </summary>

  62:          public int RowEnd { get; set; }

63:

  64:          /// <summary>

  65:          /// where参数拼接后的结构部分

  66:          /// </summary>

  67:          protected string BuildWhere;

68:

  69:          /// <summary>

  70:          /// where参数原始记录部分

  71:          /// </summary>

  72:          protected List<string[]> WhereItem = new List<string[]>();

73:

74:

  75:          public bool Trim = true;

76:

77:

  78:          /// <summary>

  79:          /// 获取数据集

  80:          /// </summary>

  81:          /// <returns></returns>

  82:          public DataSet GetDate()

  83:          {

  84:              SqlDate = string.Format(SqlDate, Select, From, Where, BuildWhere, RowStar, RowEnd, Order);

  85:              return DBHelper.ExeSqlForDataSet(SqlDate, ColumnValue);

  86:          }

87:

  88:          /// <summary>

  89:          /// 添加Where中的查询条件

  90:          /// </summary>

  91:          /// <param name="FromAt"></param>

  92:          /// <param name="Value"></param>

  93:          public void AddWhereItem(string FromAt, string Value)

  94:          {

  95:              WhereItem.Add(new string[] { FromAt, Value });

  96:          }

97:

  98:          /// <summary>

  99:          /// 添加Where中的查询条件

 100:          /// </summary>

 101:          /// <param name="FromAt"></param>

 102:          /// <param name="Value"></param>

 103:          public void AddWhereItem(string FromAt, Control c)

 104:          {

 105:              WhereItem.Add(new string[] { FromAt, ControlValue.ControlValues(c) });

 106:          }

 107:

 108:          /// <summary>

 109:          /// where 参数自动生成

 110:          /// </summary>

 111:          /// <param name="Trim">是否要进行前后去空格操作</param>

 112:          protected void GetParanValue()

 113:          {

 114:              List<string> liWhereItem = new List<string>();

 115:              string DbMark = DBConfig.DbMark(Conn);

 116:              for (int i = 0; i < WhereItem.Count; i++)

 117:                  if (!string.IsNullOrEmpty(WhereItem[i][1].Trim()))

 118:                  {

 119:                      liWhereItem.Add(string.Format(WhereItem[i][0], " " + DbMark + "Q" + WhereItem[i].GetHashCode()));

 120:                      ColumnValue.Add(DbMark + "Q" + WhereItem[i].GetHashCode(), Trim ? WhereItem[i][1].Trim() : WhereItem[i][1]);

 121:                  }

 122:              BuildWhere = string.Join(" ", liWhereItem.ToArray());

 123:          }

 124:      }

注释：（大家根据自己项目的情况自己实现）

25行、如果数据库连接串名称为空的话，取默认数据库连接。

85行、根据语句与传进去的参数返回结果集。

105行、根据控件的类型返回控件的默认值。

数据提取第二步

第二步主要的工作把翻页的信心添加进去，如数据总条数、共多少页、一页多长、等等。

   1:  public class Paging : PagingBase

   2:      {

   3:          protected string SqlCount = ""

   4:          + "\r\n select count(1) "

   5:          + "\r\n from {0} "

   6:          + "\r\n where  1=1{1} and 1=1 {2} ";

7:

   8:          protected int pageSize = 10;

   9:          /// <summary>

  10:          /// 单页显示数据条数

  11:          /// </summary>

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

13:

  14:          private int pageNow = 1;

  15:          /// <summary>

  16:          /// 当前页

  17:          /// </summary>

  18:          public int PageNow

  19:          {

  20:              get { return pageNow; }

  21:              set

  22:              {

  23:                  if (pageCount == -1)

  24:                      pageNow = 1;

  25:                  else if (value > PageCount)

  26:                      pageNow = PageCount;

  27:                  else if (value < 1)

  28:                      pageNow = 1;

  29:                  else

  30:                      pageNow = value;

  31:              }

  32:          }

33:

  34:          private int pageCount = -1;

  35:          /// <summary>

  36:          /// 数据条数

  37:          /// </summary>

  38:          public int PageCount

  39:          {

  40:              get

  41:              {

  42:                  if (pageCount == -1)

  43:                  {

  44:                      int RowCount = GetCount();

  45:                      pageCount = RowCount / PageSize + ((RowCount % PageSize > 0) ? 1 : 0);

  46:                  }

  47:                  return pageCount;

  48:              }

  49:              set

  50:              { pageCount = value; }

  51:          }

52:

  53:          /// <summary>

  54:          /// where 参数自动生成

  55:          /// </summary>

  56:          /// <param name="Trim">是否要进行前后去空格操作</param>

  57:          public new void GetParanValue()

  58:          {

  59:              pageCount = -1;

  60:              pageNow = 1;

  61:              base.GetParanValue();

  62:          }

63:

  64:          /// <summary>

  65:          /// 获取数据集

  66:          /// </summary>

  67:          /// <returns></returns>

  68:          public new DataSet GetDate()

  69:          {

  70:              RowStar = (PageNow - 1) * PageSize;

  71:              RowEnd = PageNow * PageSize + 1;

  72:              return base.GetDate();

  73:          }

74:

  75:          /// <summary>

  76:          /// 获取数据Count数

  77:          /// </summary>

  78:          /// <returns></returns>

  79:          protected int GetCount()

  80:          {

  81:              SqlCount = string.Format(SqlCount, From, Where, BuildWhere);

  82:              return Convert.ToInt32(DBHelper.ExeSqlForScalar(SqlCount, ColumnValue));

  83:          }

  84:      }

注释：（大家根据自己项目的情况自己实现）

82行、根据语句和参数获取共有多少条数据。

数据提取第三步

真正意义上讲第三步已经不是完全取数据了，而是保存查询的相关数据。

   1:  public class PagComponents : Paging

   2:          {

   3:              protected Page PagingObject;

   4:              protected StateBag VS;

5:

   6:              /// <summary>

   7:              /// 页面初始加载

   8:              /// </summary>

   9:              /// <param name="p">当前页面Page对象</param>

  10:              /// <param name="v">当前页面ViewState对象</param>

  11:              public PagComponents(Page p, StateBag v)

  12:              {

  13:                  PagingObject = p;

  14:                  VS = v;

  15:                  if (PagingObject.IsPostBack)

  16:                      GetHistoryDate();

  17:                  else

  18:                      GetParanValue();

  19:                  PagingObject.PreRender += PagingPreRender;

  20:              }

21:

  22:              /// <summary>

  23:              /// 获取历史询项

  24:              /// </summary>

  25:              /// <param name="VS"></param>

  26:              public void GetHistoryDate()

  27:              {

  28:                  PageCount = (int)VS["pageCount"];

  29:                  PageNow = (int)VS["PageNow"];

  30:                  BuildWhere = VS["BuildWhere"].ToString();

  31:                  ColumnValue = (Dictionary<string, object>)VS["ColumnValue"];

  32:              }

33:

  34:              /// <summary>

  35:              ///历史查询项保存

  36:              /// </summary>

  37:              /// <param name="VS"></param>

  38:              protected void SetHistoryDate()

  39:              {

  40:                  VS["PageNow"] = PageNow;

  41:                  VS["pageCount"] = PageCount;

  42:                  VS["BuildWhere"] = BuildWhere;

  43:                  VS["ColumnValue"] = ColumnValue;

  44:              }

45:

  46:              /// <summary>

  47:              /// 页面结束用于查询数据保存

  48:              /// </summary>

  49:              /// <param name="sender"></param>

  50:              /// <param name="e"></param>

  51:              protected void PagingPreRender(object sender, EventArgs e) { SetHistoryDate(); }

  52:          }

页面操作就一部

由于不同客户对页面的需求不同,所以翻页部分做了自定义控件方便根据需求进行相关的变更。

下面是控件页面

   1:  <table style="width:100%;">

   2:      <tr>

   3:          <td>

   4:              &nbsp;</td>

   5:          <td>

   6:  <asp:Button ID="btnFirst" runat="server" Text="首 页" onclick="btnFirst_Click" />

7:

   8:  <asp:Button ID="btnUp" runat="server" Text="上一页" onclick="btnUp_Click" />

9:

  10:  <asp:Button ID="btnDown" runat="server" Text="下一页" onclick="btnDown_Click" />

11:

  12:  <asp:Button ID="btnLast" runat="server" Text="尾 页" onclick="btnLast_Click" />

13:

  14:              当前第<asp:Label ID="lbPageNow" runat="server" Text="Label"></asp:Label>

  15:              页，共<asp:Label ID="lbPageAll" runat="server" Text="Label"></asp:Label>

  16:              页。转到<asp:TextBox ID="tbxPageGo" runat="server"></asp:TextBox>

  17:              页<asp:Button ID="btnPaging" runat="server" Text="跳转"

  18:                  onclick="btnPaging_Click" />

19:

  20:          </td>

  21:          <td>

  22:              &nbsp;</td>

  23:      </tr>

  24:  </table>

下面是控件代码

1: public partial class PageingC : System.Web.UI.UserControl

 2:      {

   3:          public Paging paging;

   4:          protected void Page_PreRender(object sender, EventArgs e)

   5:          {

   6:              this.lbPageNow.Text = paging.PageNow.ToString();

   7:              this.lbPageAll.Text = paging.PageCount.ToString();

   8:              btnFirst.Enabled = true;

   9:              btnUp.Enabled = true;

  10:              btnDown.Enabled = true;

  11:              btnLast.Enabled = true;

  12:              if (paging.PageNow == 1)

  13:              {

  14:                  btnFirst.Enabled = false;

  15:                  btnUp.Enabled = false;

  16:              }

  17:              if (paging.PageNow == paging.PageCount)

  18:              {

  19:                  btnDown.Enabled = false;

  20:                  btnLast.Enabled = false;

  21:              }

  22:          }

  23:          protected void btnFirst_Click(object sender, EventArgs e) { paging.PageNow = 1; }

  24:          protected void btnUp_Click(object sender, EventArgs e) { paging.PageNow –= 1; }

  25:          protected void btnDown_Click(object sender, EventArgs e) { paging.PageNow += 1; }

  26:          protected void btnLast_Click(object sender, EventArgs e) { paging.PageNow = paging.PageCount; }

  27:          protected void btnPaging_Click(object sender, EventArgs e)

  28:          {

  29:              int p = Convert.ToInt32(tbxPageGo.Text.Trim());

  30:              tbxPageGo.Text = string.Empty;

  31:          }

  32:      }

下面的调用页面

   1： <form id="form1" runat="server">

   2:      <asp:Repeater ID="rptList" runat="server">

   3:          <HeaderTemplate>

   4:              <table class="DataList">

   5:                  <tr>

   6:                      <td>

   7:                          <input id="cbx" type="checkbox" onclick="CheckAll(this)" />

   8:                      </td>

   9:                      <td>

  10:                          VIN

  11:                      </td>

  12:                      <td>

  13:                          &nbsp;

  14:                      </td>

  15:                  </tr>

  16:          </HeaderTemplate>

  17:          <ItemTemplate>

  18:              <tr>

  19:                  <td>

  20:                      <input id="<%#Eval("RCB_VIN") %>" name="item" type="checkbox" />

  21:                  </td>

  22:                  <td>

  23:                      <%#Eval("RCB_VIN")%>

  24:                  </td>

  25:                  <td>

  26:                      &nbsp;

  27:                  </td>

  28:              </tr>

  29:          </ItemTemplate>

  30:          <FooterTemplate>

  31:              </table>

  32:          </FooterTemplate>

  33:      </asp:Repeater>

  34:      <uc1:PageingC ID="PageingC1" runat="server" />

  35:      <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

  36:      &nbsp;<asp:Button ID="btnSelect" runat="server" OnClick="btnSelect_Click" Text="查询" />

  37: </form>

下面是代码

   1:      public partial class PalgeList_Test : Pagebase

   2:      {

   3:          PagComponents Plist;

   4:          protected void Page_Load(object sender, EventArgs e)

   5:          {

   6:              Plist = new PagComponents(this.Page, this.ViewState);

   7:              this.PageingC1.paging = Plist;

   8:              Plist.Select = "*";

   9:              Plist.From = "Rep_CarBase";

  10:              Plist.Order = "RCB_ID";

  11:              Plist.AddWhereItem("and RCB_BandID = {0}", "4");

  12:              Plist.AddWhereItem("and RCB_SeriesID = {0}", "208");

  13:          }

  14:          protected void Page_PreRender(object sender, EventArgs e)

  15:          {

  16:              this.rptList.DataSource = Plist.GetDate();

  17:              this.rptList.DataBind();

  18:          }

  19:          protected void btnSelect_Click(object sender, EventArgs e)

  20:          {

  21:              Plist.AddWhereItem("and RCB_VIN like'%'+{0}+'%'", TextBox1);

  22:              Plist.GetParanValue();

  23:          }

  24:      }

提供个完整可运行的解决方案。

下载地址：https://files.cnblogs.com/losting/Pagelist.rar

posted on 2012-02-26 14:59 lostboy 阅读(369) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

lostboy

C# asp.net 分页数据源

数据提取第一步

数据提取第二步

数据提取第三步

导航

公告

lostboy

C# asp.net 分页 数据源

数据提取第一步

数据提取第二步

数据提取第三步

导航

公告

C# asp.net 分页数据源