public override string SelectPaging(Type entityType, string condition, int pageSize, int pageIndex,
           Order order, IList<string> propertyNames)
        {
            if (pageSize <= 0)
            {
                throw new ArgumentOutOfRangeException("pageSize");
            }

            string tableName = CheckAndGetTableName(entityType);

            if (order == null || string.IsNullOrEmpty(order.OrderColumn) || string.IsNullOrEmpty(order.OrderString))
            {
                throw new ArgumentNullException(MsgResource.SortedOrderRequired);
            }

            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }

            string columnNameList = ConvertAndFormatColumnNames(entityType, propertyNames);

            pageIndex = pageIndex < 0 ? 0 : pageIndex;

            if (pageIndex == 0)
            {
                return "SELECT TOP " + pageSize + " " + columnNameList + " FROM " + tableName +
                      " WHERE " + condition + " ORDER BY " + order.OrderString;
            }

            return "SELECT TOP " + pageSize + " " + columnNameList + " FROM " + tableName +
                      " WHERE " + condition + " AND " + order.OrderColumn +
                      " NOT IN (SELECT TOP " + pageSize * pageIndex + " " + order.OrderColumn + " FROM " + tableName +
                      " WHERE " + condition + " ORDER BY " + order.OrderString + ") ORDER BY " + order.OrderString;
        }
        public override string SelectPaging(Type entityType, string condition,
            int pageSize, int pageIndex, Order order, IList<string> propertyNames = null)
        {
            if (pageSize <= 0)
            {
                throw new ArgumentOutOfRangeException("pageSize");
            }

            if (order != null && (string.IsNullOrEmpty(order.OrderColumn) || string.IsNullOrEmpty(order.OrderString)))
            {
                throw new ArgumentNullException(MsgResource.SortedOrderRequired);
            }

            string tableName = CheckAndGetTableName(entityType);

            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }

            var sql = new StringBuilder();

            sql.Append("SELECT ");
            sql.Append(ConvertAndFormatColumnNames(entityType, propertyNames));
            sql.Append(" FROM ");
            sql.Append(tableName);
            sql.Append(" WHERE " + condition);

            if (order != null && !string.IsNullOrEmpty(order.OrderString))
            {
                sql.Append(" ORDER BY " + order.OrderString);
            }

            int offset = pageSize * pageIndex;
           
            sql.Append(" LIMIT " + pageSize.ToString(CultureInfo.CurrentCulture) + " ");
            sql.Append(" OFFSET " + offset.ToString(CultureInfo.CurrentCulture) + " ");

            return sql.ToString();
        }
Example #3
0
 private static bool HasOrder(Order order)
 {
     return order != null && !string.IsNullOrEmpty(order.OrderString); 
 }
Example #4
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="left"></param>
 /// <param name="right"></param>
 /// <returns></returns>
 public static Order BitwiseAnd(Order left, Order right)
 {
     if (left == null || right==null)
     {
         throw new ArgumentNullException(MsgResource.InvalidArguments);
     }
     
     if (!HasOrder(right))
     {
         return left;
     }
     else if (!HasOrder(left))
     {
         return right;
     }
     else
     {
         left.OrderString = left.OrderString + "," + right.OrderString;
         return left;
     }
 }
        /// <summary>
        /// LIKE SQLITE ,Use LIMIT command to get the top content
        /// </summary>
        /// <param name="entityType"></param>
        /// <param name="condition"></param>
        /// <param name="topCount"></param>
        /// <param name="order"></param>
        /// <param name="propertyNames"></param>
        /// <returns></returns>
        public override string SelectTop(Type entityType, string condition, int topCount,
            Order order = null, IList<string> propertyNames = null)
        {
              if (topCount <= 0)
            {
                throw new ArgumentOutOfRangeException("topCount");
            }

            string tableName = CheckAndGetTableName(entityType);

            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }

            var sql = new StringBuilder();

            sql.Append("SELECT ");
            sql.Append(ConvertAndFormatColumnNames(entityType, propertyNames));
            sql.Append(" FROM ");
            sql.Append(tableName);
            sql.Append(" WHERE " + condition);

            if (order != null && !string.IsNullOrEmpty(order.OrderString))
            {
                sql.Append(" ORDER BY " + order.OrderString);
            }

            sql.Append(" LIMIT " + topCount.ToString(CultureInfo.CurrentCulture) + " ");

            return sql.ToString();
        }