C#.NET 生成分页SQL代码(NOT IN/TOP TOP/Top MAX)三种方法,支持ACCESS

 

using System;
using System.Web;
using System.Web.UI;
using System.Text.RegularExpressions;
using System.Text;

namespace Pub.Class
{
/// <summary>
/// 生成分页SQL代码
/// </summary>
public class GetPager
{
#region 私有成员
private string _tblName;
private string _fldKey;
private string _fldFields;
private int _PageSize;
private int _PageIndex;
private string _strWhere;
private string _strOrder;
private int _OrderType = - 1 ;
private int _doCount;
private int _State = 0 ;
#endregion

#region 属性
/// <summary>
/// 表名
/// </summary>
public string tblName { set { _tblName = value; } }
/// <summary>
/// 主键
/// </summary>
public string fldKey { set { _fldKey = value; } }
/// <summary>
/// 字段名
/// </summary>
public string fldFields { set { _fldFields = value; } }
/// <summary>
/// 页大小
/// </summary>
public int PageSize { set { _PageSize = value; } }
/// <summary>
/// 当前页
/// </summary>
public int PageIndex { set { _PageIndex = value; } }
/// <summary>
/// 条件
/// </summary>
public string strWhere { set { _strWhere = value; } }
/// <summary>
/// 排序字段 注意：如果使用OrderType时 strOrder只能使用一个字段排序
/// </summary>
public string strOrder { set { _strOrder = value; } }
/// <summary>
/// 排序类型 注意：如果strOrder支持多个字段排序时 不能使用OrderType属性
/// </summary>
public int OrderType { set { _OrderType = value; } }
/// <summary>
/// 不等0时是否输出记录数
/// </summary>
public int doCount { set { _doCount = value; } }
/// <summary>
/// 返回操作状态
/// </summary>
public int State { get { return _State; } }
#endregion

#region 构造器
/// <summary>
/// 构造器
/// </summary>
public GetPager()
{
this ._tblName = "" ;
this ._fldKey = "" ;
this ._fldFields = " * " ;
this ._PageSize = 10 ;
this ._PageIndex = 1 ;
this ._strWhere = "" ;
this ._strOrder = "" ;
this ._doCount = 0 ;
this ._OrderType = - 1 ;
}
/// <summary>
/// 构造器
/// </summary>
/// <param name="tblName"> 表名 </param>
/// <param name="fldKey"> 主键 </param>
/// <param name="fldFields"> 字段名 </param>
/// <param name="PageSize"> 页大小 </param>
/// <param name="PageIndex"> 当前页 </param>
/// <param name="strWhere"> 条件 </param>
/// <param name="strOrder"> 排序 </param>
/// <param name="orderType"> 1ASC/2DESC排序类型 </param>
/// <param name="doCount"> 不等0时是否输出记录数 </param>
public GetPager( string tblName, string fldKey, string fldFields, int PageSize, int PageIndex, string strWhere, string strOrder, int orderType, int doCount)
{
this ._tblName = tblName;
this ._fldKey = fldKey;
this ._fldFields = fldFields;
this ._PageSize = PageSize;
this ._PageIndex = PageIndex;
this ._strWhere = strWhere;
this ._strOrder = strOrder;
this ._OrderType = orderType;
this ._doCount = doCount;
}
#endregion

#region 生成SQL分页字符串
/// <summary>
/// 生成SQL字符串
/// 注意：如果使用OrderType!=-1时 strOrder只能使用一个字段排序,并且调用strSqlSingleOrder方法
/// 否则调用strSqlUseNotIn方法
/// </summary>
/// <returns> 生成SQL字符串 </returns>
public string StrSql() {
if (_OrderType == - 1 ) return StrSqlUseNotIn(); else return StrSqlSingleOrder();
}
/// <summary>
/// 使用not in
/// </summary>
/// <returns></returns>
public string StrSqlUseNotIn()
{
string _strSQL = "" ;

if (_doCount != 0 ){
if (_strWhere != "" )
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName + " where " + _strWhere;
else
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName ;
} else {
if (_strOrder != "" ) _strOrder = " order by " + _strOrder + " " ;
if (_PageIndex == 1 ){
if (_strWhere != "" )
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName + " where " + _strWhere + _strOrder;
else
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName + " " + _strOrder;
} else {
if (_strWhere != "" )
_strSQL = " select top " + _PageSize + " " + _fldFields + " from " + _tblName + " where ( " + _fldKey + " not in (select top " + (_PageSize * (_PageIndex - 1 )) + " " +
_fldKey + " from " + _tblName + " where " + _strWhere + _strOrder + " )) and ( " + _strWhere + " ) " + _strOrder ;
else
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName + " where ( " + _fldKey + " not in (select top " + (_PageSize * (_PageIndex - 1 )) + " " +
_fldKey + " from " + _tblName + " " + _strOrder + " )) " + _strOrder ;
}
}
return _strSQL;
}
/// <summary>
/// 只支持单个字段排序
/// </summary>
/// <returns></returns>
public string StrSqlSingleOrder()
{
string _strSQL = "" , _strTmp = "" , __strOrder,___strOrder;

if (_doCount != 0 ) {
if (_strWhere != "" )
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName + " where " + _strWhere;
else
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName;
} else {
___strOrder = _fldKey;
if (_fldKey.Split( ' . ' ).Length > 1 ) ___strOrder = _fldKey.Split( ' . ' )[ 1 ];
if (_OrderType != 0 ) {
_strTmp = " <(select min " ;
__strOrder = " order by " + _strOrder + " desc " ;
} else {
_strTmp = " >(select max " ;
__strOrder = " order by " + _strOrder + " asc " ;
}
if (_PageIndex == 1 ){
if (_strWhere != "" )
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName + " where " +
_strWhere + " " + __strOrder;
else
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName + " " + __strOrder;
} else {
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName +
" where " + _fldKey + _strTmp + " ( " + ___strOrder + " ) from (select top " +
((_PageIndex - 1 ) * _PageSize) + " " + _fldKey + " from " + _tblName + " " + __strOrder + " ) as tblTmp) " + __strOrder;
if (_strWhere != "" )
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName +
" where " + _fldKey + _strTmp + " ( " + ___strOrder + " ) from (select top " +
((_PageIndex - 1 ) * _PageSize) + " " + _fldKey + " from " + _tblName + " where " + _strWhere + " " +
__strOrder + " ) as tblTmp) and " + _strWhere + " " + __strOrder;
}
}
return _strSQL;
}
/// <summary>
/// 没使用not in
/// </summary>
/// <returns></returns>
public string StrSqlNoUseNotIn()
{
string _strSQL = "" , __strOrder1 = "" , __strOrder2 = "" ;

if (_doCount != 0 ){
if (_strWhere != "" )
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName + " where " + _strWhere;
else
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName ;
} else {
if (_strOrder != "" ) {
string [] OrderArr = _strOrder.Split( ' , ' );
for ( int i = 0 ; i <= OrderArr.Length - 1 ; i ++ ) {
string [] strOrderArr1 = OrderArr[i].Trim().Split( ' ' );
string [] strOrderArr2 = OrderArr[i].Trim().Split( ' . ' );
string __strOrder3 = (strOrderArr1.Length == 1 ) ? strOrderArr1[ 0 ] + " desc " : strOrderArr1[ 0 ] + (strOrderArr1[strOrderArr1.Length - 1 ] == " desc " ? " asc " : " desc " );
string [] strOrderArr3 = __strOrder3.Trim().Split( ' . ' );
__strOrder1 = (strOrderArr3.Length == 1 ) ? __strOrder1 + strOrderArr3[ 0 ] : __strOrder1 + strOrderArr3[ 1 ] + " , " ;
__strOrder2 = (strOrderArr2.Length == 1 ) ? __strOrder2 + strOrderArr2[ 0 ] : __strOrder2 + strOrderArr2[ 1 ] + " , " ;
}
__strOrder1 = " order by " + __strOrder1.TrimEnd( ' , ' );
__strOrder2 = " order by " + __strOrder2.TrimEnd( ' , ' );
_strOrder = " order by " + _strOrder;
}
if (_PageIndex == 1 ){
if (_strWhere != "" )
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName + " where " + _strWhere + _strOrder;
else
_strSQL = " select top " + _PageSize.ToString() + " " + _fldFields + " from " + _tblName + " " + _strOrder;
} else {
if (_strWhere != "" ) _strWhere = " where " + _strWhere;
_strSQL = " select * from ( " +
" SELECT TOP " + _PageSize.ToString() + " * from ( " +
" select top " + (_PageSize * _PageIndex) + " " + _fldFields + " " +
" from " + _tblName + " " +
" " + _strWhere + " " +
" " + _strOrder + " " +
" ) as _a " + __strOrder1 + " " +
" ) as _b " + __strOrder2 + " " ;
}
}
return _strSQL;
}
/// <summary>
/// MySql分页
/// </summary>
/// <returns></returns>
public string StrMySql() {
string _strSQL = "" ;

if (_doCount != 0 ){
if (_strWhere != "" )
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName + " where " + _strWhere;
else
_strSQL = " select count( " + this ._fldKey + " ) as Total from " + _tblName ;
} else {
if (_strOrder != "" ) _strOrder = " order by " + _strOrder + " " ;
if (_PageIndex == 1 ){
if (_strWhere != "" )
_strSQL = " select " + _fldFields + " from " + _tblName + " where " + _strWhere + _strOrder + " limit 0, " + _PageSize.ToString();
else
_strSQL = " select " + _fldFields + " from " + _tblName + " " + _strOrder + " limit 0, " + _PageSize.ToString();
} else {
if (_strWhere != "" )
_strSQL = " select " + _fldFields + " from " + _tblName + " where " + _strWhere + _strOrder + " limit " + ((_PageIndex - 1 ) * _PageSize) + " , " + _PageSize.ToString();
else
_strSQL = " select " + _fldFields + " from " + _tblName + " " + _strOrder + " limit " + ((_PageIndex - 1 ) * _PageSize) + " , " + _PageSize.ToString();
}
}
return _strSQL;
}
#endregion
}
}

 

转载于:https://www.cnblogs.com/livexy/archive/2010/07/05/1771685.html