HowTo: SubSonic Introduction

Updated 01/29/2008

Why Another SubSonic Introduction Blog Posting?:

In my ever growing quest for programming godliness, I've feel that its important to equip myself with all the tools that I can to get the job done.  SubSonic is one of those ORM tools that can help save me/you A LOT of initial setup time when it comes to the DAL of the application.  3 tools come to mind when I think of DAL, NHibernate, SubSonic, and LINQ.  I've tried NHibernate and frankly I wasn't impressed.  I've read about LINQ but haven't had a chance to experiment with it.  SubSonic was next on my list of "tools" to experiment with.

Intro/History:

Roughly a year ago a co-worker of mine (Dani Diaz) and I both stumbled upon SubSonic.  We had recently been using just CodeSmith and Object/Collection template to generate our DAL for our app's.  This was certainly fine and worked for our immediate needs but it was merely an attempt at creating a solid DAL.  It worked off of SProcs generated by the CodeSmith templates.  Any database change required a rebuild of both the object classes and the sprocs.  Nothing out of the ordinary there, pretty much any DAL, generator, etc. requires some manual effort when the DB changes.  At the time, since what we had been using worked fine, I overlooked SubSonic and what it had to offer.  Now that I have had an extra year or so under my belt, and also played with some other ORM's, I've come to realize that SubSonic wasn't something to be overlooked. 

In case you're not aware of what SubSonic is, according to the website, SubSonic is a "A Super High-fidelity Batman Utility Belt. SubSonic works up your DAL for you, throws in some much-needed utility functions, and generally speeds along your dev cycle".  It is certainly a hefty claim, but after using it and messing around, a claim that I think is WELL backed up. 

I'm aware that there are quite a few SubSonic introductions/tutorials out there on the web, just do a Google search and you'll find quite a few.  After using NHibernate on a current client project, I figured it was time to give SubSonic another shot.  I frankly wasn't too happy with the setup/configuration of NHibernate or the complexity of using in our project and hoped that SubSonic was easier to use.  I'll leave my conclusion till the end.

What is required/need to download:

As of time of this posting, SubSonic is at version 2.0.3, you can download it here.

In my sample/example code, I used the AdventureWorksLT database.  The full AdventureWorks database is rather large so they offer a "Limited" version which is much smaller and has enough to make decent samples out of.  The AdventureWorks database is now on CodePlex and can be found here, download the "LT" version for your specific architecture. 

The examples (can be downloaded from the bottom of this posting) are built using Visual Studio 2005 SP1. 

Install both SubSonic and the AdventureWorksLT database before continuing.

What is SubSonic?

Like the website says, "SubSonic works up your DAL for you".  SubSonic can be used basically 2 ways, as a straight up ORM tool, or as a "zero code" ORM tool. 

If your going to use it as a straight up ORM tool, you can use SubSonic to generate objects for you based off your database.  You can do this by using the bundle tool called "SubCommander".  SubCommander interrogates your database and generates objects off of the tables.  What you end up with is basically 2 classes per table.  The first is a basic object which represents your table structure.  The second object is a "collection" object which represents the "rows" in your database table (a collection of objects).  SubCommander is a command line tool, no fancy GUI here.  I won't go into all that SubCommander can do, but just open a command line window and goto the "SubCommander" directory.  Then type "sonic /?" and look through the list of commands at your disposal.

The second way to use SubSonic is to use it as a "BuildProvider" resulting in a "zero code" DAL.  This is a web app only feature, so I haven't messed around with it too much.  I encourage you to watch Rob Connery's screencast demonstrating this and how easy it is.  Since I work with both web and desktop applications, I personally want to play with the code and move it into separate projects, so this isn't a feature I personally use.

In addition to handling the "manual labor" of the DAL, SubSonic also comes with a few pre-built user controls to help speed development. 

SubSonic comes building with the following user controls (I describe the basics, check the SubSonic documentation for more options/details):

• AutoScaffold
  • The point here is to mimic the Ruby On Rails "scaffolding" concept where your up and running with your basic CRUD operations on a table with basically zero coding.  Then the point is to "take down" the scaffolding once you've built the finished product.  All you need to do is tell it the table or "object" to act on.
• QuickTable
  • The ASP.NET GridView is simple to use, but carries with it some serious overhead.  The SubSonic QuickTable was designed to give you exactly that, a quick table of your data but without the overhead that the GridView has.  Simply tell the QuickTable which table or "object" to display. 
• DropDownList
  • In almost all data-driven applications you need to add a selection control that displays a list of items, usually for "lookup" purposes.  Once again, SubSonic gives you a simple control that takes care of the grunt work, and lets you focus on your applications business logic.  The DropDownList control lets you specify the TableName and optionally the text and value fields and the beauty is that you don't have to write an "behind the scenes" code to load the dropdown and sort its values.
• ManyManyList
  • This control aides in creating a checkbox list of items for display.  This helps in the case where you have Products and Categories where a Product can have many categories, and categories can belong to many products.  I didn't get into using this control so I won't elaborate too much on it, check the documentation for more info.

Project Setup

Updated 01/29/2008

I setup the project pretty simply but broken out enough to represent a more tiered approach.

As you can see I broke out the project into 2 separate projects.  One is the "persistence" layer, which houses our objects and collections which I generated using SubCommander.  The other project is our front-end UI project which has a few examples using SubSonic calling on the Persistence project.  I broke the projects out hoping to separate the layers, but unfortunately I wasn't able to do so fully with SubSonic.  Even though the UI project should ONLY reference the Persistence project, I still had to add a reference to the SubSonic dll (Update: This is because the persistence layer is a class library and the only way to read from a "config" file is through the executing application(in this case the Web App project), hence why I had to add the SubSonic setup info into the Web.Config).  This is because the web.config contains sections which SubSonic needs to run.  (As a side note, I've had to do the same thing with NHibernate)

In a web application we put all our "setup" information into the web.config (in a desktop app, this would be put into the app.config).  Below is an example of my web.config file.

<!-- BEGIN SUBSONIC CONFIGURATION SECTIONS -->

<!-- configSections isn't a "subsonic" specific section, but the "<section>" definition is -->

<configSections> 

<section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" requirePermission="false"/>

</configSections>

<SubSonicService defaultProvider="Default">

<providers>

  <!-- Referencing a connection string setup in the connectionStrings section -->

  <!-- Also Supports MySQL, Oracle, SQL2000, SQL2005, SQLCE, SQLLite at the time of writing -->

  <add name="Default" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="advWorks" generatedNamespace="Persistence"/>

</providers>

</SubSonicService>

<!-- END SUBSONIC CONFIGURATION SECTIONS -->

<connectionStrings>

<add name="advWorks" connectionString="Data Source=coreduo;Initial Catalog=AdventureWorksLT;User ID=sa;Password=password"/>

</connectionStrings>

There are 3 things needed to setup SubSonic.

1) In the <configSections> element, tell the web.config how to read the SubSonic section.

2) Instruct SubSonic how to operate.  Tell SubSonic which DataProvider to use and which connection string to use for that provider.

3) Add a connection string.

Enough is enough, lets see some code!

Basic GridView List

To follow along here be sure to download the code samples.  I've already generated the DAL and put it into a separate project from the UI.

The first example is just your basic table dump of data in a GridView control, "List.aspx".  On the page, I load the ProductCollection and bind it to my GridView and set the GridView to auto generate columns. 

protected void Page_Load(object sender, EventArgs e)

{

    //load up all the products from our table and bind it to our datagrid.  could also use the SubSonic QuickTable

    //and avoid the "manual labor" of loading the table ourselves.  Check out the "ProductQuickTable.aspx" example

    ProductCollection products = new ProductCollection().Load();

    productGridView.AutoGenerateColumns = true;

    productGridView.DataSource = products;

    productGridView.DataBind();

}

There isn't anything too fancy going on here,  I get a collection of Products and then bind them to the GridView.

Basic GridView List With Where Clause

In this example I build upon the previous example by adding a dropdown of Product Categories and allow the user to view all the products of a selected category.

protected void Page_Load(object sender, EventArgs e)

{

    //add event handler so that when the user selects a different dropdown item the event fires

    categoriesDropDown.SelectedIndexChanged += new EventHandler(categoriesDropDown_SelectedIndexChanged);

}

public void categoriesDropDown_SelectedIndexChanged(object sender, EventArgs e)

{

    if (!string.IsNullOrEmpty(categoriesDropDown.SelectedValue))

    {

        //get all the products that match the selected product category.

        ProductCollection products = new ProductCollection().Where("ProductCategoryID", categoriesDropDown.SelectedValue).Load();

        productsGridView.AutoGenerateColumns = true;

        productsGridView.DataSource = products;

        productsGridView.DataBind();

    }

    else

    {

        //user selected an invalid item, so clear out the datagrid

        productsGridView.DataSource = null;

        productsGridView.DataBind();

    }

}

This time when the user select a category from the dropdown I pass that ProductCategoryID value in our Where clause to filter our data and then bind the returned Product Collection to the GridView.  Much like NHibernate, you filter and query your collections by specify "expressions".  With SubSonic you can do Where, Order By's, Between's, etc., the same "expressions" you'd use if you were writing SQL.

Add A Product

As with displaying data, adding data is just as easy with SubSonic.  Since objects are generated for you, you can instantiate an object, set its required fields and then persist the data to the database.

As you can see in the image, I've created a few textbox's and dropdowns to make a simple add product page with only the required fields.  Here is the code that gets executed on the Save button click event.

protected void saveProductButton_Click(object sender, EventArgs e)

{

    try

    {

        //create a new product and set the "necessary" fields based on the users input to the screen.

        Product productToAdd = new Product();

        productToAdd.Name = nameTextBox.Text;

        productToAdd.ProductNumber = productNumberTextBox.Text;

        productToAdd.StandardCost = Convert.ToDecimal(stdCostTextBox.Text);

        productToAdd.ListPrice = Convert.ToDecimal(listPriceTextBox.Text);

        productToAdd.ProductCategoryID = Convert.ToInt32(prodCategoriesDropDown.SelectedValue);

        productToAdd.ProductModelID = Convert.ToInt32(prodModelDropDown.SelectedValue);

        productToAdd.SellStartDate = DateTime.Parse(sellStartDateTextBox.Text);

        productToAdd.Rowguid = Guid.NewGuid();

        productToAdd.ModifiedDate = DateTime.Now;

        //persist our new proudct

        productToAdd.Save();

        //pull up our new product and display it in the grid for user verification

        ProductCollection products = new ProductCollection().Where("ProductID", productToAdd.ProductID).Load();

        productsGridView.AutoGenerateColumns = true;

        productsGridView.DataSource = products;

        productsGridView.DataBind();

    }

    catch (Exception ex)

    {

        //display any errors here

    }

}

Once we create the Product object and set the required fields, we persist the object and then pull it up and bind it to our GridView so that the user knows the object was successfully added.

Delete A Product

Again, deleting a object with SubSonic is easy.  Tell SubSonic which object based on the ID value, and SubSonic takes care of the rest.  This example has a GridView that I add a Delete command button do and then handle the row deleting event to delete the object.

protected void Page_Load(object sender, EventArgs e)

{

    productsGridView.RowDeleting += new GridViewDeleteEventHandler(productsGridView_RowDeleting);

    if (!IsPostBack)

    {

        //if we're not a postback, load our datagrid with a delete "command", and load up all the products

        productsGridView.AutoGenerateDeleteButton = true;

        productsGridView.AutoGenerateColumns = true;

        productsGridView.DataSource = LoadProducts();

        productsGridView.DataKeyNames = new string[] { "ProductID" };

        productsGridView.DataBind();

    }

}

public void productsGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

    //get the product id of the selected row

    int productID = Convert.ToInt32(((GridView)sender).DataKeys[e.RowIndex]["ProductID"]);

    //delete the selected product

    Product.Delete(productID);

    //rebind the updated data to the grid

    productsGridView.DataSource = LoadProducts();

    productsGridView.DataBind();

}

//get back a list of ALL products

private ProductCollection LoadProducts()

{

    //return back a listing of all the products in our table

    return new ProductCollection().Load();

}

All we need to do to delete the Product from the database is pass the Product ID to the Delete method of our Product Object class, simple as that.  Once again SubSonic does the heavy lifting for me.

Update A Product

Seeing a reoccurring theme here?  SubSonic makes your CRUD tasks easy and painless.  In the update example, I added the same textbox's and dropdowns as I did for the Add Product example.  I also have a GridView on the page which I added a hyperlink column so that when it's clicked we pass a query string variable to the page and change it into "edit" mode.  Once updated we display the specific Product in the GridView.

protected void Page_Load(object sender, EventArgs e)

{

    //set the ProductID property to the request variable ID

    ProductID = Request["id"];

    if (!IsPostBack && ProductID == null)

    {

        //if we're NOT a postback and no ID was specified, then load EVERY thing from the product table and allow user to select

        //which one they want to edit.

        //add a link so that we can post to ourselves, but with an ID of a record to update

        HyperLinkField grdLink = new HyperLinkField();

        grdLink.Text = "Update";

        grdLink.DataNavigateUrlFields = new string[] { "ProductID" };

        grdLink.DataNavigateUrlFormatString = "UpdateProduct.aspx?id={0}";

        productsGridView.Columns.Add(grdLink);

        productsGridView.AutoGenerateColumns = true;

        productsGridView.DataSource = LoadProducts(null);   //load all the products

        productsGridView.DataBind();

    }

    else if (!IsPostBack && ProductID != null)

    {

        //if we're NOT a postback, but the ID variable isn't null, then load the record matching that ID for update

        //rebind the datagrid

        productsGridView.DataSource = LoadProducts(ProductID);

        productsGridView.DataBind();

        //fetch the product specified by the request variable

        Product prod = Product.FetchByID(ProductID);

        if (prod != null)

        {

            //set the fields so that we can update the record.

            ltProductID.Text = prod.ProductID.ToString();

            nameTextBox.Text = prod.Name;

            productNumberTextBox.Text = prod.ProductNumber;

            stdCostTextBox.Text = prod.StandardCost.ToString("F");

            listPriceTextBox.Text = prod.ListPrice.ToString("F");

            sellStartDateTextBox.Text = prod.SellStartDate.ToShortDateString();

            //load the dropdowns, which are basically foreign key columns

            LoadCategories(prod.ProductCategoryID.ToString());

            LoadModels(prod.ProductModelID.ToString());

        }

    }

}

#region Load DropDowns and Products

// code omitted from posting but is in source zip

#endregion

protected void saveProductButton_Click(object sender, EventArgs e)

{

    try

    {

        //update our record based on the product id

        Product prodToUpdate = Product.FetchByID(ProductID);

        prodToUpdate.Name = nameTextBox.Text;

        prodToUpdate.ProductNumber = productNumberTextBox.Text;

        prodToUpdate.StandardCost = Convert.ToDecimal(stdCostTextBox.Text);

        prodToUpdate.ListPrice = Convert.ToDecimal(listPriceTextBox.Text);

        prodToUpdate.ProductCategoryID = Convert.ToInt32(prodCategoriesDropDown.SelectedValue);

        prodToUpdate.ProductModelID = Convert.ToInt32(prodModelDropDown.SelectedValue);

        prodToUpdate.SellStartDate = DateTime.Parse(sellStartDateTextBox.Text);

        prodToUpdate.ModifiedDate = DateTime.Now;

        //persist our updated proudct

        prodToUpdate.Save();

        //pull up our updated product and display it in the grid

        productsGridView.DataSource = LoadProducts(ProductID);

        productsGridView.DataBind();

    }

    catch (Exception ex)

    {

        //disply error here

    }

}

When the user hits the Save button, we fetch the product we want to update based on the Query String Product ID.  Then, we update the Product objects properties and call its Save() method to persist it to the database.  SubSonic is smart enough to know whether it needs to update or insert a record.

SubSonic QuickTable Example

As mentioned earlier in this post, SubSonic comes with a barebones, low-overhead Quick Table control and all you have to do is tell it the table or "object" to display.  This gets rid of the hassle of a GridView and the best part is you don't have to write a single line of "data binding" code.

I'd show you the code behind, but there isn't any.  Here's the html source.

<!-- use subsonic quicktable to quickly display all table data, also add a LinkToPage for edits -->

<ss:QuickTable

    id="quickTbl"         

    TableName="Product"

    PageSize="20"

    LinkToPage="UpdateProduct.aspx"

    runat="server" />

SubSonic AutoScaffold Example

I saved the best for last here.  The AutoScaffold control does basically everything I demo'd above, but with basically ZERO effort on your part.  Add the SubSonic user control to your page and that is it, you've got all your CRUD operations taken care of and you didn't have to write any code.

Again, no code behind to show, but here is how the html source looks.

<!-- use subsonic auto scaffold to quickly get a page for your tables CRUD (Create, Read, Update, Delete) operations -->

<ss:Scaffold 

    ID="customerScaffold"

    TableName="Customer" 

    runat="server" />

The AutoScaffold does all our work for us.  In addition, it adds paging and sorting by default to the list view.  It does everything we need to perform our CRUD operations.

Conclusion

Updated 01/29/2008

Everything wasn't 100% easy, I'll admit that.  I probably spent about 3 hours of banging my head getting SubSonic to work in a multiple project solution (Update: as mentioned above, this is because of the way .NET is designed, SubSonic can only read a config file from the executing application, in our case here, the Web application project, hence the web.config file needs to have the SubSonic config info).  Every tutorial and example I found, including the ones on the SubSonic site, always used SubSonic in just 1 project.  Other than that initial hurdle, SubSonic made coding a breeze.  I personally found SubSonic easier to use and setup than NHibernate, and no need for messing "mapping" files.  I didn't get a chance to play with stored procedures with SubSonic, but from everything I've read and the screencasts I watched, SubSonic handles your sprocs with easy (these guys thought of everything!).  I also didn't play around with the custom "query" code that you can write with SubSonic, again, it appears to be pretty damn easy to use.  I am looking forward to using SubSonic on a client project and see if it stands up to the real test.

Source Code

SubSonicTest.zip (71 kb)