构建数据库分页的代码逻辑类方法
2017-11-12 13:44:35 访问(1788) 赞(0) 踩(0)
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
namespace eKing.SzdfHrCallApp.Classes.Szdf.DB
{
public class SqlPage
{
public readonly static SqlPage instance = new SqlPage();
public SqlPage()
{
}
public enum EmDataBaseHelper
{
SqlServer = 1,
Oracle
}
/// <summary>
/// Top Select 相关的语句
/// </summary>
/// <param name="select"></param>
/// <param name="from"></param>
/// <param name="where"></param>
/// <param name="orderby"></param>
/// <param name="topNum"></param>
/// <param name="_emDataBaseHelper"></param>
/// <returns></returns>
public string TopSelect
(
string select,
string from,
string where,
string orderby,
int topNum,
EmDataBaseHelper _emDataBaseHelper
)
{
StringBuilder theResult = new StringBuilder();
switch (_emDataBaseHelper)
{
case EmDataBaseHelper.Oracle:
// SQL = "select slx.* from (" + SQL + ")slx where rownum <= " + top.ToString();
theResult.Append("select slx.* from (");
theResult.Append("select ");
theResult.Append(select);
theResult.Append(" from ");
theResult.Append(from);
if (where != null && where.Length > 0)
{
theResult.Append(" where ");
theResult.Append(where);
}
if (orderby != null && orderby.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(orderby);
}
theResult.Append(")slx where rownum <= ");
theResult.Append(topNum.ToString());
break;
default:
theResult.Append("select top ");
theResult.Append(topNum.ToString());
theResult.Append(" ");
theResult.Append(select);
theResult.Append(" from ");
theResult.Append(from);
if (where != null && where.Length > 0)
{
theResult.Append(" where ");
theResult.Append(where);
}
if (orderby != null && orderby.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(orderby);
}
break;
}
return theResult.ToString();
}
/// <summary>
/// Oracle分页用的select语句
/// </summary>
/// <param name="SQL"></param>
/// <param name="iPageIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public string OraclePageListSelect
(
string SQL,
int iPageIndex,
int iPageSize
)
{
if (iPageIndex == 0)
return "select slx.* from (" + SQL + ")slx where rownum <= " + iPageSize.ToString();
int startIndex = iPageIndex * iPageSize;
return "select * from (select a.*, rownum as p__SqlRowNumberValue from ("
+ SQL + ")a where rownum <= " + (startIndex + iPageSize).ToString() + ") where p__SqlRowNumberValue > " + startIndex;
}
/// <summary>
/// Oracle分页用的select语句
/// </summary>
/// <param name="SQL"></param>
/// <param name="startIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public string OracleStartIndexListSelect
(
string SQL,
int startIndex,
int iPageSize
)
{
if (startIndex == 0)
return "select slx.* from (" + SQL + ")slx where rownum <= " + iPageSize.ToString();
return "select * from (select a.*, rownum as p__SqlRowNumberValue from ("
+ SQL + ")a where rownum <= " + (startIndex + iPageSize).ToString() + ") where p__SqlRowNumberValue > " + startIndex;
}
/// <summary>
/// SQLServer2005以上的分页算法
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pageIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public string SqlServerRowNumberPageListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
int pageIndex,
int iPageSize
)
{
int startIndex = pageIndex * iPageSize;
return SqlServerRowNumberStartIndexListSelect
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult,
startIndex,
iPageSize
);
}
/// <summary>
/// SQLServer2005以上的分页算法
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="startIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public string SqlServerRowNumberStartIndexListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
int startIndex,
int iPageSize
)
{
// select * from (select ID,TheCode,CurID,CreateTime,UpdateTime,ROW_NUMBER() over (order by ID desc,TheCode asc) as p__SqlRowNumberValue from UTB_CODEX_MEMORY where ID >= @p1)slx where slx.p__SqlRowNumberValue > 10 and slx.p__SqlRowNumberValue <= 20
int toPageNum = iPageSize;
if (toPageNum != int.MaxValue)
toPageNum += startIndex;
StringBuilder theResult = new StringBuilder();
theResult.Append("select * from (select ");
theResult.Append(sqlSelectResult + ",");
theResult.Append("ROW_NUMBER() over (");
theResult.Append("order by ");
theResult.Append(sqlOrderResult);
theResult.Append(") as p__SqlRowNumberValue from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult.Length != 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
theResult.Append(")slx where ");
theResult.Append("slx.p__SqlRowNumberValue > ");
theResult.Append(startIndex.ToString());
theResult.Append(" and slx.p__SqlRowNumberValue <= ");
theResult.Append(toPageNum.ToString());
return theResult.ToString();
}
/// <summary>
/// SQLServer2000版本（或Access版本的分页算法）
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="pageIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public string SqlNotInModelPageListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int pageIndex,
int iPageSize
)
{
int startIndex = pageIndex * iPageSize;
return SqlNotInModelStartIndexListSelect(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult, pkName, startIndex, iPageSize);
}
/// <summary>
/// SQLServer2000版本（或Access版本的分页算法）
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="startIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public string SqlNotInModelStartIndexListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int startIndex,
int iPageSize
)
{
StringBuilder theResult = new StringBuilder();
if (startIndex == 0)
{
theResult.Append("select ");
if (iPageSize != int.MaxValue)
{
theResult.Append("top " + iPageSize.ToString() + " ");
}
theResult.Append(sqlSelectResult);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult != null && sqlWhereResult.Length > 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
if (sqlOrderResult != null && sqlOrderResult.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
return theResult.ToString();
}
// select top 10 ID,TheCode,CurID,CreateTime,UpdateTime from UTB_CODEX_MEMORY where ID not in (select top 10 ID from UTB_CODEX_MEMORY order by ID desc,TheCode asc) order by ID desc,TheCode asc
theResult.Append("select ");
if (iPageSize != int.MaxValue)
{
theResult.Append("top " + iPageSize.ToString() + " ");
}
theResult.Append(sqlSelectResult);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult.Length != 0)
{
theResult.Append(" where ");
theResult.Append("(");
theResult.Append(sqlWhereResult);
theResult.Append(") and ");
}
else
{
theResult.Append(" where ");
}
theResult.Append(pkName);
theResult.Append(" not in (");
theResult.Append("select top ");
theResult.Append(startIndex.ToString());
theResult.Append(" ");
theResult.Append(pkName);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult.Length != 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
if (sqlOrderResult.Length != 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
theResult.Append(")");
if (sqlOrderResult.Length != 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
return theResult.ToString();
}
/// <summary>
/// 合并成Select的SQL语句
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <returns></returns>
public string ToSelectSQL
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult
)
{
StringBuilder theResult = new StringBuilder();
theResult.Append("select ");
theResult.Append(sqlSelectResult);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult != null && sqlWhereResult.Length > 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
if (sqlOrderResult != null && sqlOrderResult.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
return theResult.ToString();
}
/// <summary>
/// 合并成Count的SQL语句
/// </summary>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <returns></returns>
public string ToCountSQL
(
string sqlFromResult,
string sqlWhereResult
)
{
StringBuilder theResult = new StringBuilder();
theResult.Append("select count(1) from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult != null && sqlWhereResult.Length > 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
return theResult.ToString();
}
/// <summary>
///
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="pageIndex"></param>
/// <param name="iPageSize"></param>
/// <param name="_emDataBaseHelper"></param>
/// <param name="_dataBaseVersion"></param>
/// <returns></returns>
public string PageList
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int pageIndex,
int iPageSize,
EmDataBaseHelper _emDataBaseHelper,
int _dataBaseVersion
)
{
if (pageIndex == 0 && iPageSize == int.MaxValue)
return ToSelectSQL(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult);
switch (_emDataBaseHelper)
{
case EmDataBaseHelper.Oracle:
return OraclePageListSelect(ToSelectSQL(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult), pageIndex, iPageSize);
case EmDataBaseHelper.SqlServer:
if (_dataBaseVersion ==2)
{
return SqlNotInModelPageListSelect(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult, pkName, pageIndex, iPageSize);
}
else
{
return SqlServerRowNumberPageListSelect(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult, pageIndex, iPageSize);
}
default:
throw new Exception
(
"方法："
+ MethodBase.GetCurrentMethod().ReflectedType.FullName
+ " "
+ MethodBase.GetCurrentMethod().ToString()
+ " 发生异常："
+ "枚举（"
+ _emDataBaseHelper.GetType().FullName
+ "."
+ _emDataBaseHelper.ToString()
+ "）未知，对应的代码尚未实现。"
);
}
}
/// <summary>
///
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="startIndex"></param>
/// <param name="iPageSize"></param>
/// <param name="_emDataBaseHelper"></param>
/// <param name="_dataBaseVersion"></param>
/// <returns></returns>
public string StartIndexList
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int startIndex,
int iPageSize,
EmDataBaseHelper _emDataBaseHelper,
int _dataBaseVersion
)
{
if (startIndex == 0 && iPageSize == int.MaxValue)
return ToSelectSQL
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult
);
switch (_emDataBaseHelper)
{
case EmDataBaseHelper.Oracle:
return OracleStartIndexListSelect
(
ToSelectSQL
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult
),
startIndex,
iPageSize
);
case EmDataBaseHelper.SqlServer:
if (_dataBaseVersion == 2)
{
return SqlNotInModelStartIndexListSelect
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult,
pkName,
startIndex,
iPageSize
);
}
else
{
return SqlServerRowNumberStartIndexListSelect
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult,
startIndex,
iPageSize
);
}
default:
throw new Exception
(
"方法："
+ MethodBase.GetCurrentMethod().ReflectedType.FullName
+ " "
+ MethodBase.GetCurrentMethod().ToString()
+ " 发生异常："
+ "枚举（"
+ _emDataBaseHelper.GetType().FullName
+ "."
+ _emDataBaseHelper.ToString()
+ "）未知，对应的代码尚未实现。"
);
}
}
}
}
上一条：
下一条：
相关评论
发表评论