SubSonic quick brain dump: Part 1

This post is just going to be a quick brain dump on SubSonic as I am in the mood. I am no way an expert at SubSonic, these code snippets are simply what I have implemented in a recent project to get the job done. There may be better ways to do the same thing.

In my last post I briefly described how to get started with SubSonic. So you have generated your classes for the DAL and now you want to get at that data. Well the project I have recently been working on has very simple requirements some of which I will cover here.

1. Simple binding to a GridView. You want to work with a simple collection of data. SubSonic creates these automatically for each of your tables within the db. This code snippet creates a data collection from the TelephoneList table, calls the Load method and finally binds it to a GridView called gvTelephoneList.

MyDAL.TblTelephoneListCollection telephoneList= new MyDAL.TblTelephoneListCollection();
telephoneList.Load();
gvTelephoneList.DataSource = telephoneList; gvTelephoneList.DataBind();

2. Adding a bit of a query to the mix.

TblTelephoneListCollection telephoneListPerson = new TblTelephoneListCollection()
.Where("Type", SubSonic.Comparison.Equals, "Person")
.OrderByAsc("Name");
telephoneListPerson.Load();
gvTelephoneListPerson.DataSource = telephoneListPerson ; gvTelephoneListPerson.DataBind();

3. Table fields as properties. Remember that each field within your database table is exposed as a property so you can iterate through the collection writing out the properties.

for (int i = 0; i < telephoneListPerson.Count; i++)
     {
      Console.WriteLine(telephoneListPerson[i].Name);
     }

4. Ok so I need a more complex query. Simply using what is called the Query Tool to define the query and pass that in to the FetchByQuery method. My table has fields of SubCategory and IsVisable and all I want are the TOP 5 Newsletters ordered by id DESC.

Query qryNewsletters = new Query(Tables.TblDocument);
qryNewsletters.QueryType = QueryType.Select;
qryNewsletters.AddWhere(TblDocument.Columns.SubCategory, "Newsletters");
qryNewsletters.AddWhere
(TblDocument.Columns.IsVisable, "1");
qryNewsletters.Top = "5";
qryNewsletters.ORDER_BY("id DESC");
dlLatestNewsletters.DataSource = TblDocument.FetchByQuery(qryNewsletters);
dlLatestNewsletters.DataBind();

That will do for now. I will cover simple updates and deletes in a later post.