如何在C#中将整数列表传入SQL Server表值参数？

错误原因分析 #

1. 参数类型覆盖错误：配置studyIdParam和dimensionParam时，错误地修改了respondentParam的SqlDbType，将原本的Structured（表值参数类型）改为Int，导致DataTable无法转换为Int32类型，直接触发异常。
2. 值类型转DataTable逻辑失效：当传入List<int>时，typeof(int).GetProperties()返回空数组，生成的DataTable没有列，无法匹配SQL的RespondentTypeTable表类型（需要名为Id的INT列）。
3. 不必要的GetChanges()调用：如果respondentIdTable没有修改操作，GetChanges()会返回null，导致传入空的表值参数。

修复步骤 #

1. 修正参数配置错误 #

修改SqlCommand参数设置，确保每个参数的类型对应正确，同时修正存储过程名称匹配问题：

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // 直接使用原始DataTable，无需调用GetChanges()
    DataTable respondentIdTable = dataTableConverter.ToDataTable(respondentIds);

    // 存储过程名称要与数据库中创建的一致
    SqlCommand cmd = new SqlCommand("GetSensitivityDataForDimension", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    // 配置表值参数
    SqlParameter respondentParam = cmd.Parameters.AddWithValue("@respondentIdType", respondentIdTable);
    respondentParam.SqlDbType = SqlDbType.Structured;
    respondentParam.TypeName = "dbo.RespondentTypeTable";

    // 配置studyId参数
    SqlParameter studyIdParam = cmd.Parameters.AddWithValue("@studyId", studyId);
    studyIdParam.SqlDbType = SqlDbType.Int;

    // 配置dimension参数
    SqlParameter dimensionParam = cmd.Parameters.AddWithValue("@dimension", dimension);
    dimensionParam.SqlDbType = SqlDbType.Int;

    // 存储过程返回结果集，使用ExecuteReader而非ExecuteNonQuery
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // 此处处理返回的结果（例如映射到实体类）
        // 示例：
        // List<AllUtilityDataDTO> result = new List<AllUtilityDataDTO>();
        // while (reader.Read())
        // {
        //     result.Add(new AllUtilityDataDTO
        //     {
        //         AttributeLevelId = reader.GetInt32(0),
        //         AttributeLevelName = reader.GetString(1),
        //         // 其他字段映射...
        //     });
        // }
    }
}

2. 修复List转DataTable的转换器 #

针对值类型（如int）单独处理，生成符合要求的Id列：

public class ListtoDataTableConverter
{
    public DataTable ToDataTable<T>(List<T> items)
    {
        DataTable dataTable = new DataTable();

        // 处理值类型（如int）的情况
        if (typeof(T).IsValueType && !typeof(T).IsClass)
        {
            // 对应SQL表类型的列名"Id"
            dataTable.Columns.Add("Id", typeof(T));
            foreach (T item in items)
            {
                dataTable.Rows.Add(item);
            }
        }
        else
        {
            // 原有引用类型处理逻辑
            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in props)
            {
                dataTable.Columns.Add(prop.Name);
            }
            foreach (T item in items)
            {
                object[] values = new object[props.Length];
                for (int i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }
        }

        return dataTable;
    }
}

3. 可选：修复存储过程的临时表问题 #

存储过程中无需手动销毁临时表，临时表会在存储过程执行完毕后自动释放，移除多余的DROP TABLE语句：

CREATE PROCEDURE GetSensitivityDataForDimension(@respondentIdType [dbo].[RespondentTypeTable] READONLY, @studyId INT, @dimension INT )
AS
BEGIN

CREATE TABLE #AllUtilityDataDTO  
(
    AttributeLevelId INT NOT NULL,
    AttributeLevelName NVARCHAR(MAX),
    AttributeId INT NOT NULL,
    AttributeName NVARCHAR(MAX),
    RespondentId INT NOT NULL,
    UtilityValue INT
);

INSERT INTO #AllUtilityDataDTO 
(
    AttributeLevelId,
    AttributeLevelName,
    AttributeId,
    AttributeName,
    RespondentId,
    UtilityValue
)
SELECT al.Id AS AttributeLevelId,
	   al.DisplayText AS AttributeLevelName,
	   a.Id AS AttributeId,
	   a.DisplayText AS AttributeName,
	   usd.RespondentId,
	   usd.Value AS UtilityValue
FROM dbo.Attribute a
	INNER JOIN dbo.AttributeLevel al
		ON al.AttributeId = a.Id
	INNER JOIN dbo.UtilityScoreData usd
		ON usd.AttributeLevelId = al.Id
WHERE a.StudyId = @studyId
	  AND usd.RespondentId IN (SELECT Id FROM @respondentIdType)
	  AND
	  (
		  (usd.Dimension IS NOT NULL AND usd.Dimension = @dimension)
		  OR
		  (usd.Dimension IS NULL AND @dimension = 1)
	  );

SELECT * FROM #AllUtilityDataDTO

-- 移除多余的DROP TABLE语句
END
GO

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