使用Dapper调用分页存储过程返回重复值问题求助

数据库共30条记录，使用Dapper调用存储过程分页时，每次返回的结果存在重复值。

现有代码 #

C# 代码 #

IEnumerable<TEntity> entities;
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@page", page, DbType.Int32, ParameterDirection.Input);
parameters.Add("@pageSize", pageSize, DbType.Int32, ParameterDirection.Input);
parameters.Add("@search", search, DbType.String, ParameterDirection.Input);

try
{
    _connection.Open();
    entities = await _connection.QueryAsync<TEntity>($"{storedProcedure}", parameters);
}
catch (Exception)
{
    return null;
}
finally
{
    _connection.Dispose();
    _connection.Close();
}

return entities.ToList();

存储过程代码 #

ALTER PROCEDURE [dbo].[SP_GetCategories]
    @search nvarchar(300) = '',
    @page int = 1,
    @pageSize int = 10
AS
BEGIN
    SET @page = (@page - 1) * @pageSize;
    DECLARE @Sql Nvarchar(800)
    SET @Sql=' select * from Dy.Category As C With(Nolock) where  IsDeleted=0 ';

    IF @search IS NOT NULL AND @search <> ''
    BEGIN
        SET @Sql=@Sql+' AND C.Title like N''%''' +@search +'''%'' ' ;
    END

    SET @Sql = @Sql +' Order By CreateTime Desc OFFSET '+CAST(@page AS NVARCHAR(100))+

' ROWS FETCH NEXT ' +CAST(@pageSize AS NVARCHAR(100))+' ROWS ONLY';

    EXEC sp_executesql @Sql
END

问题原因及修复方案 #

1. 排序不稳定导致分页重复 #

当前存储过程仅用CreateTime Desc排序，若存在多条记录的CreateTime完全相同，SQL Server的OFFSET/FETCH无法保证稳定的排序顺序，不同分页请求可能返回重复的记录，或跳过部分记录。

修复存储过程的排序逻辑：
在排序条件后添加唯一标识字段（如主键Id），确保排序的唯一性和稳定性：

SET @Sql = @Sql +' Order By CreateTime Desc, Id Desc OFFSET '+CAST(@page AS NVARCHAR(100))+
' ROWS FETCH NEXT ' +CAST(@pageSize AS NVARCHAR(100))+' ROWS ONLY';

2. 数据库连接管理错误 #

C#代码中finally块先调用_connection.Dispose()再调用_connection.Close()，这是错误的：Dispose()方法会自动关闭并释放连接资源，后续的Close()调用无意义，甚至可能引发异常。

优化连接管理：
使用using语句自动管理连接生命周期，无需手动调用Open()、Close()和Dispose()，同时显式指定命令类型为存储过程：

DynamicParameters parameters = new DynamicParameters();
parameters.Add("@page", page, DbType.Int32, ParameterDirection.Input);
parameters.Add("@pageSize", pageSize, DbType.Int32, ParameterDirection.Input);
parameters.Add("@search", search, DbType.String, ParameterDirection.Input);

using (var conn = _connection)
{
    try
    {
        await conn.OpenAsync();
        var entities = await conn.QueryAsync<TEntity>(
            storedProcedure, 
            parameters, 
            commandType: CommandType.StoredProcedure
        );
        return entities.ToList();
    }
    catch (Exception)
    {
        return null;
    }
}

3. 动态SQL的SQL注入风险（额外优化） #

存储过程中直接拼接@search参数到动态SQL存在SQL注入风险，应改用参数化查询：

ALTER PROCEDURE [dbo].[SP_GetCategories]
    @search nvarchar(300) = '',
    @page int = 1,
    @pageSize int = 10
AS
BEGIN
    SET @page = (@page - 1) * @pageSize;
    DECLARE @Sql Nvarchar(800)
    SET @Sql=' select * from Dy.Category As C With(Nolock) where  IsDeleted=0 ';

    IF @search IS NOT NULL AND @search <> ''
    BEGIN
        SET @Sql=@Sql+' AND C.Title like N''%'' + @search + ''%'' ' ;
    END

    SET @Sql = @Sql +' Order By CreateTime Desc, Id Desc OFFSET '+CAST(@page AS NVARCHAR(100))+
' ROWS FETCH NEXT ' +CAST(@pageSize AS NVARCHAR(100))+' ROWS ONLY';

    EXEC sp_executesql @Sql, N'@search nvarchar(300)', @search = @search;
END

内容的提问来源于stack exchange，提问作者movahedijam