/// <summary>
/// 單表（視圖）獲取分頁SQL語句
/// </summary>
/// <param name="tableName">表名或視圖名</param>
/// <param name="key">唯一鍵</param>
/// <param name="fields">獲取的字段</param>
/// <param name="condition">查詢條件（不包含WHERE）</param>
/// <param name="collatingSequence">排序規則（不包含ORDER BY）</param>
/// <param name="pageSize">頁大小</param>
/// <param name="pageIndex">頁碼（從1開始）</param>
/// <returns>分頁SQL語句</returns>
public static string GetPagingSQL(
string tableName,
string key,
string fields,
string condition,
string collatingSequence,
int pageSize,
int pageIndex)
{
string whereClause = string.Empty;
if (!string.IsNullOrEmpty(condition))
{
whereClause = string.Format("WHERE {0}", condition);
}
if (string.IsNullOrEmpty(collatingSequence))
{
collatingSequence = string.Format("{0} ASC", key);
}
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("SELECT {0} ", PrependTableName(tableName, fields, ','));
sbSql.AppendFormat("FROM ( SELECT TOP {0} ", pageSize * pageIndex);
sbSql.AppendFormat(" [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY {0} ), ", collatingSequence);
sbSql.AppendFormat(" {0} ", key);
sbSql.AppendFormat(" FROM {0} ", tableName);
sbSql.AppendFormat(" {0} ", whereClause);
sbSql.AppendFormat(" ) AS [_TempTable_] ");
sbSql.AppendFormat(" INNER JOIN {0} ON [_TempTable_].{1} = {0}.{1} ", tableName, key);
sbSql.AppendFormat("WHERE [_RowNum_] > {0} ", pageSize * (pageIndex - 1));
sbSql.AppendFormat("ORDER BY [_TempTable_].[_RowNum_] ASC ");
return sbSql.ToString();
}
/// <summary>
/// 給字段添加表名前綴
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fields">字段</param>
/// <param name="separator">標識字段間的分隔符</param>
/// <returns></returns>
public static string PrependTableName(string tableName, string fields, char separator)
{
StringBuilder sbFields = new StringBuilder();
string[] fieldArr = fields.Trim(separator).Split(separator);
foreach (string str in fieldArr)
{
sbFields.AppendFormat("{0}.{1}{2}", tableName, str.Trim(), separator);
}
return sbFields.ToString().TrimEnd(separator);
}
