Passing Parameters to Queries

SQL Parameter can be passed to all operations via the params parameter on each command. Several parameter passing schemes are supported.

Positional parameters

You can pass positional parameters by using zero based numbers prefixed by the parameter prefix (ie. @ for SQL Server).

Db.Execute("select * from customers where id=@0 and entered > @01",
           "x132", DateTime.Now.AddDays(-10) )

Anonymous Type with Properties matching Parameters

You can pass an anoymous object that has properties that match the names of the parameters used in the SQL query. The parameter object has to be a single parameter and it has to be an anonymous type - it does not work with a custom class.

Db.Execute("select * from customers where id=@id and entered > @date",
           new { Id = "x123", Date = DateTime.Now.AddDays(-10) )

DbParameter Objects

If you need more control over parameters you can also pass full DbParameter objects that allow you to specify additional information about each parameter passed, including type, size and direction.

There are a couple of variations:

Explicit DbParameter instance for Provider

Db.Execute("select * from customers where id=@id and entered > @date",
           new SqlParameter { ParameterName  = "@id", Value = "x123" },
           new SqlParameter { ParameterName  = "@date", Value = DateTime.Now.AddDays(-10) )

Using generic DbParameter instance with GetParameter()

Db.Execute("select * from customers where id=@id and entered > @date",
           Db.CreateParameter("@id", "x123" },
           Db.CreateParameter( "@date", DateTime.Now.AddDays(-10), ParameterDirection.Input )