SubSonic: All Your Database Are Belong To Us

SubSonic: Added Wildcard Methods

Rob Conery — Thu, 21 Aug 2008 20:05:07 GMT

With revision 482 I added in the ability to work with string values and "LIKE" queries a little more intuitively.

On a recent post, a commenter (liviu) left this comment:

"...Because Subsonic generated code i found it unexplainable why i cannot write something like:

Select<Product>().Where( Schema.Product.ProductName.StartsWith("a"))

or

Select<Product>().Where(Schema.Product.ProductType == "Cool Engine")."

The good news here is that in the second case it's a matter of me writing more complete samples. You can indeed do this query this way:

IList<Product> products=new Select().From<Product>()
   .Where(Product.ProductTypeColum).IsEqualTo("Cool Engine")
   .ExecuteTypedCollection<Product>();

However liviu is correct - you can't use "StartsWith" or "EndsWith" - until now. I just checked in changeset 481 which allows you to use StartsWith() and EndsWith() as well as ContainsString() - here's my Unit Tests:

        [Test]
        public void Select_Using_StartsWith_C_ShouldReturn_9_Records() {


            int records = new Select().From<Product>()
                .Where(Northwind.Product.ProductNameColumn).StartsWith("c")
                .GetRecordCount();
            Assert.AreEqual(9, records);
        }

        [Test]
        public void Select_Using_EndsWith_S_ShouldReturn_9_Records() {


            int records = new Select().From<Product>()
                .Where(Northwind.Product.ProductNameColumn)
                .EndsWith("s").GetRecordCount();
            Assert.AreEqual(9, records);
        }


        [Test]
        public void Select_Using_Contains_Ch_ShouldReturn_14_Records() {


            int records = new Select().From<Product>()
                .Where(Northwind.Product.ProductNameColumn)
                .ContainsString("ch").GetRecordCount();

            Assert.AreEqual(14, records);
        }

Many thanks to liviu for the suggestions!

Using SubStage

Rob Conery — Fri, 11 Jul 2008 01:23:00 GMT

Eric put together a really nice "IDE" for working with SubSonic 2.1. This is a walkthrough on what it is and how to use it.

Making It Easy
We're really trying to make it simple for people use SubSonic and yes, we know, our docs aren't up to speed. In this screencast I go into how you can use our new tool, SubStage, to see all the options available to you with our Providers, and also how you can generate your code precisely how you want to - all visually.

Double-click the video to make it fullscreen - or you can download the WMV here

Select Queries

Rob Conery — Fri, 11 Jul 2008 01:22:00 GMT

Here are some sample Select queries using SubSonic 2.1. These come directly from our Unit tests, if you see an issue or a correction, please let us know!

All of these samples are made against the stock Northwind database.

Simple Select with string columns

            int records = new Select("productID").
                 From("Products").GetRecordCount();

            Assert.IsTrue(records == 77);

Simple Select with typed columns

            int records = new Select(Product.ProductIDColumn, Product.ProductNameColumn).
                From<Product>().GetRecordCount();
            Assert.IsTrue(records == 77);

Returning a Single object

            Product p = new Select().From<Product>().
               Where("ProductID").IsEqualTo(1).ExecuteSingle<Product>();
            Assert.IsNotNull(p);

Returning all columns

            int records = new Select().From("Products").GetRecordCount();
            Assert.IsTrue(records == 77);

Simple Where

            int records = new Select().From("Products").
                Where("categoryID").IsEqualTo(5).GetRecordCount();
            Assert.AreEqual(7, records);

Simple Where with And (as Collection)

            ProductCollection products =
                DB.Select().From("Products")
                    .Where("categoryID").IsEqualTo(5)
                    .And("productid").IsGreaterThan(50)
                    .ExecuteAsCollection<ProductCollection>();

Simple Inner Join

            SubSonic.SqlQuery q = new Select("productid").From(OrderDetail.Schema)
                .InnerJoin(Product.Schema)
                .Where("CategoryID").IsEqualTo(5);

Simple Join With Table Enum

            SubSonic.SqlQuery q = new Select().From(Tables.OrderDetail)
                .InnerJoin(Tables.Product)
                .Where("CategoryID").IsEqualTo(5);

Multiple Joins As Collection

            CustomerCollection customersByCategory = new Select()
                .From(Customer.Schema)
                .InnerJoin(Order.Schema)
                .InnerJoin(OrderDetail.OrderIDColumn, Order.OrderIDColumn)
                .InnerJoin(Product.ProductIDColumn, OrderDetail.ProductIDColumn)
                .Where("CategoryID").IsEqualTo(5)
                .ExecuteAsCollection<CustomerCollection>();

Left Outer Join With Generics

            SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
                .From<Customer>()
                .LeftOuterJoin<Order>();

Left Outer Join With Schema

            SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
                .From(Customer.Schema)
                .LeftOuterJoin(Order.CustomerIDColumn, Customer.CustomerIDColumn);

Left Outer Join With Magic Strings

            SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
                .From("Customers")
                .LeftOuterJoin("Orders");

Simple Select With Collection Result

            ProductCollection p = Select.AllColumnsFrom<Product>()
                .ExecuteAsCollection<ProductCollection>();

Simple Select With LIKE

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

Using Nested Where/And/Or

            ProductCollection products = Select.AllColumnsFrom<Product>()
                .WhereExpression("categoryID").IsEqualTo(5).And("productid").IsGreaterThan(10)
                .OrExpression("categoryID").IsEqualTo(2).And("productID").IsBetweenAnd(2, 5)
                .ExecuteAsCollection<ProductCollection>();

            ProductCollection products = Select.AllColumnsFrom<Product>()
                .WhereExpression("categoryID").IsEqualTo(5).And("productid").IsGreaterThan(10)
                .Or("categoryID").IsEqualTo(2).AndExpression("productID").IsBetweenAnd(2, 5)
                .ExecuteAsCollection<ProductCollection>();

Simple Paged Query

            SubSonic.SqlQuery q = Select.AllColumnsFrom<Product>().
               Paged(1, 20).Where("productid").IsLessThan(100);

Paged Query With Join

            SubSonic.SqlQuery q = new Select("ProductId", "ProductName", "CategoryName").
                From("Products").InnerJoin(Category.Schema).Paged(1, 20);

Paged View

            SubSonic.SqlQuery q = new Select().From(Invoice.Schema).Paged(1, 20);

Simple IN Query

            int records = new Select().From(Product.Schema)
                .Where("productid").In(1, 2, 3, 4, 5)
                .GetRecordCount();
            Assert.IsTrue(records == 5);

Using IN With Nested Select

            int records = Select.AllColumnsFrom<Product>()
                .Where("productid")
                .In(
                new Select("productid").From(Product.Schema)
                    .Where("categoryid").IsEqualTo(5)
                )
                .GetRecordCount();

Using Multiple INs

            SubSonic.SqlQuery query = new Select()
                .From(Product.Schema)
                .Where(Product.CategoryIDColumn).In(2)
                .And(Product.SupplierIDColumn).In(3);

Aggregate Queries

Rob Conery — Fri, 11 Jul 2008 01:01:00 GMT

Writing an aggregate query (SUM, COUNT, AVG) shouldn't be painful. We've tried to take the pain out of this for you.

Rather than drone on, I thought it would be helpful to see our Unit tests. In this way, if you find an issue you can patch us with a Unit test of your own!

At their core, Aggregates are just special Select() queries - and you can use all the same Where/Or/And constraints that you normally would.

All of these queries are using stock Northwind database.

Summing

    double result = new
        Select(Aggregate.Sum("UnitPrice*Quantity", "ProductSales"))
        .From(OrderDetail.Schema)
        .ExecuteScalar<double>();

    result = Math.Round(result, 2);
    Assert.IsTrue(result == 1354458.59);

Averages

    const double expected = 55.5922;

    // overload #1
    double result = new
        Select(Aggregate.Avg("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.Avg(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.Avg("UnitPrice", "AverageUnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.Avg(Product.UnitPriceColumn, "AverageUnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Max

    const double expected = 100.00;

    // overload #1
    double result = new
        Select(Aggregate.Max("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.Max(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.Max("UnitPrice", "MostExpensive"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.Max(Product.UnitPriceColumn, "MostExpensive"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Min

    const double expected = 2.50;

    // overload #1
    double result = new
        Select(Aggregate.Min("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.Min(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.Min("UnitPrice", "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.Min(Product.UnitPriceColumn, "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Standard Deviation

    const double expected = 42.7698669325723;

    // overload #1
    double result = new
        Select(Aggregate.StandardDeviation("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.StandardDeviation(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.StandardDeviation("UnitPrice", "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.StandardDeviation(Product.UnitPriceColumn, "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Average With GroupBy

    int records = new
        Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"))
        .From("Order Details")
        .Where("Quantity").IsEqualTo(120)
        .Where(Aggregate.Avg("UnitPrice"))
        .IsGreaterThan(10)
        .GetRecordCount();

    Assert.AreEqual(5, records);

Average With Simple Where

    int records = new
        Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"))
        .From("Order Details")
        .Where(Aggregate.Avg("UnitPrice"))
        .IsGreaterThan(50)
        .GetRecordCount();

    Assert.AreEqual(7, records);

SubSonic: Writing Decoupled, Testable Code With SubSonic 2.1

Rob Conery — Wed, 18 Jun 2008 21:04:51 GMT

I've heard/read rumblings over the last few months that "SubSonic is tightly coupled" and therefore you have to "drag it around" with you in your project. I can see why people might think this - ActiveRecord is not the most testable thing in the world :). I've really tried to push SubSonic into the TDD realm and thought it might be a good idea to show how you can structure up a highly testable, decoupled application using SubSonic as your Data Access tool.

The Repository Pattern
This is usually the first choice in Data Access patterns when it comes to writing testable, decoupled code. So I'm going to use that today. If you arent' familiar with it, you may want to read more here. Essentially, the idea here is that you want to abstract your data access/query bits as much as possible from the rest of your application - including your model. I go into this a lot in the MVC Storefront series.

Rather than dive into explanations here, I'll just show the code :).

Solution Setup
I'm using ASP.NET MVC - but you don't have to use this. You can use whatever application setup you like. I'm dividing out the data access and test projects as well, so that I have 4 total in my application:

ASP.NET MVC Website
Data Access Project (Class Library)
Business Logice (Class Library)
Test Project (using MS Test)

Data Access
Let's assume, for now, that I'm writing tests for everything I do. And my tests tell me that I need to setup a Catalog Repository that talks to my DB (for today it will be Northwind). My tests tell me I need to have a Product so the first thing I do is setup a class to represent a Product:

    public class Product {

        public int ProductID { get; set; }
        public string ProductName { get; set; }
        public decimal UnitPrice { get; set; }

    }

Next, I need to have a method on my Repository that will retrieve products from the DB. For those of you following along with the MVC Storefront, I'm going to use the classic Repository Pattern here, without using IQueryable.

I define an interface that will abstract the implementation of the Repository:

    public interface ICatalogRepository {

        IList<Product> GetProducts();

    }

Finally, I need to setup an implementation of this interface, and it will use SubSonic. I reference and setup SubSonic in the Data Access class, and then generate all the good stuff SubSonic will create for me here. Note that I can use ActiveRecord or RepositoryRecord - it doesn't matter, and that's just how I want it. All I'm going to use is the new Query tool - none of the base objects that are created (except for the Repository querying).

After generating the code, my project looks like this:

Note: you'll notice an App.config in the project. This is ONLY to tell SubCommander how/where to run the generation. You don't need to do this (you can send everything in to SubCommander by command line)- I just find it handy.

In the "Generated" folder are all the classes SubSonic generates for you to use and include a full schema look at your DB. This will allow us to query as we need to.

Mapping
If you're not use to using ORM tool (Object-Relational Mappers), you may not know why I want to "duplicate" the classes that SubSonic generates, versus the one I created here in my "Model" folder.

The reason comes down to what's known as "impedance mismatch" - the idea that my DB structure will not follow my application structure (and therefore OO principles). Moreover, if you tie your DB structure to your application, eventually you will end up with some pain points as your DB grows - and to mitigate these you compromise good DB practices. The opposite is true as well - you "bend" your application to allow for easier use of the DB. I'll end this discussion here :).

What I need to do now is implement ICatalogRepository using SubSonic, and map the Northwind Products to my Model. This is easy to do with SubSonic, using "ExecuteTypedList<>":

    public class SubSonicCatalogRepository:ICatalogRepository {

        public IList<Product> GetProducts() {

            return Northwind.DB.Select("ProductID", "ProductName", "UnitPrice")
                .From<Northwind.Product>().ExecuteTypedList<Product>();


        }
    }

ExecuteTypedList<> tries to match the names of the returned columns to the names of the properties of the passed-in type. In this example they match exactly - and that's not completely real world. You can get around this by aliasing the columns - in the same way you'd alias a SQL call:

            return Northwind.DB.Select("ProductID as 'ID'", "ProductName as 'Name'", "UnitPrice as 'Price'")
                .From<Northwind.Product>().ExecuteTypedList<Product>();

Note: Using "as" is ANSI SQL, and will work with most DB providers - including MySQL 5.x and above.

You can also Stored Procedures here, or Views - up to you.

The Test
In the Test Application, all you need to do is:

Reference your Data Access project
Setup SubSonic's configuration (since this is an execution end point)

I'm looking into hard-coding the connection string info (like Linq To Sql does) so you can omit the config stuff - but I feel weird about that. Would love to hear some feedback...

This is my Test Project:

Note that I don't have any reference to SubSonic here :). I don't need it - it's abstracted nicely away. Now I can write my tests, stubbing out ICatalogRepository with a TestCatalogRepository, and use Dependency Injection to inject the SubSonicCatalogRepository as needed :).

To make sure that SubSonic is playing nicely here, you can write an integration test:

        [TestMethod]
        public void SubSonic_Should_Return_Product_List() {

            ICatalogRepository rep = new SubSonicCatalogRepository();
            IList<Product> products = rep.GetProducts();
            Assert.AreEqual(77, products.Count);
        }

And here's the result:

Yes, I know I shouldn't hard-code Count values, but this is a sample :).

Putting It All Together
The one thing I haven't shown yet is my Business Logic bits. The one thing you don't want to do in your application is instantiate a Repository class directly - that's "coupling" and ties your application to the implementation.

To get around this, you can use Dependency Injection to "inject" the Repository into your business logic class at runtime. If you want to know more about "why" you'd do this, I did a nice Dependency Injection screencast with Jeremy Miller (creator of StructureMap) on the subject.

Normally, your Service classes should implement your business logic so your Repository only concerns itself with getting the data you want. An example of this is that you may grab a list of Products and want to check inventory levels, whether the shopper is eligible for discounts - etc. You'll probably want to keep this type of logic in the application tier:

    public class CatalogService {

        ICatalogRepository _repository;
        public CatalogService(ICatalogRepository repository) {
            _repository = repository;
        }

        public IList<Product> GetProducts() {
            List<Product> products = _repository.GetProducts();
            
            //inventory checks

            //discount setups

            //cross-sell correlation

            //bundling...

        }

    }

The main thing about this setup is that the CatalogService class is FORCED to take a Repository in the constructor - this is exactly what we want in order to make our code more testable and decoupled.

Normally you might put some Linq To Sql code in here, or perhaps straight-up ADO stuff with an IDataReader. That may be a great call with a smaller app, but with anything that you want to maintain over the next few years, you're better off trying to keep it cleanly separated like this.

An example of why you might want this decoupling is you might decide to move your application to .NET 3.5 in the future and may want to use SubSonic 3.0, or Linq To Sql (or NHib... whatever). It's trivial to swap the parts out if you follow this architectural pattern.

Dependency Injection
It may seem like a bummer to have to instantiate all these things just to get at a list of Products, but that's where DI comes in. If we plug in a DI tool (like StructureMap), we can rely on it to do the associations we need:

            ForRequestedType<ICatalogRepository>()
                .TheDefaultIsConcreteType<SubSonicCatalogRepository>();

Setting this up is really, really easy and if you're interested in learning more about DI, take a look at my screencast with Jeremy Miller.

I'm very interested to hear your comments. This setup is pretty tried and true, and if you'd like to see more about how to do this type of thing, I'd push you to check out the MVC Storefront. I'm not using SubSonic there (yet) because it doesn't support IQueryable - but the ideas are pretty much the same.

SubSonic: Getting Started

Rob Conery — Fri, 06 Jun 2008 19:31:00 GMT

Code and Screencast to help you get SubSonic setup in your project.

Watch An Intro Screencast

Open up the Web.config file - we need to do a little surgery. First, declare a config section for SubSonic, right under the <configuration tag:

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

Next, add a connection to your database (let's assume it's Northwind, located on the local SQLExpress instance):

<connectionStrings>
  <add name="Northwind" connectionString="Data Source=localhost\SQLExpress; Database=Northwind; Integrated Security=true;"/>
</connectionStrings>

Next, we need to define our providers. Each SubSonic provider defines one database:

  <SubSonicService defaultProvider="Northwind">
    <providers>
      <clear/>
      <add name="Northwind" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="Northwind" generatedNamespace="Northwind"/>
    </providers>
  </SubSonicService>

Notice how here, we told the provider that it's generatedNamespace is "Northwind" - this means that in our code we'll be able to access all the generated bits using "Northwind.[TableName].Fetch()"

Next, if you want SubSonic to generate the DAL "behind the scenes", with no need for you to interfere, you'll need to declare a BuildProvider. A BuildProvider is a process the runs when Visual Studio compiles an application, or when a file is added to the App_Code folder. In our case we're going to define a BuildProvider that gets run for every file in the App_Code directory, having an extension of ".abp" (notice that this goes under the "<compilation" tag):

    <compilation debug="true" defaultLanguage="C#">
      <!--########################## SubSonic Build Provider ###############################-->
      <!--This will NOT WORK in Medium Trust-->
      <buildProviders>
        <add extension=".abp" type="SubSonic.BuildProvider, SubSonic"/>
      </buildProviders>

If you will be deploying your application to an ISP that uses shared hosting, you probably won't be able to use the BuildProvider since it won't run in MediumTrust. If this is the case, you can use SubSonicCentral (the web site downloaded with SubSonic) to generate the code for you, or you can use the Command Line tool.

Watch an intro here

SubSonic: Using Migrations

Rob Conery — Fri, 06 Jun 2008 03:48:49 GMT

I've spent the passed few weeks polishing up some features for our 2.1 release (aka Pakala), and I've been paying particular attention to Migrations - something I promised would be ready to go a few months back. As of changeset 452 (made today, just now), Migrations are reasonably solid.

As Always, There's a Video
I like blogs with code, but singalongs are more fun. I made a quick video today (about 15 minutes) of how you can use Migrations in your project.

You can watch it here.

If you're more into the code and how they work - read on! All the code you will see below is viewable in the video.

Give Shawn Some Love
I laid the groundwork for Migrations a while ago, but didn't have the bandwidth to give it the love it deserved. Frankly I don't know if I was smart enough. I asked Shawn Oster to help out and he took his massive swingin geek smarts and put together a pretty cool set of functionality.

So if you see Shawn at the local Denver Gamer Shop, setting up for the Monday Night D&D Showdown (LARP-fest), give him a wizardly high-five!

The Code>>>
The Migration system works by basically "sucking" out the Migration classes from your project, reading in the code, and then executing it in a virtual compiler. This may seem complex, but it's actually pretty simple and at it's core is what we do to generate the code files anyway.

To create a Migration, you have to follow our Conventional system and create a class file, something like

001_Initial.cs

... and place that class file into a folder called "Migrations" off the root of your project (you can override this directory convention - see below).

This class file must have one class (call it whatever you like), and it has to inherit from SubSonic.Migration:

using System;
using System.Collections.Generic;
using System.Text;
using SubSonic;

namespace MigrationSample.Migrations {

    public class Migration001:Migration {
    }
}

Each "Migration" consists of changing from one version to another, either up or down. The code, therefore, follows this logic and offers two methods for you to tell the DB what to look like.

For the first example (001_Init), this is version one. The Up() method therefore is transitioning the DB from version 0 to version 1, so we need to put in some code to tell it what we want it to do:

        public override void Up() {

            //Create the Records Table
            TableSchema.Table records = CreateTableWithKey("Records");
            records.AddColumn("RecordName");
            records.AddColumn("GroupID", System.Data.DbType.Int32);
            records.AddColumn("LabelID", System.Data.DbType.Int32);

            AddSubSonicStateColumns(records);

            //Create the Groups Table
            TableSchema.Table groups = CreateTableWithKey("Groups");
            groups.AddColumn("GroupName");
            AddSubSonicStateColumns(groups);

            //Link them
            CreateForeignKey(groups.GetColumn("id"), records.GetColumn("groupID"));


        }

For the Down() method, we need to reverse, exactly, everything we did with Up():

        public override void Down() {
            TableSchema.Table records = GetTable("records");
            TableSchema.Table groups = GetTable("groups");

            //drop the FK
            DropForeignKey(groups.GetColumn("id"), records.GetColumn("groupID"));
            
            DropTable("Records");
            DropTable("Groups");
        }

*Note: I'm working on the syntax to drop the FK constraint. I know it's heavy.

If you needed to alter a column, you use:

    AlterColumn("records", "RecordName", System.Data.DbType.String, 800);

You can change the column's length, type, name, and nullability if you need to. You can also remove a column from a table:

    RemoveColumn("records", "groupid");

Iterations
If your client says to you "hey great, you made a Records table - but you forgot Labels! You have labelID, where's labels!" - it's time to write another Migration, and migrate from version 1 to version 2:

Following our convention, add another class file to the Migrations folder:

002_AddLabels

Next up, add the code to Up/Down our migration:

namespace MigrationSample.Migrations {
    
    public class Migration002:Migration {
        public override void Up() {

            //add the labels table
            TableSchema.Table labels = CreateTableWithKey("Labels", "labelID");
            labels.AddColumn("LabelName");
            AddSubSonicStateColumns(labels);


            Execute("INSERT INTO Labels(labelname) VALUES('Capitol')");
            Execute("INSERT INTO Labels(labelname) VALUES('Arista')");
            Execute("INSERT INTO Labels(labelname) VALUES('Virgin')");

            TableSchema.Table records = GetTable("records");
            CreateForeignKey(labels.GetColumn("labelID"), records.GetColumn("id"));

        }

        public override void Down() {
            TableSchema.Table records = GetTable("records");
            TableSchema.Table labels = GetTable("labels");

            //drop the FK
            DropForeignKey(labels.GetColumn("labelID"), records.GetColumn("id"));
            DropTable("labels");
        }
    }
}

Yes! Migrations also allow you to add data! I know that inline script is probably not what you had in mind ;) - I'll have this worked out by the time we go final with 2.1 (allowing you to use our query tool) but for now - the ability is there.

Now, to execute...

sonic.exe /migrate

You can set this up like you do with other SubCommander commands - please see the video for how to make this happen!

Comparisons
There are other things out there (like DB projects with VS) and I go into some of this in the video (nudge nudge). This isn't a versioning tool per se - it's a development tool. If you like scripts, more power to ya. Migrations take a little getting used to, that's for sure.

Caveats
This stuff may still be a tad rough. We've tested it a lot, but whenever you talk about tweaking DB schema and code... well there's a reason there's not many Migration solutions in .NET land right now :). Please be patient and help us to get this up to par.

All About Rails

Rob Conery — Thu, 17 Apr 2008 16:43:00 GMT

This is a screencast I did last summer (2007) wherein I walked through some of the neat features of Rails - specifically the things that inspired SubSonic.

SubSonic 2.1 Beta 3 Now Available

Eric Kemp — Thu, 10 Apr 2008 23:15:00 GMT

Well, these days it looks like it takes a new SubSonic release to get me to actually write a blog post, so I guess I'll have to crank up the rate that we're putting out builds! In any event, I'm happy to announce the third beta of SubSonic 2.1 is now available on CodePlex. There are a ton of fixes in this release, and it's starting to look very solid, with core already faster and more solid that 2.0.3. The next milestone will be Release Candidate 1, which we will get to GA as fast we can, especially since we're very anxious to start taking advantage of the C# 3.0 language features.

While we're not officially announcing it, so as to properly set initial expectations, this release features the first migration capabilities, which Rob and Shawn have poured a ton of work in to. But if you're brave, and feeling like diving into the code, have at it.

I'd also like to extend a special thanks to Yitzchok AKA adminjew for his contributions to this release. Yitzchok not only contributed a really nice patch that allows providers to be loaded from external assemblies, he has truly embraced the often thankless job of moderating the forums, answering question after question while people like me get to do the fun stuff. Thank you Yitzchok!

Beta 3 Fix/Feature List

Work Item 11714 - DataReader Exceptions Can Cause Memory Leaks
Work Item 11740 - Scripting May Fail With Large Schemas/Data Sets
Work Item 11933 - Views Are Not Properly Paged Under Sql Server 2000
Work Item 12375 - WriteTrace Throws Security Exception with Medium Trust
Work Item 12786 - WriteTrace Throws Security Exception with Medium Trust (Duplicate)
Work Item 14660 - LoadFromPost() Doesn't Fill DirtyColumn Collection
Work Item 15015 - SubStage Generates Controller Classes when RepositoryRecord is Used
Work Item 15469 - SqlQuery Doesn't Perform Type Conversion on Constraint Values
Work Item 15590 - SqlDataProvider.ExecuteTransaction must not close the DB connection
Work Item 15594 - SqlQuery: Aggregates/Tables not aliased properly when using OracleDataProvider
Work Item 15596 - SubSonic.Sugar.Dates Incorrectly Identifies Weekends/Weekdays
Work Item 15605 - RepositoryRecord not Tagged as [Serializable]
Work Item 15649 - ExecuteJoinedDataSet Throws Exception When Sorting on Joined Column
Work Item 15652 - SqlQuery.CloseExpression() Causes "Index was Outside the Bounds of the Array" Error
Work Item 15660 - Many-to-Many Save[ClassName]Map Delete Methods Do Not Specify Data Type When Adding Parameters
Work Item 15661 - Many-to-Many Save[ClassName]Map Methods Use Incorrect Data Type for Foreign Primary Key
Work Item 15688 - ANSISqlGenerator.GenerateConstraints() Does Not Set DbType
Work Item 15689 - SqlQuery.SetConstraintParams() Loses Parameters When Building "NotIn" Queries
Work Item 15696 - Sql2005Generator.BuildPagedSelectStatement() Sets Incorrect Paging Boundaries
Work Item 15725 - AndExpression/OrExpression Improperly Identify a Closed Expression as Open
Work Item 15747 - Object Names in the Form "[Name]TypeCode" are Inexplicably Changed to "[Name]Type"
Work Item 15753 - SqlCE Provider Incorrectly Set CommandTimeout Value
Work Item 15771 - ExecuteJoinedDataSet Incorrectly Inserts "PK" Column When First Ordinal Column Is a Foreign Key
Work Item 15779 - SqlQuery: INNER JOIN with WHERE Clause Can Result in 'Ambiguous column name' Error
Work Item 15795 - Repository Get[ObjectName] Methods Leave Open Data Readers
Work Item 15796 - SubStage Doesn't Fully Refresh Database Schema
Work Item 15799 - SqlQuery: INNER JOIN with WHERE Clause Can Result in 'Ambiguous column name' Error (Duplicate)
Work Item 15823 - SqlQuery.SetConstraintParams() Loses Parameters When Building "NotIn" Queries (Duplicate)
Work Item 15841 - ANSISqlGenerator.GenerateCommandLine() Does Not Properly Comma-Delimit Select List When Passing Literal Values
Work Item 15939 - GetValue<T>(string columnName) in TableSchema May Fail with Non-Nullable GUIDs
Work Item 16012 - SqlQuery.ExecuteScalar() Does Not Properly Handle/Cast DBNull Return Values
Work Item 15616 - Option to Use UTC Format for Managed DateTime Fields
Work Item 15978 - Patch to Support DataProviders in External Assemblies
Work Item 15996 - DataProvider.GetSPList() Should Return Stored Procedures in Alphabetical Order

Thanks again to everyone who contributed to this release. Keep the feedback and Work Items coming!

Download SubSonic 2.1 Beta 3

Debug Visualizer for SubSonic Collections

Etienne Tremblay — Thu, 03 Apr 2008 13:16:00 GMT

This article comes from LavaBlast Software Blog

Wouldn't it be nice to be able to see a SubSonic collections while you are debugging, just like the DataSet debug visualizer? Because we often need such a tool to debug our SubSonic collections here at LavaBlast, I've created a small Visual Studio Debug Visualizer that you can use to see what your SubSonic collections contain. How does it work? While you are debugging, put a breakpoint somewhere and simply hover your mouse over a SubSonic collection variable. You should see something similar to the screenshot shown above.

Once the tooltip has appeared, you can click on the small magnifier to open the debug visualizer. You'll see a DataGridView similar to the picture on the right. This will show all your SubSonic objects in a easy to navigate list. Basically my code takes the SubSonic list and transform it in a DataTable. I can't use the method ToDataTable() from the SubSonic AbstractList because this requires access to the provider's configuration, and the JIT executing the debug visualizer doesn't have access to it.

To use this simple tool, download this file SubSonicVisualizer.dll (8.00 kb) and put it in your [My Documents]\Visual Studio 2008\Visualizers\ folder, creating the directory if it doesn't already exist. (You might want to double check your file system permissions on this folder, as well.) I compiled this for Visual Studio 2008. I have also included the source code here (Visual Studio 2008) in case anyone wants to enhance it.

SubSonicVisualizer.zip (386.36 kb)

If you are using Visual Studio 2005, I think this file will work for you, but I have not tested it: SubSonicVisualizer2005.dll (8.00 kb). I simply changed the reference from Microsoft.VisualStudio.DebuggerVisualizers.dll version 9.0 to 8.0 and, from what I have read, it should work in VS.NET 2005.