Пример #1
0
        /// <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());
        }
Пример #2
0
        /// <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());
        }
Пример #3
0
        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());
        }
Пример #6
0
        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("&", ""));
        }