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()


Or in C#

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


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?

kick it on
Published Tuesday, September 18, 2007 8:03 PM by zowens


# re: SubSonic of the day - Collections

Ahhh... that is a nice overview of collections, Kevin.

I hope I don't duplicate content... I just want to spread the SubSonic "love" arround :)

Wednesday, September 19, 2007 3:01 PM by zowens

# re: SubSonic of the day - Collections

Thak you so much for the "load up the query into a collection" part. I have a query which has a few fields that my C# code needs to generate.

What I did is I created a dummy table in the format I needed: (id, name1, name2, dynamicField1, dynamicField2).

Then I created a Stored Proc to return the data that comes from the database: (id, name1, name2).

I call the Stored Proc by using GetReader() and then load my dummy table collection with the DataReader. And lastly I iterate through the collection and set the two dynamic fields.

Much better than what I was doing... Created a DataSet Schema and ran GetDataSet<MySchema>().

Thursday, September 20, 2007 6:35 PM by Denny Ferrassoli

# re: SubSonic of the day - Collections


I agree. SubSonic really does save time and energy!

SPs can also be used... I forgot to mention that. Maybe I should do a post on SubSonic SP wrappers.

Thursday, September 20, 2007 6:50 PM by Zack Owens

# re: SubSonic of the day - Collections

great article, i m using SubSonic too. :)

Monday, October 1, 2007 11:10 PM by Zharfan Mazli