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.

var param = new SqlParameter(parameter.Id, parameter.Value);, should it be var param = new SqlParameter(parameter.Id, /*your column data type, int or etc, SqlDbType.Something*/); — ah_hau, Sep 05 '14 at 08:59
could you show the contents (obfuscated, if required) of your filters collection? — decPL, Sep 05 '14 at 09:09
how about cmd.Parameters.AddWithValue(parameter.Id, parameter.Value); or cmd.Parameters.Add(parameter.Id, parameter.Value); directly without new variable declared ? — ah_hau, Sep 05 '14 at 09:10
also, put a breakpoint on your parameter to make sure it has the correct name(@offerID) and value as expected — ah_hau, Sep 05 '14 at 09:11
if your parameter.Value is null, change it to DBNull.Value for sql to understand it — ah_hau, Sep 05 '14 at 09:12
We tried all of this. But don't work. An interested thing is no exception, also seems no problem in debug. — mtaha, Sep 05 '14 at 09:13
I tihnk you do not need to do this again param.Value = parameter.Value; — Mairaj Ahmad, Sep 05 '14 at 09:43

score 1 · Accepted Answer · answered Sep 07 '14 at 20:05

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.

score 0 · Answer 2 · answered Sep 07 '14 at 20:24

Using your code on Visual Studio 2010 and SQL Server 2008 I tried to reproduce your scenario (or at least what I thought your scenario was as you weren't very specific). I created these tables: Q25682067 using int for the offer id and bit for the 'isActive' field; and Q25682067_Offers with sql_variant for each field, as suggested by your post.

CREATE TABLE [Q25682067]([ID] [int] NOT NULL, [IsActive] [bit] NOT NULL) ON [PRIMARY]
CREATE TABLE [Q25682067_Offers]([ID] [sql_variant] NOT NULL,[IsActive] [sql_variant] NOT NULL ) ON [PRIMARY]

Data pairs (1,false) and (1,true) added to each table. Now considering your filters are something like:

var filters = new Parameter[] { 
    new Parameter() {Id="@offerID ", Value=1},
    new Parameter() {Id="@isActive", Value=false}
};

where a Parameter might very swiftly be (without any consideration to OOP practices):

internal class Parameter
{
   public string Id;
   public object Value;
}

Now, this populates the data table:

cmd.CommandText = "select * from Q25682067 where ID = @offerID and IsActive = @isActive";

this does not:

cmd.CommandText = "select * from Q25682067_Offers where ID = @offerID and IsActive = @isActive";

Using the SqlParameter constructor like that binds your parameters to SqlDbType.Int and SqlDbType.Bit instead of SqlDbType.Variant, which seems to be your weapon of choice. That's why your code works with the first table definition, and not the second.

Why SqlParameter always set parameter is null?

2 Answers2