Microsoft.Data.SqlClient SqlParameter未传Size至Azure SQL Server致查询无结果

问题概述 #

在Azure SQL Server中查询自定义视图时，通过Microsoft.Data.SqlClient的SqlParameter传递参数后无法返回数据。已确认底层表存在对应数据，在SSMS中将参数类型从NVARCHAR改为NVARCHAR(255)后查询正常，但代码中显式设置SqlParameter的类型和Size后仍然无效。

相关代码片段 #

执行查询的C#代码 #

try {
    var resultsList = new List<object[]>();
    await using (var connection = new SqlConnection(_decryptedConnectionString)) {
        await using (var cmd = new SqlCommand(sqlQuery, connection) { CommandTimeout = DefaultCommandTimeout }) {
            if (sqlParams != null) {
                foreach (var param in sqlParams) {
                    cmd.Parameters.Add(param);
                }
            }

            await connection.OpenAsync();
            await using (var reader = await cmd.ExecuteReaderAsync()) {
                int columnCount = reader.FieldCount;

                if (sendHeaders) {
                    var headers = Enumerable.Range(0, columnCount)
                        .Select(i => reader.GetName(i) as object)
                        .ToArray();
                    resultsList.Add(headers);
                }

                while (await reader.ReadAsync()) {
                    var row = new object[columnCount];
                    reader.GetValues(row);
                    resultsList.Add(row);
                }
            }
        }
    }

    if (resultsList.IsNullOrEmpty()) { 
        return null; 
    }

    return resultsList.ToArray().To2D();
}

// further catch .. finally logic (not running in this example, as the command goes through successfully

创建SqlParameter的代码 #

// filterValues is an object[] of values, which in theory can be integers or strings, but currently it is all strings
Dictionary<string, SqlParameter> potentialParams = new();
int counter = c.Parameters.Count;

foreach (var param in filterValues) {
    var interParam = new SqlParameter($"P{counter}", SqlDbType.NVarChar, 255) {
        Value = param
    };

    potentialParams.Add($"P{counter}", interParam);
    counter++;
}

SSMS测试验证 #

在SSMS中测试时，直接声明NVARCHAR类型参数查询无结果：

DECLARE @P0 NVARCHAR = 'Jul-2023'
DECLARE @P1 NVARCHAR = 'Aug-2023'
DECLARE @P2 NVARCHAR = 'Foo'

SELECT TOP (1000) SUM([Value]) as Value
    ,[Calendar Month]
FROM [schema].[View]
WHERE [Calendar Month] IN (@P0, @P1)
    AND [FieldOne] = (@P2)

将参数改为NVARCHAR(255)后，查询正常返回预期聚合结果。

视图及底层表结构 #

视图创建脚本 #

CREATE VIEW [schema].[View] 
AS
    SELECT 
        FieldOneDimTable.Value AS [FieldOne]
        ,CalendarMonths.Value AS [Calendar Month]
        ,uvt.[Value]
    FROM 
        [schema].[UnderlyingValuesTable] AS uvt
    LEFT JOIN 
        [schema].FieldOneDimTable ON uvt.FieldOneId = [schema].FieldOneDimTable.Id
    LEFT JOIN 
        [schema].CalendarMonths ON uvt.CalendarMonthId = [schema].CalendarMonths.Id
GO

底层表结构 #

CREATE TABLE [schema].[FieldOneDimTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [varchar](255) NOT NULL,
)

CREATE TABLE [schema].[Calendar Months](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [varchar](255) NOT NULL,
)

CREATE TABLE [schema].[UnderlyingValuesTable](
    [FieldOneId] [int] NOT NULL,
    [CalendarMonthId] [int] NOT NULL,
    [Value] [bigint] NOT NULL,
)

已尝试的无效操作 #

• 显式设置SqlParameter的SqlDbType.NVarChar和Size=255
• 尝试NChar、NText、VarChar等多种SqlDbType（未正确匹配底层类型）
• 使用SqlValue代替Value赋值
• 调试确认SqlParameter的Size属性已设置，但未解决问题

解决方案 #

问题根源是参数类型与底层表字段类型不匹配：底层表的Value字段为varchar(255)（非Unicode），但代码中使用SqlDbType.NVarChar（Unicode类型），导致SQL Server执行隐式类型转换，无法正确匹配数据。

修正步骤 #

1. 匹配参数与字段类型：将SqlParameter的类型改为SqlDbType.VarChar，与底层表的varchar类型保持一致：

foreach (var param in filterValues) {
    var interParam = new SqlParameter($"P{counter}", SqlDbType.VarChar, 255) {
        Value = param ?? DBNull.Value // 处理空值，避免参数值为null导致的异常
    };

    potentialParams.Add($"P{counter}", interParam);
    counter++;
}

2. 验证参数传递：可通过Azure SQL的查询存储或SQL Server Profiler捕获实际执行的语句，确认参数类型已正确传递。

3. 避免隐式类型转换：当不同字符类型（varchar vs nvarchar）进行比较时，SQL Server会将varchar转换为nvarchar，这不仅可能导致匹配失败，还会使索引失效（若存在索引），保持类型一致是最佳实践。

额外建议 #

• 字符串参数始终明确指定Size，避免使用默认大小（NVARCHAR默认大小为1，这也是SSMS中直接用NVARCHAR无结果的原因）。
• 检查视图中的连接条件，确保关联字段类型一致，避免隐式转换影响查询逻辑。

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