Adhoc queries - activa/iridium GitHub Wiki

If the data provider supports it (currently all SQL data providers do), you can execute raw queries on the database. For SQL providers, this means you can execute SQL queries directly on the database.

The following methods are available on the StorageContext class:

• int Execute(string sql, object parameters)
• IENumerable<T> Query<T>(string sql, object parameters)
• IEnumerable<Dictionary<string,object>> Query(string sql, object parameters)
• T QueryScalar<T>(string sql, object parameters)
• IEnumerable<T> QueryScalars<T>(string sql, object parameters)

#####Execute(sql, parameters)

Runs a query and returns the number of records affected by the query.

#####Query(sql, parameters)

Runs a query and returns a list of Dictionary<string,object> objects, one for each record retrieved from the database.

#####Query<T>(sql, parameters)

Runs a query and returns a list of custom objects, one for each record retrieved from the database. The fields are mapped to properties or fields in the custom class. Every attempt will be made to map the field to the class, even if the data types don't match (for example, a text field from the database can be mapped to a numeric field in the object if the text value can be converted to a number in some way)

Every ad-hoc query method accepts a parameters object. This should be an anonymous object where the property names are treated as parameter names. Parameters should be referenced in the query as @parameterName.

For example:

var n = dbContext.QueryScalar<int>(
            "select count(*) from Product where Price>@price and InStock>@stock",
             new { price = 100.0, stock = 5 }
           );

// n = number of products costing more than $100 with more than 5 items in stock