复制代码 代码如下:
namespace Alex
{
public class PageTools
{
/// <summary>
/// 表名称
/// </summary>
private string tableName;
public string TableName
{
get { return tableName; }
set { tableName = value; }
}
/// <summary>
/// 返回的列名
/// </summary>
private string returnColumns;
public string ReturnColumns
{
get { return returnColumns; }
set { returnColumns = value; }
}
/// <summary>
/// 条件
/// </summary>
private string where;
public string Where
{
get { return where; }
set { where = value; }
}
/// <summary>
/// 排序列
/// </summary>
private string orderColumnName;
public string OrderColumnName
{
get { return orderColumnName; }
set { orderColumnName = value; }
}
/// <summary>
/// 排序，true为降序
/// </summary>
private Boolean orderDesc;
public Boolean OrderDesc
{
get { return orderDesc; }
set { orderDesc = value; }
}
/// <summary>
/// 主键
/// </summary>
private string keyColumnName;
public string KeyColumnName
{
get { return keyColumnName; }
set { keyColumnName = value; }
}
/// <summary>
/// 页面大小
/// </summary>
private Int32 pageSize;
public Int32 PageSize
{
get { return pageSize; }
set { pageSize = value; }
}
/// <summary>
/// 第几页
/// </summary>
private Int32 pageIndex;
public Int32 PageIndex
{
get { return pageIndex; }
set { pageIndex = value; }
}
/// <summary>
/// SQL参数
/// </summary>
private SqlParameter[] whereParams;
public SqlParameter[] WhereParams
{
get { return whereParams; }
set { whereParams = value; }
}
/// <param></param>
/// <param></param>
/// <param>条件</param>
/// <param>排序列</param>
/// <param>排序，true为降序</param>
/// <param>主键</param>
/// <param>页面大小</param>
/// <param>第几页</param>
/// <param>SQL参数</param>
public PageTools(string tableName, string returnColumns, string where, string orderColumnName, Boolean orderDesc,
string keyColumnName, Int32 pageSize, Int32 pageIndex, params SqlParameter[] whereParams)
{
this.TableName = tableName;
this.ReturnColumns = returnColumns;
this.Where = where;
this.OrderColumnName = orderColumnName;
this.OrderDesc = orderDesc;
this.KeyColumnName = keyColumnName;
this.PageSize = pageSize;
this.PageIndex = pageIndex;
this.WhereParams = WhereParams;
}
public PageTools() { }
public DataTable RetrievePagerData()
{
if (pageIndex == 1)
{
return RetrieveTopData();
}
else
{
string sql = string.Format("select top {0} {1} from {2} where {3} and {6} not in ( select top {7} {6} from {2} where {3} order by {4} {5} ) order by {4} {5}", pageSize, returnColumns, tableName, where, orderColumnName, orderDesc == true ? "desc" : string.Empty, keyColumnName, pageSize * (pageIndex - 1));
return DbHelperSQL.Query(sql, CheckNull(whereParams)).Tables[0];
}
}
/// <summary>
/// 获取数据的条数
/// </summary>
/// <param></param>
/// <param></param>
/// <param></param>
/// <returns></returns>
public Int32 RetrieveDataCount()
{
string sql = string.Format("select count(*) from {0} where {1}" , tableName, where);
Int32 result = (Int32)DbHelperSQL.GetSingle(sql, CheckNull(whereParams));
return result;
}
/// <summary>
/// 获取第一页
/// </summary>
/// <param></param>
/// <param></param>
/// <param></param>
/// <param></param>
/// <param></param>
/// <param></param>
/// <param></param>
/// <returns></returns>
public DataTable RetrieveTopData()
{
string sql = string.Format("select top {0} {1} from {2} where {3} order by {4} {5}", pageSize, returnColumns, tableName, where, orderColumnName, orderDesc == true ? "desc" : string.Empty);
return DbHelperSQL.Query(sql, whereParams).Tables[0];
}
/// <summary>
/// 移除空参数
/// </summary>
/// <param></param>
/// <returns></returns>
private SqlParameter[] CheckNull(SqlParameter[] paras)
{
if (paras == null)
{
return null;
}
List<SqlParameter> list = new List<SqlParameter>();
foreach (SqlParameter para in paras)
{
if (para != null) list.Add(para);
}
return list.ToArray();
}
}
}
页面后台：
复制代码 代码如下: