C#构建分页应用的实例方法分析

/// <summary>

/// 表名

/// </summary>

private const string _tableNane = "StockIC";

/// <summary>

/// 获取库存列表

/// </summary>

public List<StockIcResult> GetStockIcList(StockIcParam param)

{

  List<StockIcResult> list = new List<StockIcResult>();

  string sql = "WITH [temptablefor{0}] AS";

  sql += " (SELECT *,ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber FROM [{0}] WHERE 1=1 {2})";

  sql += " SELECT * FROM [temptablefor{0}] WHERE RowNumber BETWEEN {3} AND {4}";

  StringBuilder sqlCondition = new StringBuilder();

  List<SqlParameter> sqlParams = new List<SqlParameter>();

  //型号

  if (!String.IsNullOrEmpty(param.Model))

  {

    sqlCondition.AppendFormat(" AND Model LIKE '%{0}%'", param.Model);

  }

  //开始时间

  if (param.BeginTime.HasValue)

  {

    sqlCondition.Append(" AND CreateTime >= @BeginTime");

    sqlParams.Add(new SqlParameter("@BeginTime", param.BeginTime.Value));

  }

  //结束时间

  if (param.EndTime.HasValue)

  {

    sqlCondition.Append(" AND CreateTime < @EndTime");

    sqlParams.Add(new SqlParameter("@EndTime", param.EndTime.Value.AddDays(1)));

  }

  //排序

  if (String.IsNullOrWhiteSpace(param.OrderBy))

  {

    param.OrderBy = " CreateTime DESC";

  }

  //分页

  param.PageIndex = param.PageIndex - 1;

  Int64 startNumber = param.PageIndex * param.PageSize + 1;

  Int64 endNumber = startNumber + param.PageSize - 1;

  //拼装SQL

  sql = String.Format(sql, _tableNane, param.OrderBy, sqlCondition, startNumber, endNumber);

  //执行SQL语句

  DataSet dataSet = DBHelper.GetReader(sql.ToString(), sqlParams.ToArray());

  list = TranToList(dataSet);

  return list;

}

//分页

Int64 startNumber = (param.PageIndex - 1) * param.PageSize + 1;

Int64 endNumber = startNumber + param.PageSize - 1;

//总页数 = (数据总数 + 分页大小 -1) / 分页大小

TotalPage = (TotalCount + PageSize - 1) / PageSize;