public DetailPagingRet DetailPaging(ExecuteDelegate executeAction,
int pageIndex, int pageSize,
string fixedSql, string selectSql, SqlParameter[] paramValues,
CommandType commandType = CommandType.Text
)
{
//##0 init
string SELECTSQL, orderBodyString, SELECTWithoutOrder, fromBodyString, FINALSQL;
int totalCount = 0;
//##1 Check
DealHelper.DetailPagingHelper_Prepare(selectSql, out SELECTSQL);
try
{
DealHelper.DetailPagingHelper_SplitStrings(SELECTSQL, out orderBodyString, out SELECTWithoutOrder, out fromBodyString);
//##2 get finall sql
DetailPagingHelper_SqlPageAndCount(paramValues, orderBodyString, SELECTWithoutOrder, fixedSql, pageIndex, pageSize,
out FINALSQL);
//##3 exec sql expreession and return result
DataTable querydt = executeAction(FINALSQL, paramValues, commandType);
return(DetailPagingHelper_returnResult(querydt, totalCount));
}
catch (SqlException ex)
{
throw DealHelper.DetailPagingHelper_CatchException_SqlException(ex, fixedSql, SELECTSQL);
}
catch (Exception ex)
{
throw DealHelper.DetailPagingHelper_CatchException_CommonException(ex, fixedSql, SELECTSQL, totalCount);
}
}
private static void DetailPagingHelper_SqlCount(string fixedSql, string SELECTSQL, string SELECTWithoutOrder, string fromBodyString, string orderBodyString,
out string sqlCount)
{
bool hasOrderBy = !String.IsNullOrEmpty(orderBodyString);
//sqlCount
if (!DealHelper.hasDistinct(SELECTSQL))
{//## no DISTINCT
sqlCount = "SELECT COUNT(1) " + fromBodyString;
}
else
{//## has DISTINCT
if (hasOrderBy)
{
sqlCount = "SELECT COUNT(1) FROM ( " + SELECTWithoutOrder + ") as ___temp___sqlCount___";
}
else
{
sqlCount = "SELECT COUNT(1) FROM ( " + SELECTSQL + ") as ___temp___sqlCount___";
}
}
//----processing fixed sql----
sqlCount = fixedSql + "\r\n" + sqlCount;
//----------------------------
}
private static void DetailPagingHelper_SqlPageAndCount(SqlParameter[] paramValues, string orderBodyString, string SELECTWithoutOrder, string fixedSql, int pageIndex, int pageSize,
out string FINALSQL)
{
StringBuilder adjustedSQL = new StringBuilder();
bool hasDistinct = DealHelper.hasDistinct(SELECTWithoutOrder);
//##1 Add TOTAL column
if (!hasDistinct)
{
int firstSelectPos = SELECTWithoutOrder.IndexOf("SELECT ", StringComparison.OrdinalIgnoreCase);
adjustedSQL.Append("SELECT COUNT(*) OVER() AS ___totalCount___,");
adjustedSQL.AppendLine(SELECTWithoutOrder.Substring(firstSelectPos + 7));
}
else
{
adjustedSQL.Append("SELECT COUNT(*) OVER() AS ___totalCount___, * FROM ( ");
adjustedSQL.Append(SELECTWithoutOrder);
adjustedSQL.AppendLine(" ) as ___temp___selectBody___ ");
}
//##2 Handle OrderBy
bool HasOrderBy = !String.IsNullOrEmpty(orderBodyString);
if (HasOrderBy)
{
adjustedSQL.Append("ORDER BY ");
adjustedSQL.AppendLine(orderBodyString);
}
else
{
adjustedSQL.AppendLine("ORDER BY (select null) ");
}
//##3 Offset & Fetch
adjustedSQL.Append("OFFSET ");
adjustedSQL.Append((pageIndex - 1) * pageSize);
adjustedSQL.AppendLine(" ROWS ");
adjustedSQL.Append("FETCH NEXT ");
adjustedSQL.Append(pageSize);
adjustedSQL.AppendLine(" ROWS ONLY ");
//----processing fixed sql----
FINALSQL = fixedSql + "\r\n" + adjustedSQL.ToString();
//----------------------------
}
/// <summary>
/// Detail to PagingAsync
/// </summary>
public async Task <DetailPagingRet> DetailPagingAsync(ExecuteAsyncDelegate executeAction,
int pageIndex, int pageSize,
string fixedSql, string selectSql, SqlParameter[] paramValues,
CommandType commandType = CommandType.Text
)
{
//##0 init
string SELECTSQL, orderBodyString, SELECTWithoutOrder, fromBodyString, FINALSQL;
int totalCount = 0;
var paras = new List <SqlParameter>();
//##1 Check
DealHelper.DetailPagingHelper_Prepare(selectSql, out SELECTSQL);
try
{
DealHelper.DetailPagingHelper_SplitStrings(SELECTSQL, out orderBodyString, out SELECTWithoutOrder, out fromBodyString);
string sqlCount;
DetailPagingHelper_SqlCount(fixedSql, SELECTSQL, SELECTWithoutOrder, fromBodyString, orderBodyString, out sqlCount);
//##2 get record total and begin to page
DataTable countDt = await executeAction(sqlCount, paramValues, commandType);
DetailPagingHelper_SqlPage(countDt, paramValues, orderBodyString, SELECTWithoutOrder, fixedSql,
pageIndex, pageSize,
ref SELECTSQL, ref paras,
out totalCount, out FINALSQL);
//##3 exec sql expreession and return result
DataTable querydt = await executeAction(FINALSQL, paras.ToArray(), commandType);
return(DetailPagingHelper_returnResult(querydt, totalCount));
}
catch (SqlException ex)
{
throw DealHelper.DetailPagingHelper_CatchException_SqlException(ex, fixedSql, SELECTSQL);
}
catch (Exception ex)
{
throw DealHelper.DetailPagingHelper_CatchException_CommonException(ex, fixedSql, SELECTSQL, totalCount);
}
}
