Peta Poco: How to pass multiple parameters to a Fetch<T>

Question

My Code:

string sql = "SELECT * FROM people where birthday >= @t1 AND birthday <= @t2"
DateTime t1 = DateTime.Parse("01-01-2000");
DateTime t2 = DateTime.Parse("01-01-2001");
var results = db.Fetch<Person>(sql, t1,t2);

This code produces an error:

additional information: Parameter '@t1' specified but none of the passed arguments  have a property with this name (in 'SELECT......

I wish to know what the correct syntax is?

Vitriol

Answer 1

I'm not really familiar with petapoco, but by the error message, it seems to be trying to bind the parameters to the property of the object you pass in to the Fetch call. If so, try something like this instead:

var results = db.Fetch<Person>(sql, new {t1 = t1, t2 = t2});

Sprag

Shorter version: var results = db.Fetch<Person>(sql, new {t1, t2}); – Ocker

@Eduardo: Hey, thanks. Didn't know that you could infer the property names that way. – Sprag

Answer 2

The documentation isn't overly direct, but for positional parameters you need to use zero-indexed placeholder names @0, @1... in your query. Try:

string sql = "SELECT * FROM people where birthday >= @0 AND birthday <= @1"

If you use named placeholders, PetaPoco looks for an object with that property name, hence the error message you're getting. For example:

sql.Append("AND date_created>=@start AND date_created<=@end", 
    new 
        { 
            start=DateTime.UtcNow.AddDays(-2), 
            end=DateTime.UtcNow 
        }
    );

Leong