Geeks With Blogs
Scott Kuhl Warning: I may have no idea what I am talking about!

Columns

By default, the query will return all columns in the table.  This can be changed by supplying a comma delimited list of columns to the SelectList property.

query.SelectList = Product.Columns.ProductName + ", " + Product.Columns.SupplierID;

Rows

The query will also return all rows in the table.  This can be changed several ways, the easiest of which is the Top property, which can be set to return only the given number of rows.

query.Top = "10";

(I'm not sure the reasoning, but this value needs to be a string instead of an integer.)

Filtering by a date range is possible with the AddBetweenAnd method which takes a column name, start date, and end date.  This method can be called multiple times to limit by more than one column.

query.AddBetweenAnd(Order.Columns.OrderDate, new DateTime(1980, 1, 1), DateTime.Now);

Or you can do the same thing with non-date values using AddBetweenValues.

query.AddBetweenValues(Product.Columns.ProductName, "A", "F");

The final and most powerful method is AddWhere.  Like the AddBetween methods it can be called multiple times to create a complete WHERE clause.  AddWhere has several different constructors, the simplest of which takes a column name and matching value.

query.AddWhere(Product.Columns.SupplierID, 2);

You can also supply a Comparison instead of doing an exact match. (The complete comparison possibilities are Blank, Equals, GreaterOrEquals, GreaterThan, LessOrEquals, LessThan, Like, NotEquals, and NotLike.)

query.AddWhere(Product.Columns.SupplierID, SubSonic.Comparison.GreaterThan, 2);

SubSonic also supports the concept of paging data by setting the PageSize and PageIndex properties.

query.PageIndex = 2;
query.PageSize = 5;

Find the entire series of posts here.

Posted on Thursday, December 7, 2006 1:22 PM SubSonic | Back to top


Comments on this post: Getting Started with SubSonic - Part 16, Queries - Filtering

# re: Getting Started with SubSonic - Part 16, Queries - Filtering
Requesting Gravatar...
Quoting you ....
"..(I'm not sure the reasoning, but this value needs to be a string instead of an integer.)..."

This is because the Query.Top can also be specified as Query.Top = "10%".
However, for this to happen, the 'Top' member cannot be an integer, it will have to be a string.

Left by Rajat Kaushish on Mar 02, 2008 1:54 PM

# re: Getting Started with SubSonic - Part 16, Queries - Filtering
Requesting Gravatar...
my view :AddBetweenValues is not flexible
----my english is very poor
Left by Vincent Luo on Mar 12, 2008 9:21 PM

Your comment:
 (will show your gravatar)


Copyright © Scott Kuhl | Powered by: GeeksWithBlogs.net