(Starting with version 2.1, this query tool has been superceded. We've left all the good stuff here in place - but please be sure to view this post here if you're using 2.1+)
Let's Watch a Movie...
 | Using the Query Tool This webcast shows you some really neat tricks to do with the Query Tool. |
Dynamic Query Tool
The Dynamic Query Tool is a very versatile class that will allow you to do 90% of the queries you need. Our position on the more complex queries (using ORs, Joins, etc) is to use Views or Stored Procedures and have SubSonic wrap them for you that way.The query tool uses what's been coined a "fluent interface". This means that you can append on the conditionals in-line, avoiding excessive lines of code. This is optional but is preferred since it makes the code more readable.
A word about injection
The Query Tool builds it's statements using SubSonic's QueryCommand Object, and makes sure to use parameterized queries EVERY time. Never is SQL appended inline, so in this way we avoid to the best extent possible any SQL Injection attacks. You can use the QueryCommand object with straight inline SQL, but this is discouraged.
Simple Query
Use the built-in schema:
//(using the default WHERE)
IDataReader rdr=new Query(Product.Schema).WHERE("ProductID",2).ExecuteReader();
//(using an expression - you can also use other SQL expressions like >,<, <=, etc)
IDataReader rdr=new Query(Product.Schema).WHERE("ProductID = 2").ExecuteReader();
//Using a string for the table or view:
IDataReader rdr=new Query("Products").WHERE("ProductID",2).ExecuteReader();
IDataReader rdr=new Query("Products").BETWEEN_AND("DateExpires",DateTime.Now, DateTime.Now.AddDays(30).ExecuteReader();
If you don't want to use string values, SubSonic generates Structs for your tables and Views, so you can reference the names in code:
IDataReader rdr = new Query(Tables.Products).AddWhere(Product.Columns.ProductID, 2).ExecuteReader();
SubSonic also allows you to return results in any way you like; GetReader() is just one example. You can also use:
GetDataSet()
ExecuteScalar()
Execute()
You can engineer your results in many other ways, including sorting, "TOP" reference, and altering the select list:
Query qry = new Query(Tables.Products);
qry.Top="10";
qry.SelectList = Product.Columns.ProductName + "," + Product.Columns.UnitPrice;
qry.OrderBy = OrderBy.Desc(Product.Columns.UnitPrice);
//You can page your results if you like:
Query qry = new Query(Tables.Products);
qry.PageSize=10;
qry.PageIndex=2;
Complex Queries
However there are times when you just want to run a Simple OR, or an IN. You can do that with SubSonic (as of version 2.0):
IDataReader rdr = new Query("Products").WHERE("CategoryID = 5").AND("UnitPrice > 10").OR("CategoryID = 1").AND("UnitPrice > 10").ExecuteReader();
//Our IN method takes 3 arguments: a ListItemCollection, an ArrayList, or an object array.
//ArrayList:
ArrayList list = new ArrayList();
for (int i = 1; i <= 5; i++) {
list.Add(i);
}
IDataReader rdr = new Query("products").IN("ProductID", list).ExecuteReader();
//Object Array:
IDataReader rdr = new Query("products").IN("ProductID", new object[] { 1, 2, 3, 4, 5 }).ExecuteReader();
ListItemCollection:
ListItemCollection coll = new ListItemCollection();
for (int i = 1; i <= 5; i++) {
ListItem item = new ListItem(i.ToString(), i.ToString());
item.Selected = true;
coll.Add(item);
}
IDataReader rdr = new Query("products").IN("ProductID", coll).ExecuteReader();
Pass-through Queries
//This query updates all prices in the products table to 100 where category ID =1
Query qry = new Query(Tables.Products);
qry.AddUpdateSetting("Price", 100);
qry.AddWhere(Product.Columns.CategoryID, 1);
qry.Execute();
//To Delete:
//deletes all records with a category ID of 1
Query qry = new Query(Tables.Products);
qry.QueryType=QueryType.Delete;
qry.AddWhere(Product.Columns.CategoryID, 1);
qry.Execute();
Multiple Result Sets
//load up the product using a multi-return DataSet
//for performance, queue up the 4 SQL calls into one
string sql = "";
//Product Main Info
Query q = new Query("vwProduct");
q.AddWhere("productID", productID);
//append
sql = q.GetSql()"\r\n";
//Images
q = new Query(Commerce.Common.Image.GetTableSchema());
q.AddWhere("productID", productID);
q.OrderBy = OrderBy.Asc("listOrder");
//append
sql += q.GetSql() + "\r\n";
//Reviews
q = new Query(ProductReview.GetTableSchema());
q.AddWhere("productID", productID);
q.AddWhere("isApproved", 1);
//append
sql += q.GetSql() + "\r\n";
//Descriptors
q = new Query(ProductDescriptor.GetTableSchema());
q.AddWhere("productID", productID);
q.OrderBy = OrderBy.Asc("listOrder");
//append
sql += q.GetSql() + "\r\n";
QueryCommand cmd = new QueryCommand(sql);
cmd.AddParameter("@productID", productID,DbType.Int32);
cmd.AddParameter("@isApproved", true,DbType.Boolean);
DataSet ds = DataService.GetDataSet(cmd);
Notice the last line uses the QueryCommand object. There's more on that in another section.
Aggregates
SubSonic.Where w = new SubSonic.Where();
w.ColumnName = "categoryID";
w.ParameterValue = "1";
Query.GetCount("products", "productID", w);
The Where object is optional in and you can get a straight table count by just passing in the name of the table and the column to count on.
In addition to GetCount(), you can use GetAverage() and GetSum() in the same manner.
