SubSonic of the day - Collections

I am going to start spreading the goodness of SubSonic. I think SubSonic is sometimes overlooked for flashy things such as LINQ and Project JASPER. SubSonic is in a transition point (which is apparent when reading Rob Conery's blog), but I think the project still has tons of momentum and is an INVALUABLE resource for ANY .NET developer. If anything, look at the code... it's some good stuff!

I'm not going into how to setup SubSonic. That has been covered extensively on the SubSonic website... no need to repeat :)

So my first topic will be collections. Yes, Yes, I know this has been covered in other places (such as the SubSonic website) but I am going to go into the ways you can use collections and how they save your life!

Yea, about that Collection thing...

One of the cool things about the SubSonic generation is that all the generated objects basically work together. The table objects (as I call them) work with the controllers (most notably the MVC templates that were built). It also has a collection object that is generated. The collection object is basically a Generic Collection (part of System.Collections) but it has been tailored to play nice with SubSonic. I will detail the ways you can load up the table objects into the collection.

Use the SubSonic Query!!!

One option is to use the SubSonic Query (very cool tool... I will detail that later) but basically the Query syntax looks like this:

Dim qry as New SubSonic.Query(myDAL.Tables.Products)

qry.AddWhere(myDAL.Products.Columns.orderDate, Comparison.GreaterThan, DateTime.Now())

qry.OrderBy = SubSonic.OrderBy.Asc(myDAL.Products.Columns.orderDate)

Or in C#

Query qry = new SubSonic.Query(Tables.Products);

qry.AddWhere(myDAL.Products.Columns.orderDate, Comparison.GreaterThan, DateTime.Now());

qry.OrderBy = SubSonic.OrderBy.Asc(myDAL.Products.Columns.orderDate);

OK, so the query above goes to the products table, gets the records where the orderDate is greater than the current date, and sorts them by the orderDate ascending. This is sort of a simple example, but the query tool is really cool.

You can load up the query into a collection. It's easier doing it that way than pulling it from an IDataReader, trust me :). So this is how you do it:

Dim col as new myDAL.ProductsCollection()

col.LoadAndCloseReader(qry.ExecuteReader())

Or in C#

myDAL.ProductsCollection col = new myDAL.ProductsCollection();

col.LoadAndCloseReader(qry.ExecuteReader());

So now you have a list of Product objects. Now they are at your disposal! You can really do, well, anything with a collection!

No, No, No... load it using the Collection

There's another option... loading directly from the collection. Here's the example from above by loading from the collection:

Dim col as myDAL.ProductsCollection = new myDAL.ProductsCollection().Where(myDAL.Products.Columns.orderDate, Comparison.GreaterThan, DateTime.Now()).OrderByAsc(myDAL.Products.Columns.orderDate).Load()

Or in C#

myDAL.ProductsCollection col = new myDAL.ProductsCollection().Where(myDAL.Products.Columns.orderDate, Comparison.GreaterThan, DateTime.Now()).OrderByAsc(myDAL.Products.Columns.orderDate).Load();

So it's basically just the same thing.

So which do I choose

I typically load by the collection (basically because it saves me coding time and memory use), but if you are going to do things like Server-Side Paging (which I COMPLETELY recommend and will detail in a later post) you should use the query loading method.

Other Cool Things

The Sort method is useful. Instead of loading up the collection with an OrderBy, you can do a sort without the extra SQL think time. In a large system, the number of times you ping your database is important. Anything to save a little memory space.

OK, it seems like something you can just look over (since there are other flashy things SubSonic offers), but until you use a SubSonic collection object and use it in your applications, you can't really know how COOL they are. They really help your data access experience work. In the end, that's what any Data Access Tool should do, right?