/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList());
if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式
{
/*
* SELECT * FROM
* (
* SELECT *, ROWNUM RN
* FROM (SELECT * FROM TABLE_NAME)
* WHERE ROWNUM <= 30
* )
* WHERE RN > 20
*/
SelectCommand innestCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, baseCommand.TableName);
innestCommand.InternalSetQueryFieldList(baseCommand);
innestCommand.InternalSetJoinList(baseCommand);
innestCommand.InternalSetWhereCondition(baseCommand);
innestCommand.InternalSetGroupByFieldList(baseCommand);
innestCommand.InternalSetHavingCondition(baseCommand);
innestCommand.InternalSetOrderList(baseCommand);
SelectCommand innerCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, innestCommand, "");
innerCommand.InternalSetWhereCondition(innerCommand.ConditionBuilder.LessThanOrEqualColumn("ROWNUM", (baseCommand.RecordStart + baseCommand.PageSize).ToString()));
innerCommand.InternalQuerys(baseCommand.QueryFields);
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROWNUM", "RN"));
sb.AppendSelectFrom(innerCommand.GetCommandText(), true);
sb.AppendWhere(baseCommand.ConditionBuilder.GreaterThanColumn("RN", baseCommand.RecordStart.ToString()));
}
else//正常模式
{
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList());
ISqlCondition where = baseCommand.WhereCondition;
if (baseCommand.PageSize > 0)
{
where = baseCommand.ConditionBuilder.And(where, baseCommand.ConditionBuilder.LessThanOrEqualColumn("ROWNUM", baseCommand.PageSize.ToString()));
}
sb.AppendWhere(where);
sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList());
sb.AppendHaving(baseCommand.InternalGetHavingCondition());
sb.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), orderReverse);
}
return(sb.ToString());
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式
{
/*
* SELECT * FROM
* (
* SELECT TOP 30 *, ROW_NUMBER() OVER(ORDER BY ID ASC) AS RN
* FROM TABLE_NAME
* ) AS T
* WHERE RN > 20
*/
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList());
SqlCommandBuilder innerBuilder = new SqlCommandBuilder(baseCommand.Database);
innerBuilder.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), false);
SelectCommand innerCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, baseCommand.TableName);
innerCommand.InternalQuerys(baseCommand.QueryFields);
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROW_NUMBER() OVER( " + innerBuilder.ToString() + ")", "RN"));
innerCommand.PageSize = baseCommand.RecordStart + baseCommand.PageSize;
innerCommand.InternalSetJoinList(baseCommand);
innerCommand.InternalSetWhereCondition(baseCommand);
innerCommand.InternalSetGroupByFieldList(baseCommand);
innerCommand.InternalSetHavingCondition(baseCommand);
sb.AppendSelectFrom(innerCommand.GetCommandText("T"), true);
sb.AppendWhere(baseCommand.ConditionBuilder.GreaterThanColumn("RN", baseCommand.RecordStart.ToString()));
}
else//正常模式
{
if (baseCommand.PageSize > 0)
{
sb.AppendSelectTop(baseCommand.PageSize);
}
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList());
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList());
sb.AppendWhere(baseCommand.WhereCondition);
sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList());
sb.AppendHaving(baseCommand.InternalGetHavingCondition());
sb.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), orderReverse);
}
return(sb.ToString());
}
public XElement GetValueElement(SqlQueryReader reader, SqlQueryField field)
{
var name = GetFieldElementName(field);
if (reader.IsDbNull(field.Index))
{
return(new XElement(name, ""));
}
if (field.AttrDef != null)
{
switch (field.AttrDef.Type.Id)
{
case (short)CissaDataType.Enum:
if (field.AttrDef.EnumDefType == null)
{
break;
}
var enumDef = _enumRepo.Get(field.AttrDef.EnumDefType.Id);
var enumItem = enumDef.EnumItems.FirstOrDefault(i => i.Id == reader.GetGuid(field.Index));
if (enumItem != null)
{
return(new XElement(name, new XElement("Id", enumItem.Id),
new XElement("Value", enumItem.Value)));
}
break;
case (short)CissaDataType.Doc:
if (PublicDocAttribute(field.AttrDef))
{
var doc = _docRepo.LoadById(reader.GetGuid(field.Index));
return(new XElement(name, GetDocElement(doc)));
}
break;
/*
* case (short) CissaDataType.DocList:
* if (PublicDocAttribute(field.AttrDef))
* {
* DocRepo.Ge
* var docList = new DocList(DataContext);
* return new XElement(name, GetDocListElement(doc));
* }
* break;
*/
}
}
return(new XElement(name, reader.GetValue(field.Index)));
}
/// <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="baseCommand">源选择语句</param>
/// <returns>选择数量语句</returns>
internal static String InternalGetCountCommand(SelectCommand baseCommand)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
List <SqlQueryField> queryFields = new List <SqlQueryField>();
queryFields.Add(SqlQueryField.InternalCreateFromAggregateFunction(baseCommand, SqlAggregateFunction.Count));
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, queryFields);
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList());
sb.AppendWhere(baseCommand.WhereCondition);
sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList());
sb.AppendHaving(baseCommand.InternalGetHavingCondition());
return(sb.ToString());
}
private static string GetFieldElementName(SqlQueryField field)
{
var name = field.AttributeName;
if (String.IsNullOrWhiteSpace(name))
{
name = field.SelectAttribute != null ? field.SelectAttribute.Alias : String.Empty;
}
if (String.IsNullOrWhiteSpace(name))
{
name = field.SelectAttribute != null ? field.SelectAttribute.AliasName : String.Empty;
}
if (String.IsNullOrWhiteSpace(name))
{
name = field.AttributeId.ToString();
}
return(name.Replace("&", ""));
}