/// <summary>
/// 按照分页查询数据
/// </summary>
/// <param name="dataAccess">数据访问器</param>
/// <param name="whereString">查询条件</param>
/// <param name="orderString">排序或分组条件</param>
/// <param name="pageStart">查询页编号</param>
/// <param name="pageSize">每页数据量</param>
/// <returns></returns>
public IDbCommand CreateDataSetPage(IDataAccess dataAccess, string whereString, string orderString, int pageStart, int pageSize)
{
string strOrderby = string.Empty;
string strWhere = string.Empty;
int index = -1;
if (!string.IsNullOrEmpty(orderString))
{
index = orderString.ToUpper().IndexOf("ORDER BY");
strOrderby = "ORDER BY " + (index == -1 ? orderString.Trim() : orderString.Substring(index + 9).Trim());
}
if (!string.IsNullOrEmpty(whereString))
{
index = whereString.ToUpper().IndexOf("WHERE");
strWhere = "WHERE " + (index == -1 ? whereString.Trim() : whereString.Substring(index + 6).Trim());
}
IDbCommand dbCommand = dataAccess.CreateCommand();
dbCommand.CommandType = CommandType.Text;
string sqlText = null;
long rowStart = pageStart < 1 ? 1 : (pageStart - 1) * pageSize + 1;
long rowEnd = rowStart + pageSize;
switch (dataAccess.DataBaseType)
{
case DataBaseType.Sql:
if (rowStart <= 1L)
{
//sqlText = string.Format("SELECT TOP {0} {1} FROM {2} WITH(NOLOCK){3} ORDER BY {4}",
sqlText = string.Format("SELECT TOP {0} {1} FROM {2} {3} {4}",
new object[] { rowEnd, "*", this.TableName, strWhere, strOrderby });
}
else
{
sqlText = string.Format("WITH temptable AS (SELECT ROW_NUMBER() OVER({0}) AS 'RowNo',{1} FROM {2} {3}) SELECT * FROM temptable WHERE RowNo BETWEEN {4} AND {5}",
new object[] { strOrderby, "*", this.TableName, strWhere, rowStart, rowEnd });
}
break;
case DataBaseType.Oracle:
case DataBaseType.Odac:
strWhere = "AND" + strWhere.Substring(5);
sqlText = string.Format("SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM {0} {1}) a WHERE ROWNUM <= {2} {3}) WHERE rn >= {4}",
new object[] { this.TableName, strOrderby, rowEnd, strWhere, rowStart });
break;
case DataBaseType.OleDb:
throw new Exception("没有设定");
}
dbCommand.CommandText = sqlText;
return dbCommand;
}