/// <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; }