asp.net原创分页存储过程和分页实例

作者：袖梨 2022-06-25

我们先把子sql存储过程拿出来

分页存储过程。

代码如下 复制代码

CREATE PROCEDURE [dbo].[proc_SplitPage]
-- Add the parameters for the stored procedure here
@tblName varchar(255), -- 表名
@strFields varchar(1000) = '*', -- 需要返回的列，默认*
@strOrder varchar(255)='', -- 排序的字段名，必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize int = 10, -- 页尺寸,默认10
@PageIndex int = 1, -- 页码,默认1
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000)
if @strWhere !=''
set @strWhere=' where '+@strWhere

set @strSQL=
'SELECT '+@strFields+' FROM ('+
'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS

pos,'+@strFields+' '+
'FROM '+@tblName+' '+@strWhere+
') AS sp
WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)


exec (@strSQL)


下面是个分页类pager{}

代码如下 复制代码

public class Pager
{
private string _firstPageText;
///


/// 最前一页文字显示 默认显示为"首页"
///
public string FirstPageText
{
get {
return string.IsNullOrEmpty(_firstPageText)? "首页" : _firstPageText;
}
set {
_firstPageText = value;
}
}

private string _prePageText;
///


/// 上一页文字显示 默认显示为"上一页"
///
public string PrePageText
{
get
{
return string.IsNullOrEmpty(_prePageText) ? "上一页" : _prePageText;
}
set
{
_prePageText = value;
}
}

private string _nextPageText;
///


/// 下一页文字显示 默认显示为"下一页"
///
public string NextPageText
{
get
{
return string.IsNullOrEmpty(_nextPageText) ? "下一页" : _nextPageText;
}
set
{
_nextPageText = value;
}
}

private string _lastPageText;
///


/// 末页文字显示 默认显示为"末页"
///
public string LastPageText
{
get
{
return string.IsNullOrEmpty(_lastPageText) ? "末页" : _lastPageText;
}
set
{
_lastPageText = value;
}
}

///


/// 总记录数
///
public int RecordCount { get; set; }

private int _pagesize=15;
///


/// 每页分页尺寸 默认为15
///
public int PageSize {
get {
return _pagesize == 0 ? 15 : _pagesize;
}set{
_pagesize = value;
}
}

private int _pageIndex=1;
///


/// 当前页码
///
public int PageIndex {
get {
return _pageIndex == 0 ? 1 : _pageIndex;
}
set {
_pageIndex = value;
}
}

private int _maxShowPageSize = 10;
///


/// 显示页码列表的最大个数 默认为10
///
public int MaxShowPageSize {
get {
return _maxShowPageSize;
}
set {
_maxShowPageSize = value;
}
}

private string _queryStringName;
///


/// 页码在浏览器中传值的名称 默认为page
///
public string QueryStringName {
get {
return string.IsNullOrEmpty(_queryStringName)? "page" : _queryStringName;
}
set {
_queryStringName = value;
}
}

///


/// 页面的URL
///
public string URL {
get {
string url = HttpContext.Current.Request.Url.AbsoluteUri;//当前页面绝对路

径
if (EnableUrlRewriting)//使用url重写
{
url=url.Substring(0, url.LastIndexOf("/") + 1);//获取当前页面的目录路

径
url += UrlRewritePattern;
}
else {//普通带问号的页面传值
//demo.aspx
//demo.aspx?a=1
//demo.aspx?page=1
//demo.aspx?a=2&page=1
if (url.Contains("aspx?"))
{
if (Regex.IsMatch(url,@"page=[0-9]*$",RegexOptions.IgnoreCase))//

如果存在page=*的字符串
{
url = Regex.Replace(url, @"page=[0-9]*$", "",

RegexOptions.IgnoreCase);//替换掉page=*的字符串
}
url += QueryStringName + "={0}";
}
else {
url += "?" + QueryStringName + "={0}";
}
}
return url;
}
}

private bool _enableUrlRewriting;
///


/// URL是否重写 默认为flase
///
public bool EnableUrlRewriting {
get {
return (object)_enableUrlRewriting == null ? false : _enableUrlRewriting;
}
set {
_enableUrlRewriting = value;
}
}

///


/// 页面URL重写规则，将页码传值用{0}来代替 比如list-1-{0}.html,启用该功能需要将

EnableUrlRewriting属性设为true
///
public string UrlRewritePattern { get; set; }

private string _className;
///


/// 分页容器的css名称
///
public string ClassName {
get {
return string.IsNullOrEmpty(_className) ? "paginator" : _className;
}set{
_className = value;
}
}

private string _currentPageCss;
///


/// 当前页面按钮css
///
public string CurrentPageButtonCss {
get {
return string.IsNullOrEmpty(_currentPageCss) ? "cpb" : _currentPageCss;
}set{
_currentPageCss = value;
}
}

private bool _showSpanText;
///


/// Span 标签中文字信息是否显示 默认为false不显示
///
public bool ShowSpanText {
get {
return (object)_showSpanText == null ? false : _showSpanText;
}
set {
_showSpanText = value;
}
}

private string _spanTextClass;
///


/// 分页文字描述span标签css
///
public string SpanTextClass {
get {
return string.IsNullOrEmpty(_spanTextClass) ? "stc" : _spanTextClass;
}
set {
_spanTextClass = value;
}
}

private string _submitButtonText;
///


/// 确定按钮文字显示 默认显示"确定"
///
public string SubmitButtonText {
get {
return string.IsNullOrEmpty(_submitButtonText) ? "确定" :

_submitButtonText;
}
set {
_submitButtonText = value;
}
}
}

分页显示html代码的拼接方法：

代码如下 复制代码

public class SplitManager
{
public static string AspNetPagers(Pager pager)
{
StringBuilder sb = new StringBuilder();
string attr="";
int pagecount = 0;//当前页面的总层数
int floorcount = 0;//分页的总层数
int currentLastPage = 0;//当前最后一页的页码
int pageNum = pager.RecordCount / pager.PageSize + 1;//总页数 1~24
sb.AppendFormat("

n", pager.ClassName);
attr=pager.PageIndex==1?"disabled=disabled":"";//标志当前页第一页是否相等 来控

制前俩个按钮的有效性
sb.AppendFormat(GetAHtml(attr,string.Format

(pager.URL,1),pager.FirstPageText));//添加最前一页的代码
sb.AppendFormat(GetAHtml(attr, string.Format(pager.URL, pager.PageIndex-1),

pager.PrePageText));//添加上一页的代码
pagecount = pager.PageIndex/pager.MaxShowPageSize;//当前页数 0~1~2
pagecount = pager.PageIndex % pager.MaxShowPageSize == 0 ? pagecount - 1 :

pagecount;//清除当 当前页数为分页页码数的整数倍页时除数多一的状况
floorcount = pageNum / pager.MaxShowPageSize;//页面层数 0~1~2
currentLastPage=pageNum

pageNum:pager.MaxShowPageSize * (pagecount + 1);
if (pager.PageIndex > pager.MaxShowPageSize)//当当前序号大于每页页码个数时显示

再前端...
{
sb.AppendFormat(GetAHtml("", string.Format(pager.URL,

pager.MaxShowPageSize * pagecount), "..."));
}
for (int i = pager.MaxShowPageSize * pagecount + 1; i {
if (i == pager.PageIndex)//判断循环页面是否为当前页
{
sb.AppendFormat(GetSpanHtml(i, pager.CurrentPageButtonCss));
}
else {
sb.AppendFormat(GetAHtml("", string.Format(pager.URL, i),i.ToString

()));
}
}
if (pager.PageIndex

第二页页码时显示在后端...
{
sb.AppendFormat(GetAHtml("", string.Format(pager.URL,

pager.MaxShowPageSize * (pagecount + 1) + 1), "..."));
}
attr = pager.PageIndex == pageNum ? "disabled=disabled" : "";//标志当前页最后

一页是否相等 来控制后俩个按钮的有效性
sb.AppendFormat(GetAHtml(attr, string.Format(pager.URL, pager.PageIndex+1),

pager.NextPageText));//添加后一页的代码
sb.AppendFormat(GetAHtml(attr, string.Format(pager.URL, pageNum),

pager.LastPageText));//添加最后一页的代码
if (pager.ShowSpanText)//是否显示分页文字span标签显示
{
sb.AppendFormat("共{0}页,每页

{1}条记录 n", pageNum, pager.PageSize);
sb.AppendFormat("到第

style="width:20px;" name="jump" value="{0}" />页", pager.PageIndex == pageNum ?

pageNum : pager.PageIndex + 1);
sb.AppendFormat("n");
sb.Append(GetJumpScript(pager.URL));//添加按钮跳转的javascript代码
}
sb.AppendFormat("

");//
return sb.ToString();
}

///


/// get the html of a label
///
/// a's title
/// the url of a
/// the attribute
/// return html string
private static string GetAHtml(string attr,string url,string title)
{
return "n";
}

///


/// get the html of a label
///
/// the content of span
/// Class style Name
/// return html string
private static string GetSpanHtml(int num, string className)
{
return "" + num + "n";
}

///


/// 获取跳转的javascript代码
///
/// 当前分页的url规则
/// 返回一个javascript代码
private static string GetJumpScript(string url)
{
string scriptstr = "n";
return scriptstr;
}

}

最精简必要的几个参数传进去就能显示分页效果了：

代码如下 复制代码

protected string str = "";
protected void Page_Load(object sender, EventArgs e)
{
Pager pager = new Pager() { RecordCount = 350,
PageSize = 15,
MaxShowPageSize=10,
PageIndex = Convert.ToInt32(Request.QueryString["page"]),
ShowSpanText=true};
str = SplitManager.AspNetPagers(pager);

}

供测试的css：

width:10px;_width:10px;
margin-right:5px;
text-align:center;
white-space:nowrap;
font-size:12px;
font-family:
Arial,SimSun;
padding:0 3px;}
.paginator .stc{color:#999;margin-left:20px;}
.paginator .stc a{margin-left:10px;}
/*分页样式控制的结束*/

同时配合为了配合分页，再给出一个DataTable转泛型列表的一个方法和一个分页存储过程。

此转换方法需配合相应的实体类，并且实体类中需对象相应表的字段名，不区分大小写。

代码如下 复制代码

#region DataTable To List/Model

///


/// DataTable To List
///
/// object type
/// DataTable
/// return a List Model type
public static List DataTableToObjectList(DataTable dt) where T : new()
{
DataRowCollection drc = dt.Rows;
int columncount = drc.Count;
List result = new List(); //declare the generic type of return
Type type = typeof(T);

PropertyInfo[] propertys = type.GetProperties

(BindingFlags.IgnoreCase|BindingFlags.Instance|BindingFlags.Public|BindingFlags.SetPropert

y); //get the collections of the model
foreach (DataRow r in drc)
{
result.Add(DataRowToObjectModel(r, propertys));
}
return result;

}

///


/// DataRow To a Model
///
/// the type of Model
/// DataRow
/// the object to Model
/// return a Model Type
private static T DataRowToObjectModel(DataRow r, PropertyInfo[] propertys)

where T : new()
{
T t = new T();
for (int i = 0; i {
object obj = r[propertys[i].Name];
if (obj != null)
{
if (propertys[i].PropertyType == typeof(int))
propertys[i].SetValue(t, PublicMethod.GetInt(obj), null);
if (propertys[i].PropertyType == typeof(string))
propertys[i].SetValue(t, obj.ToString(), null);
if (propertys[i].PropertyType == typeof(DateTime))
propertys[i].SetValue(t, PublicMethod.GetDateTime(obj), null);
}
}
return t;
}
#endregion


好了，这样就全部KO了。

相关文章

精彩推荐