sqlServer 分页存储过程

1.分页函数

点击查看代码

USE [CRM]
GO

/****** Object:  StoredProcedure [dbo].[alexpage]    Script Date: 2022/5/16 16:41:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[alexpage]
    @sqlTotal NVARCHAR(MAX), ----查询记录总数
    @varSqlcon NVARCHAR(MAX), ----sql语句完整
    @intTotalCount INT OUTPUT ----记录总数
AS
BEGIN
    DECLARE @sqlSelect    NVARCHAR(MAX) ----局部变量（sql语句），查询记录集
    DECLARE @sqlGetCount  NVARCHAR(MAX) ----局部变量（sql语句），取出记录集总数
    
    
    SET @sqlSelect =@varSqlcon
    SET @sqlGetCount = @sqlTotal
    
    
    EXEC (@sqlSelect) 
    EXEC SP_EXECUTESQL @sqlGetCount,
         N'@intTotalCount INT OUTPUT',
         @intTotalCount OUTPUT
END
GO

2.C# 封装调用方法

点击查看代码

 public static DataSet GetAlexPager(string sql,string sqlTotal,out int TotalCount, string Conn = "")
        {
            if (string.IsNullOrEmpty(Conn)) Conn = ConnStr;
            SqlConnection con = new SqlConnection(Conn);
            SqlParameter[] parms =
            {
                    new SqlParameter("@sqlTotal",sqlTotal),
                    new SqlParameter("@varSqlcon",sql),
                    new SqlParameter("@intTotalCount",SqlDbType.Int,4)
            };
            parms[2].Direction = ParameterDirection.Output;
            try {
                con.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    if (parms != null) cmd.Parameters.AddRange(parms);
                    cmd.Connection = con;
                    cmd.CommandText= "alexpage";
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter dp = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    dp.Fill(ds);
                    TotalCount = Convert.ToInt32(parms[2].Value);
                    con.Dispose();
                    return ds;
                };
            }
            catch(Exception e)
            {
                con.Close();
                con.Dispose();
                throw e;
            }
        }