/// <summary>
/// 构造分页查询语句
/// </summary>
/// <param name="options">分页查询参数</param>
/// <returns></returns>
public string BuildPagedQuerySql(PagedQueryOptions options)
{
if (string.IsNullOrEmpty(options.SqlFieldsPart))
{
throw new ArgumentNullException("Need to specify SqlFieldsPart");
}
if (string.IsNullOrEmpty(options.SqlFromPart))
{
throw new ArgumentNullException("Need to specify SqlFromPart");
}
if (string.IsNullOrEmpty(options.SqlOrderPart))
{
throw new ArgumentNullException("Need to specify SqlOrderPart");
}
int pageStartIndex = options.PageSize * options.PageIndex + 1;
int pageEndIndex = options.PageSize * (options.PageIndex + 1);
var sql = string.Format(@"SELECT * FROM (
SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS RowNumber FROM {2}
{3} {4}
) as PageTable where RowNumber>={5} and RowNumber<={6};",
options.SqlFieldsPart, options.SqlOrderPart, options.SqlFromPart,
string.IsNullOrEmpty(options.SqlConditionPart) ? string.Empty : "WHERE " + options.SqlConditionPart,
string.IsNullOrEmpty(options.SqlGroupPart) ? string.Empty : "GROUP BY " + options.SqlGroupPart,
pageStartIndex, pageEndIndex);
return(sql);
}
/// <summary>
/// 构造分页查询语句
/// </summary>
/// <param name="options">分页查询参数</param>
/// <returns></returns>
public string BuildPagedQuerySql(PagedQueryOptions options)
{
if (string.IsNullOrEmpty(options.SqlFieldsPart))
{
throw new ArgumentNullException("Need to specify SqlFieldsPart");
}
if (string.IsNullOrEmpty(options.SqlFromPart))
{
throw new ArgumentNullException("Need to specify SqlFromPart");
}
if (string.IsNullOrEmpty(options.SqlOrderPart))
{
throw new ArgumentNullException("Need to specify SqlOrderPart");
}
int pageStartIndex = options.PageSize * options.PageIndex;
int currentPageCount = options.PageSize;
var sql = string.Format(@"SELECT {0} FROM {1} {2} {3} {4} LIMIT {5},{6};",
options.SqlFieldsPart, options.SqlFromPart,
string.IsNullOrEmpty(options.SqlConditionPart) ? string.Empty : "WHERE " + options.SqlConditionPart,
string.IsNullOrEmpty(options.SqlGroupPart) ? string.Empty : "GROUP BY " + options.SqlGroupPart,
string.IsNullOrEmpty(options.SqlOrderPart) ? string.Empty : "ORDER BY " + options.SqlOrderPart,
pageStartIndex, currentPageCount);
return(sql);
}