subsonic查询语句例子

//(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 It's our opinion that your choice of DAL tool shouldn't tell you how to do something or make you choose one particular way over another. We just want to make your life easier. It would take a reasonably large amount of code to facilitate the things that Views and SPs already accomplish quite well. In fact if you think on it, that's why these things are there in the first place :). So our answer for the more complex queries is usually: Use a View or an SP.

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 If you need to run an update or delete, it's pretty straightforward:

//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 For performance, it's sometimes optimal to make multiple selects with one call to the DB. We do this for the Commerce Starter Kit. The Products page has to return 6 result sets for the all the product information (reviews, images, etc) so we opted to return a dataset with 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.AggregatesSometimes it's nice to have an aggregate query run, and while we're big fans of using SPs/Views for more complicated queries (and wrapping them with SubSonic), we understand that sometimes you just need a count of records. To that end you can do that with SubSonic in this way:

 

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.