/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
if (cmd.PageSize == 0)//正常模式
{
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else//分页模式
{
/*
* SELECT * FROM
* (
* SELECT A.*, ROWNUM RN
* FROM (SELECT * FROM TABLE_NAME) A
* WHERE ROWNUM <= 40
* )
* WHERE RN >= 21
*/
SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName);
innestCommand.QueryFields = cmd.QueryFields;
innestCommand.SqlJoins = cmd.SqlJoins;
innestCommand.SqlWhere = cmd.SqlWhere;
innestCommand.GroupByColumns = cmd.GroupByColumns;
innestCommand.SqlHaving = cmd.SqlHaving;
innestCommand.SqlOrders = cmd.SqlOrders;
SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T");
innerCommand.SqlWhere = SqlCondition.LessThanOrEqual("ROWNUM", realPageIndex * cmd.PageSize);
innerCommand.InternalQuerys(cmd.QueryFields.ToArray());
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction("ROWNUM", "RN"));
sb.AppendSelectFrom(innerCommand.ToString("T"), true);
sb.AppendWhere(SqlCondition.GreaterThanOrEqual("RN", (realPageIndex - 1) * cmd.PageSize + 1));
}
return(sb.ToString());
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
if (cmd.PageSize == 0)//正常模式
{
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else//分页模式
{
/*
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
*/
SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName);
innestCommand.QueryFields = cmd.QueryFields;
innestCommand.SqlJoins = cmd.SqlJoins;
innestCommand.SqlWhere = cmd.SqlWhere;
innestCommand.GroupByColumns = cmd.GroupByColumns;
innestCommand.SqlHaving = cmd.SqlHaving;
innestCommand.SqlOrders = cmd.SqlOrders;
SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T");
innerCommand.SqlWhere = SqlCondition.LessThanOrEqual("ROWNUM", realPageIndex * cmd.PageSize);
innerCommand.InternalQuerys(cmd.QueryFields.ToArray());
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction("ROWNUM", "RN"));
sb.AppendSelectFrom(innerCommand.ToString("T"), true);
sb.AppendWhere(SqlCondition.GreaterThanOrEqual("RN", (realPageIndex - 1) * cmd.PageSize + 1));
}
return sb.ToString();
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
if (realPageIndex == 1)//第一页
{
if (cmd.PageSize > 0)
{
sb.AppendSelectTop(cmd.PageSize);
}
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else if (realPageIndex == realPageCount)//最后一页
{
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innerCommand = new SelectCommand(cmd.Database, cmd.TableName);
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.RecordCount - cmd.PageSize * (realPageIndex - 1);
innerCommand.SqlJoins = cmd.SqlJoins;
innerCommand.SqlWhere = cmd.SqlWhere;
innerCommand.GroupByColumns = cmd.GroupByColumns;
innerCommand.SqlHaving = cmd.SqlHaving;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else if (realPageIndex < (realPageCount / 2 + realPageCount % 2))//前1/2部分页
{
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName);
innestCommand.QueryFields = cmd.QueryFields;
innestCommand.PageSize = cmd.PageSize * realPageIndex;
innestCommand.SqlJoins = cmd.SqlJoins;
innestCommand.SqlWhere = cmd.SqlWhere;
innestCommand.GroupByColumns = cmd.GroupByColumns;
innestCommand.SqlHaving = cmd.SqlHaving;
innestCommand.SqlOrders = cmd.SqlOrders;
SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T");
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.PageSize;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else//后1/2部分页
{
sb.AppendSelectTop(cmd.PageSize);
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innerCommand = new SelectCommand(cmd.Database, cmd.TableName);
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.RecordCount - cmd.PageSize * (realPageIndex - 1);
innerCommand.SqlJoins = cmd.SqlJoins;
innerCommand.SqlWhere = cmd.SqlWhere;
innerCommand.GroupByColumns = cmd.GroupByColumns;
innerCommand.SqlHaving = cmd.SqlHaving;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
return(sb.ToString());
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
if (realPageIndex == 1)//第一页
{
if (cmd.PageSize > 0)
{
sb.AppendSelectTop(cmd.PageSize);
}
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else if (realPageIndex == realPageCount)//最后一页
{
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innerCommand = new SelectCommand(cmd.Database, cmd.TableName);
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.RecordCount - cmd.PageSize * (realPageIndex - 1);
innerCommand.SqlJoins = cmd.SqlJoins;
innerCommand.SqlWhere = cmd.SqlWhere;
innerCommand.GroupByColumns = cmd.GroupByColumns;
innerCommand.SqlHaving = cmd.SqlHaving;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else if (realPageIndex < (realPageCount / 2 + realPageCount % 2))//前1/2部分页
{
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName);
innestCommand.QueryFields = cmd.QueryFields;
innestCommand.PageSize = cmd.PageSize * realPageIndex;
innestCommand.SqlJoins = cmd.SqlJoins;
innestCommand.SqlWhere = cmd.SqlWhere;
innestCommand.GroupByColumns = cmd.GroupByColumns;
innestCommand.SqlHaving = cmd.SqlHaving;
innestCommand.SqlOrders = cmd.SqlOrders;
SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T");
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.PageSize;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else//后1/2部分页
{
sb.AppendSelectTop(cmd.PageSize);
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innerCommand = new SelectCommand(cmd.Database, cmd.TableName);
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.RecordCount - cmd.PageSize * (realPageIndex - 1);
innerCommand.SqlJoins = cmd.SqlJoins;
innerCommand.SqlWhere = cmd.SqlWhere;
innerCommand.GroupByColumns = cmd.GroupByColumns;
innerCommand.SqlHaving = cmd.SqlHaving;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
return sb.ToString();
}
