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();
}
private static bool HasOrder(Order order)
{
return order != null && !string.IsNullOrEmpty(order.OrderString);
}
/// <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();
}