/// <summary>
/// 分页导出数据,PageIndex从1开始
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="sql"></param>
/// <param name="Pars"></param>
/// <param name="PageSize">为零不限制页面大小即返回所有数据</param>
/// <param name="PageIndex">PageIndex从1开始</param>
/// <param name="returnTotalRows"></param>
/// <param name="TotalRows"></param>
/// <returns></returns>
public static DataTable SelectPageDataTable(this DbContext connInfo,
string sql, RequestBase param, int PageSize, int PageIndex, bool returnTotalRows, ref int TotalRows,
string filter = "", string orderStatement = "", Dictionary <string, object> condition = null)
{
bool getAll = (0 == PageSize);
if (PageSize < 0)
{
throw new Exception("PageSize must greater or equal than 0!");
}
if (!getAll && PageIndex < 1)
{
throw new Exception("PageIndex must greater or equal than 1!");
}
StringBuilder sqlbld = new StringBuilder(1024);
sqlbld.Append(sql);
bool hasCondi = (!string.IsNullOrEmpty(filter) || (null != condition && 0 < condition.Count)); //无过滤
if (hasCondi)
{
bool containWhere = 0 <= sql.IndexOf("WHERE ", StringComparison.OrdinalIgnoreCase);
sqlbld.Append(containWhere ? " AND " : " WHERE ");
sqlbld.Append(filter);
AddParameters(condition, ref param);
}
AppendOrderBy(orderStatement, sqlbld);
string SQL = sqlbld.ToString();
if (!getAll)
{
if (returnTotalRows)
{
TotalRows = 0;
TotalRows = Count(connInfo, SQL, param);
}
return(connInfo.GetDataTablePage(SQL, (PageIndex - 1) * PageSize, PageSize, param));
}
else
{
DataTable result = connInfo.GetDataTable(SQL, param);
if (returnTotalRows)
{
TotalRows = (null != result ? result.Rows.Count : 0);
}
return(result);
}
}
/// <summary>
/// Start下标从零开始
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="Start">0--></param>
/// <param name="Rows"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static IEnumerable <T> Select <T>(this DbContext connInfo, string sql, object param, int Start, int Rows)
where T : new()
{
if (connInfo.IsMySQL)
{
return(connInfo.Query <T>(SqlBuilder.ConvertToMySqlPagingSql(sql, Start, Rows), param));
}
else if (connInfo.DbType == DbConnType.SQLITE)
{
return(connInfo.Query <T>(SqlBuilder.ConvertToSqlitePagingSql(sql, Start, Rows), param));
}
else if (connInfo.DbType == DbConnType.POSTGRESQL)
{
return(connInfo.Query <T>(SqlBuilder.ConvertToPostgreSqlPagingSql(sql, Start, Rows), param));
}
else if (connInfo.DbType == DbConnType.MSSQL)
{
bool converted = false;
string newSql = SqlBuilder.ConvertToMsSqlPagingSql(sql, out converted);
if (converted)
{
RequestBase para = SqlBuilder.ConvertParameter(param).SetValue("pageSize", Rows).SetValue("startIndex", Start);
return(connInfo.Query <T>(newSql, para));
}
}
else if (connInfo.IsOracleDb /*&& Start < 300000*/)
{
bool converted = false;
string newSql = SqlBuilder.ConvertToOraclePagingSql(sql, out converted);
if (converted)
{
RequestBase para = SqlBuilder.ConvertParameter(param).SetValue("endIndex", Start + Rows).SetValue("startIndex", Start);
return(connInfo.Query <T>(newSql, para));
}
}
DataTable dt = null;
try
{
dt = connInfo.GetDataTablePage(sql, Start, Rows, param);
return(EntityReader.GetEntities <T>(dt));
}
finally
{
dt?.Dispose();
}
}