SubSonic: 2.1 (Pakala) Preview, Part 2

Wednesday, January 16, 2008 -

We're working full guns on the next release of SubSonic, fixing bugs implementing literally hundreds of the features you've asked for. I wrote previously about our new Query tool, and we've introduced some changes to it to make it even more usable and readable. We've also implemented the Repository Pattern, which a lot of people favor over Active Record. In this post I'll talk about that and a few other things that we've been working on. No, I don't know when :).

Query2 Updates
A lot of people have asked to be able to use the new Query tool without having to identify the schema for each table in the query. To that end I've put in some generics overrides, so now you can write your query this way:

    ProductCollection p = Northwind.DB.Select()
        .From<Northwind.Product>()
        .InnerJoin<Northwind.Category>()
        .Where("CategoryName").Like("c%")
        .ExecuteAsCollection<Northwind.ProductCollection>();

We've also added the ability to return typed results from Stored Procedures. We don't generate the classes for you (yet), but if you declare a class for holding the results:

    /// <summary>
    /// Class for holding SP Results for CustOrderHist
    /// </summary>
    class CustomerOrder {
        private string productName;

        public string ProductName {
            get { return productName; }
            set { productName = value; }
        }
        private int total;

        public int Total {
            get { return total; }
            set { total = value; }
        }

    }

You can execute your SP like this:

    List<CustomerOrder> orders = Northwind.SPs.CustOrderHist("ALFKI")
        .ExecuteTypedList<CustomerOrder>();

The New Repository Pattern
SubSonic has always used ActiveRecord, and many people have found that to be a bit heavy for use in .NET applications. To address this, I created a set of alternate templates a while back (that I called MVC, which in retrospect was pretty confusing I'm sure) which reworked the generated classes and created "dumb classes" more or less (classes that aren't database-aware).

With 2.1 we're introducing an alternative that not only addresses the ActiveRecord "issue", but also easily allows you to create your own base classes without having to create a whole new set of templates. If you want to use the Repository Pattern, you can by simply specifying the "RepositoryRecord" base class to your provider (note that you can put in any base class here now - including your own - and ActiveRecord is the default):

    <add name="NorthwindRepository" type="SubSonic.SqlDataProvider, SubSonic" 
         connectionStringName="Northwind" 
         generatedNamespace="NorthwindRepository" 
         tableBaseClass="RepositoryRecord"/>

This will add Repository methods(Get, Save, Delete, Destroy) to the DB class in the namespace "NorthwindRepository" (this is just my test namespace). Each table-based object (as opposed to View) is now blissfully ignorant of the database and is not allows to Fetch() or Save().

Using the Repository-based classes is as you'd expect. Here's my unit test to verify CRUD operations:

    //Get a record
    Product p = NorthwindRepository.DB.Get<NorthwindRepository.Product>(1);
    Assert.IsTrue(p.ProductName == "Chai");

    p.UnitPrice = 200;
    NorthwindRepository.DB.Save(p);

    //pull it back out and test
    p = NorthwindRepository.DB.Get<NorthwindRepository.Product>(1);
    Assert.IsTrue(p.UnitPrice==200);

    //add a new product
    p = new Product();
    p.ProductName = "Test Product";
    p.SupplierID = 1;
    p.CategoryID = 1;
    p.QuantityPerUnit = "0";
    p.UnitPrice = 0;
    p.UnitsInStock = 0;
    p.UnitsOnOrder = 0;
    p.ReorderLevel = 0;
    p.Discontinued = false;
    p.DateCreated = DateTime.Today;

    //save
    DB.Save(p);
    int newID = p.ProductID;
    Assert.IsTrue(newID > 0);

    //pull the new record back out
    p = NorthwindRepository.DB.Get<NorthwindRepository.Product>(newID);
    Assert.IsTrue(p.ProductName == "Test Product");

    //delete it - this sets Deleted to "true"
    NorthwindRepository.DB.Delete(p);
    p = NorthwindRepository.DB.Get<NorthwindRepository.Product>(newID);
    Assert.IsTrue(p.Deleted == true);


    //destroy it
    NorthwindRepository.DB.Destroy(p);
    p = NorthwindRepository.DB.Get<NorthwindRepository.Product>(newID);
    Assert.IsTrue(p.IsLoaded==false);

    //now destroy all test data
    NorthwindRepository.DB.Destroy<NorthwindRepository.Product>("ProductName", "Test Product");

It's worth noting here that I've gone to great lengths to keep from introducing breaking changes - even in reworking our core classes. It's not as tidy as I'd like in there, but we'll clean this all up with 3.0 :).

In addition to the CRUD methods above, the DB class acts as a query factory (as you can see in the first example with generics), and you can ask it for every type of Select/Insert/Update/Delete query that you want. These methods get generated no matter which base class you choose - ActiveRecord or Repository.

Rev 274
All of the above items are checked in, including a silly bug that Shawn Oster somehow snuck in (who gave that guy commit!). Someday Shawn you'll have to tell me what a "Neep" is.

I could really use some help testing this, and moreover "doing the VB thing" with our templates to make sure they all synch properly.

That Sneaky Eric
Eric has been massively geeking out (and ignoring his new wife) with his new Pet Project called "SubStage" - though that name may change (I'm thinking SubSonicFunk after his "canofunk" alias?). I won't steal his thunder - but I will say that it's a pretty damn nice tool and will help many, many people who've had a hard time with docs and conventions. I keep prodding him to throw up a preview post... if he doesn't I might just spill the beans in the coming week...