webform三层结构中分页数据

最新推荐文章于 2025-08-08 16:37:23 发布

转载最新推荐文章于 2025-08-08 16:37:23 发布 · 91 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/masonblog/p/8628767.html

本文介绍了一个SQL Server环境中创建表的示例，并提供了一种通过多条件筛选实现分页查询的方法。该方法利用了ROW_NUMBER()窗口函数来生成行号进行分页。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、表的创建语句
CREATE TABLE [dbo].[BaseInfo](
   [TNo] [varchar](50) NOT NULL,
   [VNo] [varchar](50) NOT NULL,
   [VName] [nvarchar](max) NULL,
   [OrderNo] [int] NULL,
   [TName] [nvarchar](100) NULL,
CONSTRAINT [PK_BASEINFO] PRIMARY KEY CLUSTERED
(
   [TNo] ASC,
   [VNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2、方法
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="BaseInfo">多条件查询的实体对象</param>
/// <param name="sOrderField">排序规则</param>
/// <param name="iPageSize">页中显示数据的行数</param>
/// <param name="iCurrentPageIndex">页码</param>
/// <param name="iCount">总记录行数</param>
/// <returns></returns>
public DataTable GetPageList(Model.BaseInfo BaseInfo, string sOrderField, int iPageSize, int iCurrentPageIndex, out int iCount)
{
    StringBuilder sbWhere = new StringBuilder();
    List<SqlParameter> lstParas = new List<SqlParameter>();
    string sSql = " Select * From (Select ROW_NUMBER() OVER (Order By {3}) as 'RowNumber',* FROM BaseInfo where 1=1 {0})temp Where temp.RowNumber Between {1} And {2} ";
    string sCountSql = "Select Count(1) FROM BaseInfo where 1=1 {0}";
    if (!string.IsNullOrEmpty(BaseInfo.VName))
    {
        sbWhere.Append(" And BaseInfo.VName like @VName ");
        lstParas.Add(new SqlParameter("@VName", string.Format("%{0}%", BaseInfo.VName)));
    }
    if (!string.IsNullOrEmpty(BaseInfo.TName))
    {
        sbWhere.Append(" And BaseInfo.TName like @TName ");
        lstParas.Add(new SqlParameter("@TName", string.Format("%{0}%", BaseInfo.TName)));
    }
    if (!string.IsNullOrEmpty(BaseInfo.TNo))
    {
        sbWhere.Append(" And BaseInfo.TNo=@TNo ");
        lstParas.Add(new SqlParameter("@TNo", BaseInfo.TNo));
    }
    if (!string.IsNullOrEmpty(BaseInfo.VNo))
    {
        sbWhere.Append(" And BaseInfo.VNo=@VNo ");
        lstParas.Add(new SqlParameter("@VNo", BaseInfo.VNo));
    }
    sCountSql = string.Format(sCountSql, sbWhere.ToString());
    sSql = string.Format(sSql, sbWhere.ToString(), (iCurrentPageIndex - 1) * iPageSize + 1, iCurrentPageIndex * iPageSize, sOrderField);
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(sSql);
    sbSql.Append(sCountSql);
    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), lstParas);
    iCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
    return ds.Tables[0];
}

转载于:https://www.cnblogs.com/masonblog/p/8628767.html