c# - 为什么SqlParameter总是设置参数为null？

Question

using (SqlCommand cmd = new SqlCommand())
{
    DataTable dt = new DataTable();
    cmd.CommandText = p;
    cmd.CommandType = CommandType.Text;
    SqlConnection con = new SqlConnection("--");
    cmd.Connection = con;
    cmd.Connection.Open();
    foreach (var parameter in filters)
    {
        var type = parameter.Value.GetType();
        var param = new SqlParameter(parameter.Id, parameter.Value);
        param.Direction = ParameterDirection.Input;
        param.Value = parameter.Value;

        cmd.Parameters.Add(param);
    }
    dt.Load(cmd.ExecuteReader());
    cmd.Connection.Close();
    return dt;
}

这是我的代码。变量“p”是我的 sqlQuery 字符串。变量“过滤器”是我的参数列表。例如:parameter.Id = "@offerId"(作为字符串)和 parameter.Value = 1230(作为整数)我的查询也是这样的:“select * from Offers where ID = @offerID and IsActive = @isActive”

当传递到 cmd.ExecuteReader() 时，IntelliTrace 会像这样显示我的查询:

--The data may be truncated and may not represent the query that was run on the server
USE [DB];

GO

--Type and value data was not available for the following variables. Their values have been set to defaults.
DECLARE @offerID AS SQL_VARIANT;
DECLARE @isActive AS SQL_VARIANT;
SET @offerID = NULL;
SET @isActive = NULL;

select  *  from Offers where  ID = @offerID and IsActive = @isActive

我们尝试了很多set方法。但总是变量设置为空。

Answer 1

IntelliTrace 目前只支持来自 MMA scenarios 的日志文件的这种类型信息.在您的场景中，不会收集来自 SqlParameter 的类型信息；结果，查询中的所有变量都默认为具有空值的 SQL_VARIANT。