C#SQL SERVER分页

最新推荐文章于 2022-03-24 10:35:26 发布

程序猿够钟

最新推荐文章于 2022-03-24 10:35:26 发布

阅读量7k

点赞数 1

分类专栏： C# 文章标签： c# sqlserver

本文链接：https://blog.csdn.net/qq_42756590/article/details/118999440

版权

C# 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

这段代码展示了如何使用C#进行SQL分页查询，同时处理输入参数和输出参数，计算总记录数，并通过SqlDataAdapter填充DataSet。核心部分是利用ROW_NUMBER()函数实现分页，以及设置参数值为DBNull.Value来避免空值错误。

摘要由CSDN通过智能技术生成

public static DataSet GetPageList(string selectCommandText, List<SqlParameter> paramList, int pageSize, int pageIndex, string filedOrder, out string recordTotal)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            StringBuilder SQLString = new StringBuilder();
            SQLString.Append(@"
            BEGIN
                DECLARE @beginRow INT;
                DECLARE @endRow INT;

                SET NOCOUNT ON

                SET @beginRow = (@pageIndex - 1) * @pageSize + 1;
                SET @endRow = @pageIndex * @pageSize;

                --输出参数为总记录数
                SELECT @recordTotal = COUNT(1) FROM ("+ @selectCommandText + @") AS my_temp;

                --主查询返回结果集
                SELECT * FROM (SELECT ROW_NUMBER() OVER (order by @orderString) AS rows ,* FROM (" + @selectCommandText + @") AS A) AS main_temp 
                WHERE rows BETWEEN CAST(@beginRow AS VARCHAR) AND CAST(@endRow AS VARCHAR);
                SET NOCOUNT OFF
            END");
            connection.Open();
            SqlCommand cmd = new SqlCommand(SQLString.ToString(), connection);
            if (paramList != null && paramList.Count > 0)
            {
                foreach (SqlParameter param in paramList)
                {
                    if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Input) &&
                    (param.Value == null))
                    {
                        param.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(param);
                }
            }
            recordTotal = "0";
            cmd.Parameters.Add(new SqlParameter("@recordTotal", SqlDbType.Int, 8) { Value = int.Parse(recordTotal), Direction = ParameterDirection.Output });
            cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int, 8) { Value = pageSize });
            cmd.Parameters.Add(new SqlParameter("@pageIndex", SqlDbType.Int, 8) { Value = pageIndex });
            cmd.Parameters.Add(new SqlParameter("@orderString", SqlDbType.VarChar, 1000) { Value = filedOrder });
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = cmd;
            DataSet ds = new DataSet();
            sqlDA.Fill(ds);
            recordTotal = cmd.Parameters["@recordTotal"].Value != DBNull.Value ? cmd.Parameters["@recordTotal"].Value.ToString() : "0";
            connection.Close();
            cmd.Parameters.Clear();
            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}