I was happy to see that the SubSonic project is back in full swing and better than ever. I had a few post on the SubSonic project a while back when they were on version 2. Rob really took the bulky version 2 and slimmed it down tremendously with a lot of new features and the excellent use of VS.NET templates. With what seems to be a strong emphasis on leverage LINQ has turned into a superb product. And, of course the SubSonic project remains free, which is not the best part of the project but it doesn’t hurt any either. Actually, Rob has thrown together several different methods for accessing database data via your code. He even took a principle that I have only seen in Ruby on Rail which allows objects in code to basically create/build database schema, which is pretty powerful. I am writing this post quite late into the game since I really haven’t done any major .NET development since last year but better late than never.

I’ll be using SubSonic again on one of my next projects and posting some goodies here when I run across them. If you are not familiar with ActiveRecord or O/RM you can breeze through some of the SubSonic pages to get an idea of what is going on.

Using the subsonic project ORM for .NET clearly has tons of benefits for DAL and BLL purposes.

A great way to get a list of records from the db is to use the collection object which is created for tables and view objects from the db.

Here are two examples of how to use the .Load() method and overload to get data in a collection for which you can loop through using a foreach() statement.

Using the Query Object with Collection Object

Query q = new Query(Views.Accounts);
q.WHERE("accountID", Comparison.Equals, _accountID.value.ToString());

AccountsCollection obj1 = new AccountsCollection();
obj1.Load(q.ExecuteReader());

Using only the Collection Object

AccountsCollection obj1 = new AccountsCollection().Where("accountID", Comparison.Equals, accountID.value.ToString()).Load();

Now, to loop through the Collection
Once the collection has been instantiated and the Load() method called a foreach() loop can take place to get the records.

if (obj1.Count > 0)
{
   foreach (Accounts x in obj1)
   {
      Console.Write("My Account Name is {0}", x.AccountName);
   }
}
else
   Console.Write("There are no accounts in the collection");