C#中构建一个分页的应用

快来打我* 2021-09-10 19:32 306阅读 0赞

**1、SQL分页语法**

SELECT * FROM(
    	SELECT ROW_NUMBER() OVER(ORDER BY 排序条件) AS RowNumber,* FROM 表
    	WHERE 条件
    ) TEMP_TABLE WHERE RowNumber BETWEEN (pageIndex - 1) * pageSize + 1 AND pageIndex * pageSize

**2、后台方法**

/// <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";
        }
        //分页
        Int64 startNumber = (param.PageIndex - 1) * param.PageSize + 1;
        Int64 endNumber = param.PageIndex * param.PageSize;
        //拼装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;
    }

注意：DBHelper.GetReader()方法、TranToList()方法等请自己完善。

**一些计算方法**

//分页
    Int64 startNumber = (param.PageIndex - 1) * param.PageSize + 1;
    Int64 endNumber = param.PageIndex * param.PageSize;

//总页数 = (数据总数 + 分页大小 -1) / 分页大小
    TotalPage = (TotalCount + PageSize - 1) / PageSize;