Why SqlParameter always set parameter is 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;
}

Here is my code. Variable "p" is my sqlQuery string. Variable "filters" is my parameter list. For example: parameter.Id = "@offerId" (as string) and parameter.Value = 1230 (as Integer) Also my query is like that : "select * from Offers where ID = @offerID and IsActive = @isActive"

when pass into cmd.ExecuteReader(), in IntelliTrace shows my query like that:

--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

We tried lots of method for set. But always variables set null.

Answer 1

IntelliTrace currently only supports this kind of type information for log files from MMA scenarios. In your scenario, the type information from SqlParameter isn't collected; as a result all the variables in the query default to SQL_VARIANT with null values.