·您现在的位置： 云翼网络 >> 文章中心 >> 网站建设 >> 网站建设开发 >> ASP.NET网站开发 >> 将以前写的SQL2分查找法通用分页存储过程算法 改成.net类实现
- HTML5入门（一）
- 在Java和.NET平台的加密术比较
- ASP.NET - Panel Web 服务器控件概述
- Ajax的post方式提交数据
- ASP.NET 表单验证方法与客户端(浏览器)服务器交互机制的故事
- 要经营我的园子了！！！
- Asp.net+Xml实现无数据库论坛一点即通
- C#实现像Git那样计算Hash值
- 冒泡排序（C#）
- ASP.NET将原始图片按照指定尺寸等比例缩放显示图片
- ASP.NET 2.0里轻松获取数据库连接统计数据
- 关于asp.net c#中对cookie的操作
- asp：get请求写法
- AD帐户操作C#示例代码（一）——导入用户信息
- 15款提高工作效率的Web项目管理工具
- 在asp.net mvc中如何使用Grid++ Report (锐浪报表)
- JavaScript大杂烩0
- 【ASP.NET进阶】根据IP地址进行百度地图定位
- ASP.NET - HyperLink Web 服务器控件概述
- 生成四位随机数，用于登录验证码制作。
- ASP.NET MVC3-Music Store中英文教程 [下载]
- ReportingServices的一些问题
- 你应该了解的jquery 验证框架
- ASP.NET2.0下含有CheckBox的GridView删除选定记录实例
- 免费和开源的权限管理系统
- ADO.NET操纵数据库
- ASP.NET根据IP获取省市地址
- 在webservice中传递Hashtable
- 吐槽一下万网空间
- DataSet、DataTable、DataRow区别
将以前写的SQL2分查找法通用分页存储过程算法 改成.net类实现
using System;
namespace CountryPark.DAL
...{
/**//**//**//// <summary>
/// PageList 的摘要说明。
/// </summary>
public sealed class PageList
...{
static PageList()
...{
}
/**//**//**//// <summary>
/// 分页查询数据记录总数获取
/// </summary>
/// <param name="_tbName">----要显示的表或多个表的连接</param>
/// <param name="_ID">----主表的主键</param>
/// <param name="_strCondition">----查询条件,不需where</param>
/// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
/// <returns></returns>
public static string getPageListCounts(string _tbName, string _ID, string _strCondition, int _Dist)
...{
//---存放取得查询结果总数的查询语句
//---对含有DISTINCT的查询进行SQL构造
//---对含有DISTINCT的总数查询进行SQL构造
string strTmp="", SqlSelect="", SqlCounts="";
if (_Dist == 0)
...{
SqlSelect = "SELECT ";
SqlCounts = "COUNT(*)";
}
else
...{
SqlSelect = "SELECT DISTINCT ";
SqlCounts = "COUNT(DISTINCT "+ _ID +")";
}
if (_strCondition == string.Empty)
...{
strTmp = SqlSelect +" @Counts="+ SqlCounts +" FROM "+ _tbName;
}
else
...{
strTmp = SqlSelect +" @Counts="+ SqlCounts +" FROM "+ " WHERE (1=1) "+ _strCondition;
}
return strTmp;
}
/**//**//**//// <summary>
/// 获取分页数据查询SQL
/// </summary>
/// <param name="_tbName">----要显示的表或多个表的连接</param>
/// <param name="_fldName">----要显示的字段列表</param>
/// <param name="_PageSize">----每页显示的记录个数</param>
/// <param name="_Page">----要显示那一页的记录</param>
/// <param name="_PageCount">----查询结果分页后的总页数</param>
/// <param name="_Counts">----查询到的记录数</param>
/// <param name="_fldSort">----排序字段列表或条件(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')</param>
/// <param name="_Sort">----排序方法，0为升序，1为降序</param>
/// <param name="_strCondition">----查询条件,不需where</param>
/// <param name="_ID">----主表的主键</param>
/// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
/// <returns></returns>
public static string getPageListSql(string _tbName, string _fldName, int _PageSize, int _Page, out int _PageCount, int _Counts, string _fldSort, int _Sort, string _strCondition, string _ID, int _Dist)
...{
string strTmp=""; //---strTmp用于返回的SQL语句
string SqlSelect="", strSortType="", strfsortType="";
if (_Dist == 0)
...{
SqlSelect = "SELECT ";
}
else
...{
SqlSelect = "SELECT DISTINCT ";
}
if (_Sort == 0)
...{
strFSortType = " ASC";
strSortType = " DESC";
}
else
...{
strFSortType = " DESC";
strSortType = " ASC";
}
// ----取得查询结果总数量-----
int tmpCounts = 1;
if (_Counts != 0)
...{
tmpCounts = _Counts;
}
// --取得分页总数
_PageCount = (tmpCounts + _PageSize - 1)/_PageSize;
// /**//**当前页大于总页数 取最后一页**/
if (_Page > _PageCount)
...{
_Page = _PageCount;
}
if (_Page <= 0)
...{
_Page = 1;
}
// --/*-----数据分页2分处理-------*/
int pageIndex = tmpCounts/_PageSize;
int lastCount = tmpCounts%_PageSize;
if (lastCount > 0)
...{
pageIndex = pageIndex + 1;
}
else
...{
lastCount = _PageSize;
}
if (_strCondition == string.Empty) // --没有设置显示条件
...{
if (pageIndex < 2 || _Page <= (pageIndex/2 + pageIndex%2)) //--前半部分数据处理
...{
if (_Page == 1)
...{
strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +" ORDER BY "+ _fldSort +" "+ strFSortType;
}
else
...{
strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +" WHERE "+ _ID +" <(SELECT MIN("+ _ID +") FROM ("+ SqlSelect +" TOP "+ _PageSize*(_Page-1) +" "+ _ID +" FROM "+ _tbName +
" ORDER BY "+ _fldSort +" "+ strFSortType +") AS TBMinID) ORDER BY "+ _fldSort +" "+ strFSortType;
}
}
else
...{
_Page = pageIndex - _Page + 1; //后半部分数据处理
if (_Page <= 1) //--最后一页数据显示
...{
strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ lastCount +" "+ _fldName +" FROM "+ _tbName +" ORDER BY "+ _fldSort +" "+ strSortType +") AS TempTB"+ " ORDER BY "+ _fldSort +" "+ strFSortType;
}
else
...{
strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +
" WHERE "+ _ID +" >(SELECT MAX("+ _ID +") FROM("+ SqlSelect +" TOP "+ (_PageSize*(_Page-2)+lastCount) +" "+ _ID +" FROM "+ _tbName +
" ORDER BY "+ _fldSort +" "+ strSortType +") AS TBMaxID) ORDER BY "+ _fldSort +" "+ strSortType +") AS TempTB ORDER BY "+ _fldSort +" "+ strFSortType;
}
}
}
else // --有查询条件
...{
if (pageIndex < 2 || _Page <=(pageIndex/2 + pageIndex%2))//--前半部分数据处理
...{
if (_Page == 1)
...{
strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +"WHERE 1=1 "+ _strCondition +" ORDER BY "+ _fldSort +" "+ strFSortType;
}
else
...{
strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +
" WHERE "+ _ID +" <(SELECT MIN("+ _ID +") FROM ("+ SqlSelect +" TOP "+ (_PageSize*(_Page-1)) +" "+ _ID +" FROM " +_tbName +
" WHERE 1=1 "+ _strCondition +" ORDER BY "+ _fldSort +" "+ strFSortType +") AS TBMaxID) "+ _strCondition +
" ORDER BY "+ _fldSort +" "+ strFSortType;
}
}
else //--后半部分数据处理
...{
_Page = pageIndex-_Page+1;
if (_Page <= 1) //--最后一页数据显示
...{
strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ lastCount +" "+ _fldName +" FROM "+ _tbName +
" WHERE 1=1 "+ _strCondition +" ORDER BY "+ _fldSort +" "+ strSortType +") AS TempTB ORDER BY "+ _fldSort +" "+ strFSortType;
}
else
...{
strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +
" WHERE "+ _ID +" >(SELECT MAX("+ _ID +") FROM("+ SqlSelect +" TOP "+ (_PageSize*(_Page-2)+ lastCount) +" "+ _ID +" FROM "+ _tbName +
" WHERE 1=1 "+ _strCondition +" ORDER BY "+ _fldSort +" "+ strSortType +") AS TBMaxID) "+ _strCondition +
" ORDER BY "+ _fldSort +" "+ strSortType +") AS TempTB ORDER BY "+ _fldSort +" "+ strFSortType;
}
}
}
return strTmp;
}
}
}
--以上代码是针对之前写的TOP MAX模式的分页存储过程修改
--以上分页算法对SQL SERVER 和 access同样有效
参见:http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html
//调用函数例子
public IList getParkDataList(string key, int curPage, out int pageCount, int pageSize, int Counts)
...{
IList list = new ArrayList();
string SECLECT_FIELD = "T_Park.ParkID, T_Park.ParkTitle, T_Park.ParkLetter, T_ParkArea.AreaName, T_ParkType.ParkTypeName ";
string SECLECT_TABLE = "T_ParkType INNER JOIN (T_ParkArea INNER JOIN T_Park ON T_ParkArea.ParkAreaID = T_Park.ParkAreaID) ON T_ParkType.ParkTypeID = T_Park.ParkTypeID";
string SECLECT_CONDITION = string.Empty;
if (key != string.Empty)
...{
SECLECT_CONDITION = " AND T_Park.ParkTitle like '%"+ key +"%'";
}
string SELECT_ID = "ParkID";
string SELECT_FLDSORT = "ParkID";
int SELECT_SORT = 1;
int SELECT_DIST = 0;
string SQL = PageList.getPageListSql(SECLECT_TABLE, SECLECT_FIELD, pageSize, curPage, out pageCount, Counts, SELECT_FLDSORT, SELECT_SORT, SECLECT_CONDITION, SELECT_ID, SELECT_DIST);
//string strCondition;
OleDb db = new OleDb();
ParkBE park;
using(OleDbDataReader dr = (OleDbDataReader)db.ExecuteReader(CommonFun.GetConnectionString(), CommandType.Text, SQL))
...{
while (dr.Read())
...{
park = new ParkBE();
park.ParkID = Convert.ToInt32(dr[0]);
park.ParkTitle = dr[1].ToString();
park.ParkLetter = dr[2].ToString();
park.ParkAreaName = dr[3].ToString();
park.ParkTypeName = dr[4].ToString();
list.Add(park);
}
}
return list;
}
http://blog.csdn.net/todaywlq/archive/2007/01/29/1497418.aspx
- 上一篇文章： .NET自动字符编码识别程序库 NChardet
- 下一篇文章： 关于URL传递日文字符乱码问题的解决方案