SQL：分页存储过程(优化了部分语句,附C#调用接口)（转）

最新推荐文章于 2022-11-03 11:10:28 发布
最新推荐文章于 2022-11-03 11:10:28 发布
阅读量803 收藏
点赞数
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/hefeng_aspnet/article/details/13998691

网上分页存储过程的代码很多，这个是我一直在用的经典的三层架构里提取出来的，并做了部分语句的优化...

如有更好的欢迎讨论！

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途：分页存储过程(对有主键的表效率极高) 
--说明：（优化了部分语句）
------------------------------------
 
ALTER PROCEDURE [dbo].[TP_GetRecordByPage]
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsReCount    bit = 0,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)
AS
 
declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(800)        -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
declare @strOrder varchar(400)        -- 排序类型
 
if @OrderType != 0
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by [' + @fldName +'] asc'
end
 
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
    + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
    + @strOrder
 
if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
 
if @PageIndex = 1
begin
    set @strTmp =''
    if @strWhere != ''
        set @strTmp = ' where ' + @strWhere
 
    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
        + @tblName + ']' + @strTmp + ' ' + @strOrder
end
 
if @IsReCount != 0
    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
 
exec (@strSQL)

以下是C#调用的接口方法

使用时注意一下命名空间改为你的，需要引用DBUtility操作类。

/// <summary>
/// 得到记录总数
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static int GetRecordCount(string tableName)
{
    return GetRecordCount(tableName, "");
}
/// <summary>
/// 得到记录总数
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="strWhere">筛选条件（可以不用加where）</param>
/// <returns></returns>
public static int GetRecordCount(string tableName, string strWhere)
{
    string strsql = string.Empty;
    if (string.IsNullOrEmpty(strWhere))
        strsql = "select count(*) from " + tableName;
    else
        strsql = "select count(*) from " + tableName + " where " + strWhere;
    object obj = TopkeeOA.DBUtility.DbHelperSQL.GetSingle(strsql);
    if (obj == null)
    {
        return 0;
    }
    else
    {
        return int.Parse(obj.ToString());
    }
}
 
/// <summary>
/// 分页获取数据列表（分页）
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="PageSize">每页显示多少要记录</param>
/// <param name="PageIndex">当前页</param>
/// <param name="strWhere">筛选条件（可为空）</param>
/// <param name="FieldName">排序字段（一般为主键ID）</param>
/// <param name="OrderType">排序类型（1为降序，0为升序）</param>
/// <returns></returns>
public static DataSet GetList(string tableName, int PageSize, int PageIndex, string strWhere, string FieldName, int OrderType)
{
    SqlParameter[] parameters = {
            new SqlParameter("@tblName", SqlDbType.VarChar, 255),
            new SqlParameter("@fldName", SqlDbType.VarChar, 255),
            new SqlParameter("@PageSize", SqlDbType.Int),
            new SqlParameter("@PageIndex", SqlDbType.Int),
            new SqlParameter("@IsReCount", SqlDbType.Bit),
            new SqlParameter("@OrderType", SqlDbType.Bit),
            new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
            };
    parameters[0].Value = tableName;
    parameters[1].Value = FieldName;
    parameters[2].Value = PageSize;
    parameters[3].Value = PageIndex;
    parameters[4].Value = 0;
    parameters[5].Value = OrderType;
    parameters[6].Value = strWhere;
    return TopkeeOA.DBUtility.DbHelperSQL.RunProcedure("TP_GetRecordByPage", parameters, "ds");
}

第一次使用请把“ALTER PROCEDURE ”改为“CREATE PROCEDURE ”以便创建一个新的存储过程。

AspNetPager调用示例：

private void DataBinder()
        {
            DataList1.DataSource = GetList("Test",AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strWhere, "", 0);
            DataList1.DataBind();
        }
 
        protected void AspNetPager1_PageChanged(object src, EventArgs e)
        {
            DataBinder();
        }

上面的是通用型的比较适用于sql2000，只支持主键排序，需要其它字段的大家可以改一下，下面的这个适用于sql 2005的通用分页排序存储过程，支持多字段排序，可以不一定要求是以主键排序

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[TP_GetRecordByPage2005]
 @tableName varchar(50),            --表名
 @fields varchar(2000) = '*',       --字段名(全部字段为*)
 @orderField varchar(500),          --排序字段(必须!支持多字段)
 @sqlWhere varchar(2000) = Null,    --条件语句(不用加where)
 @pageSize int,                     --每页显示多少条记录
 @pageIndex int = 1 ,               --指定当前为第几页
 @orderType int = 1,                --排序方式，非0则降序
 @TotalPage int output              --返回总页数
as
begin
    Begin Tran --开始事务
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;
    Declare @sort varchar(10);
     
    if @orderType != 0
        set @sort = ' DESC'
    else
        set @sort = ' ASC'
 
    --计算总记录数
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'select @totalRecord = count(*) from ' + @tableName
    else
        set @sql = 'select @totalRecord = count(*) from ' + @tableName + ' where ' + @sqlWhere
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
 
    --计算总页数
    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderField + @sort + ') as RowNum,' + @fields + ' from ' + @tableName
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderField + @sort + ') as RowNum,' + @fields + ' from ' + @tableName + ' where ' + @SqlWhere
     
    --处理页数超出范围情况
    if @PageIndex<=0
        Set @pageIndex = 1
 
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage
 
     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
 
    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1
 
    --继续合成sql语句
    set @Sql = @Sql + ') as ' + @tableName + ' where RowNum between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
 
    Exec(@Sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        Return @totalRecord ---返回记录总数
      End
end

以下是C#通用调用接口，需要引用DBUtility操作类

#region 公用存储过程分页相关操作
    /// <summary>
    /// 得到记录总数
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public static int GetRecordCount(string tableName)
    {
        return GetRecordCount(tableName, "");
    }
    /// <summary>
    /// 得到记录总数
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="strWhere">筛选条件（可以不用加where）</param>
    /// <returns></returns>
    public static int GetRecordCount(string tableName, string strWhere)
    {
        string strsql = string.Empty;
        if (string.IsNullOrEmpty(strWhere))
            strsql = "select count(*) from " + tableName;
        else
            strsql = "select count(*) from " + tableName + " where " + strWhere;
        object obj = TK.DBUtility.DbHelperSQL.GetSingle(strsql);
        if (obj == null)
        {
            return 0;
        }
        else
        {
            return int.Parse(obj.ToString());
        }
    }
 
    /// <summary>
    /// 分页获取数据列表（分页）
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="orderField">排序字段(必须!支持多字段，PS：多个字段用","分割，一般不为空，为空时默认为"ID")</param>
    /// <param name="sqlWhere">附加条件语句(不用加where)，可为空</param>
    /// <param name="pageSize">每页显示多少条记录</param>
    /// <param name="pageIndex">指定当前为第几页</param>
    /// <param name="OrderType">排序方式，非0则降序</param>
    /// <returns></returns>
    public static DataSet GetList(string tableName, string orderField, string sqlWhere, int pageSize, int pageIndex, int OrderType)
    {
        return GetList(tableName, "*", orderField, sqlWhere, pageSize, pageIndex, OrderType);
    }
 
 
    /// <summary>
    /// 分页获取数据列表（分页）
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="fields">字段名(PS：可为空，为空时显示全部字段，显示全部字段为"*")</param>
    /// <param name="orderField">排序字段(必须!支持多字段，PS：多个字段用","分割，一般不为空，为空时默认为"ID")</param>
    /// <param name="sqlWhere">附加条件语句(不用加where)，可为空</param>
    /// <param name="pageSize">每页显示多少条记录</param>
    /// <param name="pageIndex">指定当前为第几页</param>
    /// <param name="OrderType">排序方式，非0则降序</param>
    /// <returns></returns>
    public static DataSet GetList(string tableName, string fields, string orderField, string sqlWhere, int pageSize, int pageIndex, int OrderType)
    {
 
        SqlParameter[] parameters = {
                    new SqlParameter("@tableName", SqlDbType.VarChar, 50),
                    new SqlParameter("@fields", SqlDbType.VarChar, 2000),
                    new SqlParameter("@orderField", SqlDbType.VarChar, 500),
                    new SqlParameter("@sqlWhere", SqlDbType.VarChar,2000),
                    new SqlParameter("@pageSize", SqlDbType.Int),
                    new SqlParameter("@pageIndex", SqlDbType.Int),
                    new SqlParameter("@orderType", SqlDbType.Int),
                    new SqlParameter("@TotalPage", SqlDbType.Int)
                    };
        parameters[0].Value = tableName;
        parameters[1].Value = string.IsNullOrEmpty(fields) ? "*" : fields;
        parameters[2].Value = string.IsNullOrEmpty(orderField) ? "ID" : orderField;
        parameters[3].Value = string.IsNullOrEmpty(sqlWhere) ? "" : sqlWhere;
        parameters[4].Value = pageSize;
        parameters[5].Value = pageIndex;
        parameters[6].Value = OrderType;
        parameters[7].Direction = ParameterDirection.Output;
 
        return TK.DBUtility.DbHelperSQL.RunProcedure("TP_GetRecordByPage2005", parameters, "ds");
    }
    #endregion

sqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询计划】
12-15
代码如下: –代码一DECLARE @cc INT SELECT NewsId,ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex INTO #tb FROM news WITH(NOLOCK) WHERE NewsTypeId=@NewsTypeId AND IsShow=1 SET @cc = @@ROWCOUNT SELECT n.* FROM news AS n WITH(NOLOCK), #tb As t WHERE t.RowIndex>@PageIndex*@PageSize AND t.RowIndex<=(@PageInd
基于c#实现的分页
10-29
分页是采用c#基于事务实现的分页，实现首页、上一页、下一页、跳转
C# 语言 - 一个优雅的分页实现
12-14 233
这篇文章介绍分页对象的封装，如何优雅的对数据进行分页。 先上调用代码： 我们希望能在一个Enumerable对象后面直接.ToPagedList(pageIndex,pageSize)这样优雅的调用分页代码。 从图中可以看到，代码并没有报错，说明这是可以哒。下来就来详细说说这个.ToPagedList()方法背后的"男人"。 首先我们需要一个返回值的接口定义，IPagedLis...
C# 分页获取数据方法
07-05
C# 分页获取数据方法 SQLSERVER、ORACLE通用 绝对实用 Repository类请自行实现[数据库访问类]
c#中实现分页效果
11-21
利用集合实现分页技术，把数据库里的数据储存在集合中，对集合实现分页
C#实现Sql Server分页存储过程调用示例
### 知识点五：C#调用分页存储过程 首先，在C#中，我们需要创建一个SqlCommand对象，并设置其CommandType为StoredProcedure。然后，传入存储过程的名称和参数，并调用ExecuteReader()方法执行存储过程，最后使用...
C#调用SQL语句时乘号的用法
09-04
在C#中调用SQL语句时，可能会遇到一些语法上的问题，特别是在涉及到数学运算时，比如使用乘号(*)。由于SQL语言中，* 有特殊的含义，它用于表示所有列（通配符）或者在乘法运算中，这可能导致在编程时产生混淆。本文...
asp.net中如何调用sql存储过程实现分页
10-23
在ASP.NET中调用SQL存储过程实现分页是一种常见的数据处理技术，这有助于提高网站的性能，因为它减少了从数据库检索大量数据的需求。以下是一个详细的步骤和示例代码，解释了如何在ASP.NET中进行此操作。 1. **创建...
SqlServer 分页存储过程以及代码调用
06-01
三、调用存储过程 在代码中调用这个存储过程，你需要传递表名、每页大小和当前页码。以下是一个在C#中的调用示例： ```csharp using (SqlConnection connection = new SqlConnection(connectionString)) { ...
C# sql分页存储过程例子
08-28
本示例将详细阐述如何在C#应用程序中利用SQL Server的存储过程实现分页功能。 首先，我们要了解什么是分页。分页是一种在用户界面上显示大量数据时，将数据分成多个小块（页）的方法，每次只加载一部分数据，而不是...
分页功能实现
02-21
运用pagedatasource实现分页效果
C# 分页带数字选择的上下分页
08-08
C#分页项目实例，带数字选择的和上一页下一页的功能，完整项目实例
C# 爬虫接口
01-30
C#C/S 写的 爬虫接口，支持接口，网页，文件资源下载等数据下载！ 具体可参考博客地址：http://blog.csdn.net/qq237183141/article/details/79208909
WebApiPagination:一个带有分页的简单Web API
03-09
Web API导航 这是一个使用ASP核心Web API的简单Web API导航
C#简单的通用分页
weixin_33772645的博客
05-10 319
通用分页技术分析 需要返回不同的类型的数据--采用泛型实现该操作 需要提供不同的方法 上一页 上一页 第一页 最后一页 跳转到指定页 Demo 代码 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; ...
“DBUtility.DbHelperSQL”的类型初始值设定项引发异常 “DBUtility.DbHelperSQL”的类型初始值设定项引发异常...
05-31 839
今天遇到了一个这样的问题“DBUtility.DbHelperSQL”的类型初始值设定项引发异常“DBUtility.DbHelperSQL”的类型初始值设定项引发异常 也许有和我遇到这问题的人也在这个问题怎么解决，在没有解决这个问题的时候我也和你们现在的心情一样，也百度过了，goolge过但是还是没有解决，在这个时候我们需要冷静下来想想问什么会出现这个问题 分析一下问题出现的原因，...
C# 数据分页
qq_41128526的博客
11-03 1494
数据分页
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

csdn_aspnet

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值