/// <summary>
/// 获取分页语句
/// </summary>
/// <param name="sql">要被分页的SQL</param>
/// <param name="objCondition">分页类</param>
/// <returns></returns>
public static string GetCutPageSql(string sql, PageContent objPage)
{
long starIndex = objPage.GetStarIndex();
string tmpsql = sql;
tmpsql += " limit " + starIndex + "," + objPage.PageSize;
return(tmpsql);
}
/// <summary>
/// 生成分页SQL语句
/// </summary>
/// <param name="part">SQL条件</param>
/// <returns></returns>
private static string GetCutPageSql(SelectCondition objCondition, PageContent objPage)
{
string orderBy = null;
if (objCondition.Orders.Length > 0)//如果有排序就用本身排序
{
orderBy = objCondition.Orders.ToString();
}
else//如果没有就用主键排序
{
StringBuilder sbOrder = new StringBuilder();
foreach (string pkName in objCondition.PrimaryKey)
{
sbOrder.Append(pkName + ",");
}
if (sbOrder.Length > 0)
{
sbOrder.Remove(sbOrder.Length - 1, 1);
orderBy = sbOrder.ToString();
}
}
long starIndex = objPage.GetStarIndex() + 1;
string rowNumberName = "\"__cur_rowNumber" + objPage.PagerIndex + "\"";
long endIndex = objPage.PageSize * (objPage.CurrentPage + 1);
StringBuilder sql = new StringBuilder(5000);
sql.Append("select * from (");
sql.Append("select ROW_NUMBER() over(order by " + orderBy + ") as " + rowNumberName + ",");
if (!objCondition.HasGroup)
{
sql.Append(objCondition.SqlParams.ToString() + " from " + objCondition.Tables.ToString());
if (objCondition.Condition.Length > 0)
{
sql.Append(" where " + objCondition.Condition.ToString());
}
if (objCondition.GroupBy.Length > 0)
{
sql.Append(" group by " + objCondition.GroupBy.ToString());
}
if (objCondition.Having.Length > 0)
{
sql.Append(" having ");
sql.Append(objCondition.Having.ToString());
}
}
else
{
sql.Append("\"_tmpInnerTable\".*");
sql.Append(" from (");
Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.CutPageSqlCreater.GetGroupPart(objCondition, sql);
sql.Append(") \"_tmpInnerTable\"");
}
sql.Append(") tmp where " + rowNumberName + " >=" + starIndex + " and " + rowNumberName + " <=" + endIndex);
return(sql.ToString());
}
/// <summary>
/// 生成分页SQL语句
/// </summary>
/// <param name="part">SQL条件</param>
/// <returns></returns>
private static string GetCursorPageSql(string sql, PageContent objPage)
{
long starIndex = objPage.GetStarIndex() + 1;
string rowNumberName = "\"__cur_rowNumber" + objPage.PagerIndex + "\"";
long endIndex = objPage.PageSize * (objPage.CurrentPage + 1);
StringBuilder sb = new StringBuilder(5000);
sb.Append("select * from (");
sb.Append("select ROW_NUMBER() over() as " + rowNumberName + ",tmp.* from (");
sb.Append(sql);
sb.Append(") tmp) tmp1 where " + rowNumberName + " >=" + starIndex + " and " + rowNumberName + " <=" + endIndex);
return(sb.ToString());
}
/// <summary>
/// 获取分页语句
/// </summary>
/// <param name="sql">要被分页的SQL</param>
/// <param name="objCondition">分页类</param>
/// <returns></returns>
public static string GetCutPageSql(string sql, PageContent objPage)
{
long starIndex = objPage.GetStarIndex() + 1;
long endIndex = objPage.PageSize + starIndex - 1;
StringBuilder tmpsql = new StringBuilder(5000);
string rowNumberName = "\"cur_rowNumber" + objPage.PagerIndex + "\"";
tmpsql.Append("SELECT * FROM (SELECT tmp.*, ROWNUM as " + rowNumberName + " FROM (");
tmpsql.Append(sql);
tmpsql.Append(") tmp WHERE ROWNUM <= ");
tmpsql.Append(endIndex.ToString());
tmpsql.Append(") WHERE " + rowNumberName + " >=");
tmpsql.Append(starIndex.ToString());
return(tmpsql.ToString());
}
/// <summary>
/// 查询并且返回DataSet(游标分页)
/// </summary>
/// <param name="sql">要查询的SQL语句</param>
/// <param name="objPage">分页对象</param>
/// <param name="oper">数据库对象</param>
/// <param name="curType">映射的实体类型(如果用回数据库的原列名，则此为null)</param>
/// <returns></returns>
public static DataTable QueryDataTable(string sql, PageContent objPage, DataBaseOperate oper, Type curType)
{
DataTable ret = new DataTable();
ParamList lstParams = new ParamList();
lstParams.AddNew("@sql", DbType.AnsiString, sql);
lstParams.AddNew("@currentIndex", DbType.Int32, objPage.GetStarIndex() + 1);
lstParams.AddNew("@pagesize", DbType.Int32, objPage.PageSize);
lstParams.AddNew("@maxRecords", DbType.Int64, objPage.MaxSelectRecords);
IDataReader reader = null;
try
{
InitProc(oper);
reader = oper.Query(ProcName, lstParams, CommandType.StoredProcedure, null);
if (reader.NextResult())//第二个结果集为查询结果
{
if (curType == null)
{
ret = CacheReader.GenerateDataTable(reader, "newDt", false);
}
else
{
ret = CacheReader.GenerateDataTable(reader, "newDt", curType, false);
}
}
if (reader.NextResult())//第三个结果集为总行数
{
if (reader.Read())
{
int totalRecord = reader.GetInt32(0);
objPage.TotalRecords = totalRecord;
//int totalPage = (int)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
//objPage.TotalPage = totalPage;
if (objPage.CurrentPage >= objPage.TotalPage - 1)
{
objPage.CurrentPage = objPage.TotalPage - 1;
}
}
}
}
finally
{
reader.Close();
}
return(ret);
}
/// <summary>
/// 查询并且返回集合(游标分页)
/// </summary>
/// <param name="sql">要查询的SQL语句</param>
/// <param name="objPage">分页对象</param>
/// <param name="oper">数据库对象</param>
/// <returns></returns>
public static IDataReader Query(string sql, PageContent objPage, DataBaseOperate oper)
{
ParamList lstParams = new ParamList();
lstParams.AddNew("@sql", DbType.AnsiString, sql);
lstParams.AddNew("@currentIndex", DbType.Int32, objPage.GetStarIndex() + 1);
lstParams.AddNew("@pagesize", DbType.Int32, objPage.PageSize);
lstParams.AddNew("@maxRecords", DbType.Int64, objPage.MaxSelectRecords);
//lstParams.AddNew("@@total", DbType.Int64, 0, ParameterDirection.Output);
IDataReader reader = null;
InitProc(oper);
reader = oper.Query(ProcName, lstParams, CommandType.StoredProcedure, null);
if (reader.NextResult())//第二个结果集为查询记录数
{
if (objPage.IsFillTotalRecords)
{
if (reader.Read())
{
int totalRecord = reader.GetInt32(0);
objPage.TotalRecords = totalRecord;
//int totalPage = (int)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
//objPage.TotalPage = totalPage;
if (objPage.CurrentPage >= objPage.TotalPage - 1)
{
objPage.CurrentPage = objPage.TotalPage - 1;
}
}
}
}
if (reader.NextResult())//第三个结果集为真正记录
{
return(reader);
}
return(null);
}
/// <summary>
/// 生成分页SQL语句
/// </summary>
/// <param name="part">SQL条件</param>
/// <returns></returns>
private static string CreateCutPageSql(SelectCondition objCondition, PageContent objPage)
{
string totalLine = objPage.PageSize.ToString();
long starIndex = objPage.GetStarIndex();
if (starIndex == 0)
{
//sql.Append("select top " + totalLine + " " + objCondition.SqlParams + " from [" + objCondition.Tables + "] " + " where " + objCondition.Condition);
return(GetFristPageSql(objCondition, objPage));
}
string pkKey = null;
if (objCondition.PrimaryKey.Count > 1)
{
StringBuilder sb = new StringBuilder();
foreach (string pk in objCondition.PrimaryKey)
{
sb.Append("convert(varchar," + pk + ",21)+'|'+");
}
if (sb.Length > 0)
{
sb.Remove(sb.Length - 5, 5);
}
pkKey = sb.ToString();
}
else if (objCondition.PrimaryKey.Count == 1)
{
pkKey = objCondition.PrimaryKey[0];
}
StringBuilder sql = new StringBuilder(5000);
//sql.Append("select top " + totalLine + " " + objCondition.SqlParams.ToString() + " from [" + objCondition.Tables.ToString() + "] where(not exists (select * from (select top " + starIndex.ToString() + " " + objCondition.SqlParams + " from [" + objCondition.Tables + "] where " + objCondition.Condition);
sql.Append("select top ");
sql.Append(totalLine);
sql.Append(" ");
sql.Append(objCondition.SqlParams.ToString());
sql.Append(" from ");
sql.Append(objCondition.Tables.ToString());
sql.Append(" where(" + pkKey + " not in (select top ");
sql.Append(starIndex.ToString());
sql.Append(" ");
sql.Append(pkKey);
sql.Append(" from ");
sql.Append(objCondition.Tables.ToString());
if (objCondition.Condition.Length > 0)
{
sql.Append(" where ");
sql.Append(objCondition.Condition.ToString());
}
if (objCondition.GroupBy.Length > 0)
{
sql.Append(" group by ");
sql.Append(objCondition.GroupBy.ToString());
}
if (objCondition.Orders.Length > 0)
{
sql.Append(" order by ");
sql.Append(objCondition.Orders.ToString());
}
if (objCondition.Having.Length > 0)
{
sql.Append(" having ");
sql.Append(objCondition.Having.ToString());
}
sql.Append(")");
sql.Append(")");
if (objCondition.Condition.Length > 0)
{
sql.Append(" and (");
sql.Append(objCondition.Condition.ToString());
sql.Append(")");
}
if (objCondition.GroupBy.Length > 0)
{
sql.Append(" group by ");
sql.Append(objCondition.GroupBy.ToString());
}
if (objCondition.Orders.Length > 0)
{
sql.Append(" order by ");
sql.Append(objCondition.Orders.ToString());
}
if (objCondition.Having.Length > 0)
{
sql.Append(" having ");
sql.Append(objCondition.Having.ToString());
}
return(sql.ToString());
}
/// <summary>
/// 生成分页SQL语句
/// </summary>
/// <param name="part">SQL条件</param>
/// <returns></returns>
private static string CreateCutPageSql(SelectCondition objCondition, PageContent objPage)
{
string orderBy = null;
if (objCondition.Orders.Length > 0)//如果有排序就用本身排序
{
orderBy = objCondition.Orders.ToString();
}
else//如果没有就用主键排序
{
StringBuilder sbOrder = new StringBuilder();
foreach (string pkName in objCondition.PrimaryKey)
{
sbOrder.Append(pkName + ",");
}
if (sbOrder.Length > 0)
{
sbOrder.Remove(sbOrder.Length - 1, 1);
orderBy = sbOrder.ToString();
}
}
StringBuilder sql = new StringBuilder(5000);
if (objPage.GetStarIndex() == 0)
{
return(Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.CutPageSqlCreater.GetFristPageSql(objCondition, objPage));
}
long starIndex = objPage.GetStarIndex() + 1;
string rowNumberName = "[cur_rowNumber" + objPage.PagerIndex + "]";
long topRec = objPage.GetStarIndex() + objPage.PageSize;
//sql.Append("select top " + objPage.PageSize + " * from(");
sql.Append("select ");
string newOrderBy = null;
if (objCondition.HasGroup)
{
sql.Append("*");
newOrderBy = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.CutPageSqlCreater.FilterGroupOrderBy(orderBy, "[_tmpInnerTable]");
}
else
{
sql.Append("*");
newOrderBy = orderBy;
}
sql.Append(" from (");
//sql.Append("select row_number() over(order by " + newOrderBy + ") as " +
// rowNumberName + ",");
sql.Append("select row_number() over(order by " + newOrderBy + ") as " +
rowNumberName + ",");
if (!objCondition.HasGroup)
{
sql.Append(objCondition.SqlParams.ToString());
sql.Append(" from ");
sql.Append(objCondition.Tables.ToString());
if (objCondition.Condition.Length > 0)
{
sql.Append(" where " + objCondition.Condition.ToString());
}
if (objCondition.GroupBy.Length > 0)
{
sql.Append(" group by " + objCondition.GroupBy.ToString());
}
if (objCondition.Having.Length > 0)
{
sql.Append(" having ");
sql.Append(objCondition.Having.ToString());
}
}
else
{
sql.Append("[_tmpInnerTable].*");
sql.Append(" from (");
Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.CutPageSqlCreater.GetGroupPart(objCondition, sql);
sql.Append(") [_tmpInnerTable]");
}
sql.Append(") tmp where " + rowNumberName + " between " + starIndex + " and " + topRec);
sql.Append(" order by ");
sql.Append(rowNumberName);
return(sql.ToString());
}
