CODEWITHSUNDEEP
c#.netsql-server-cesql-server-ce-4petapoco
Jon
Jon

Reputation: 40032

PetaPoco not returning expected data

I have a BIT data type on one of my columns.

I have written a query that does SELECT * FROM TABLE WHERE BITCOLUMN <> @0

It works fine if I pass in 1 or 0 but if I pass in 3 PetaPoco doesn't return the results I expect.

Executing the SQL in a query window does return all records when I use 3 as the parameter value.

Any ideas?

UPDATE: If I use string SQL = "SELECT * FROM TABLE WHERE BITCOLUMN <> " + MethodParam; This returns data as expected.

Upvotes: 2

Views: 3770

Answers (3)

Jon Snow
Jon Snow

Reputation: 11

Use new Sql() not Append.

You should use Query = new PetaPoco.Sql( over Query = PetaPoco.Sql.Append

Upvotes: 0

Trevor Pilley
Trevor Pilley

Reputation: 16403

I think it will be because when you use a parametrised sql string, you are passing in int with a value of 3 in as one of the args. PetaPoco will then create an IDataParameter for that argument and the DbType will be set to DbType.Int32 by default since PetaPoco has no idea what the underlying table column type is.

Upvotes: 0

Yin
Yin

Reputation: 301

Could you tell me what result you expecting? According to MSDN, A bit column can either be 1, 0 or null. It does not make senses to me when you pass 3 to it, since it will select all rows.

And my quick test shows that Petapoco behaves as expected.

using (var database = new Database("sql"))
{
    string sql = "SELECT COUNT(*) FROM TBLTESTBIT WHERE BITCOLUMN <> " + "3";
    var test = database.ExecuteScalar<long>("SELECT COUNT(*) FROM TBLTESTBIT WHERE BITCOLUMN <> @0", 3);
    var test2 = database.ExecuteScalar<long>(sql);

    Console.WriteLine(test == test2); // this output true
    Console.Read();
}

Upvotes: 4

Related Questions