SubSonic 2.0: Kick the tires on Beta 1!

Summary

SubSonic 2.0 (the zero code data access layer) just hit Beta 1 today. Download it, marvel at all the cool new features, and get your feedback in quickly because they're planning a really short beta phase with a final release as early as next Wednesday.

Background

I got fired up about SubSonic when I first saw it last August and wrote that Microsoft should ship it with ASP.NET AJAX (then called ATLAS). I knew it wouldn't happen, but I still think I was right. SubSonic is a zero code data access layer for ASP.NET - drop the DLL in your bin directory and add a few lines to your web.config, and you get immediate access to classes and controls that make ASP.NET work as if it was built just for you and your database.

Part of what got me excited was that my first feature request was implemented and released less than two hours after I left it in a comment on Rob's weblog. They haven't slowed down a bit.

What's New 

Here's what's new in the 2.0 release (lifted from Rob's release announcement):

  • Multiple Database support! Now you can generate a full DAL for as many databases as you like.
  • Enterprise Library 3.0 Support. Just added this in and it works nicely.
  • All-new command-line tool. You can now use our command-line tool (called SubCommander) to do all kinds of fun things, like generate your code and version your database. You can even hook into it using Visual Studio's External Tools - this will allow you to generate your project's code with the click of a button, and it will use your project settings (look for a blog post on Rob's blog).
  • Improved querying. You can now use our Query tool to run OR's, IN's, and aggregates. You can even type what you want to see:
    IDataReader rdr = new SubSonic.Query("Products").WHERE("CategoryID = 5").ExecuteReader();
    We've also renamed many of our methods (well, we've added aliases) to make the query more readable. You can now use WHERE, AND, OR, IN, BETWEEN_AND, etc. to make your calls that much more readable.
  • New Controls. You can now use our Smart Dropdown, which loads itself:
    <subsonic:DropDown id=mySmarty runat=server tablename="categories" />
    You can also use our new ManyToMany Checkbox list helper to both list and save information for many to many relationships:
    <subsonic: ManyManyList id=myList runat=server MapTableName="Product_Category_Map" PrimaryTableName="Products" PrimaryKeyValue="1" ForeignTableName="Categories" />
  • A new AutoScaffold page that you can drop right into your project to admin all your tables. This thing reads your tables and creates scaffolds for you automagically (thanks Eric!).
  • A completely reworked code-generation system that uses an ASP-style templating system. You can now code up your templates like you would an ASP page (also just like CodeSmith). Test them in your web site to make sure they work, then replace (or add to) the bits that get generated at runtime. You can override our templates by specifying a template directory in the web.config:
    <SubSonicService defaultProvider="Northwind" fixPluralClassNames="false" templateDirectory="D:\PathToMyTemplates">
  • Regular Expression Naming Engine. If you don't like what our convention is, then you can use your own with some simple regex. Pass a simple string, or a dictionary replacement and all of your tables/views will be renamed as you specify.
  • Query Inspection. Want to know what's happening to your query, and how long it's taking? You can simply use the new Inspect() method which outputs the results and statistics to HTML for you to review.
  • Improved Trace/Debug. We've added tracing to (almost) every facet of SubSonic, so if you turn tracing on you can see what SubSonic's trying to do. We're always adding to this and if you see something we've missed, let us know :).
  • Improved Scaffolding. Your foreign key values are now pulled into the GridView and displayed instead of their keys. Eric figured this one out - go dude!

My favorites are probably the command-line tool, scaffolding improvements, the auto-scaffold, and the code generation system. Here's why:

  • The command-line tool (sonic.exe) lets you work with SubSonic however you'd like. Previous releases of SubSonic required you to either use a build provider (which only works in ASP.NET 2.0 Web Site Projects, not Web Application Projects), or through a web page which would generate your DAL code. sonic.exe removes that restriction and gives you full and complete access to just about anything SubSonic can do. Since SubSonic's data engine is pretty much SQL Server Management Object (SMO) on steroids + support for other databases like MySQL and Oracle, command line access lets you do things with your data that make a lot of sense even if you never touch ASP.NET. sonic.exe can script out your database (schema and / or data) and build you a complete data access layer based on default templates or templates you provide, which is pretty nice for any .NET data application. Phil and I contributed some code to this, and we're using it in a current application for a large client. It works very well.
  • Scaffolding improvements are a big deal. The original scaffold control was nice for developer admin interfaces, but there were a few problems that kept me from using it in live applications. One problem was that the scaffold showed ID values for foreign key columns. In the new release, the scaffold follows the columns to show the values from the foreign keyed tables, which is really slick. Even nicer is that you can output the code for a scaffold using sonic.exe, which means you can do whatever you can customize it as much as you'd like.
  • The AutoScaffold is a great timesaver. It gives you a complete admin interface to your entire database - view, add, edit and delete for all tables - with zero code. Note that the editor controls are automatically generated - calendar controls for date values, dropdowns for foreign keyed values, etc.

    AutoScaffold
  • The changes to the code generation system to use ASP.NET style templates, which will make it easier to fit this in with existing projects or standards. Even better, this will open the door for community development and improvement of templates, and that can't be bad.

It's ironic that I need to wrap this up to get back to writing a tip for the ASP.NET book I'm co-authoring (with Phil Haack, Jeff Atwood, and K. Scott Allen). This tip, scheduled and assigned to me months ago, is on SubSonic. I had a chat with Rob about some upcoming features to include in the book, and I can only say that they're even better than this 2.0 stuff.

11 Comments

  • sweet! I can't wait to check it out.

  • can i host the web sites created by SubSonic on shared hosting use Medium trust .. ?

  • Yes Mohamed, you can create your classes from SubSonic or just compile the whole site before uploading it.

  • No support for multi-column primary keys? That pretty much just rendered it completely useless for anyone that actually uses primary keys to (shock horror) enforce some kind of uniqueness on data

  • Simon - I think you'll find that pretty common in any object / relational mapper.

    There are a lot of different approaches to database design, but I don't think multi-column primary keys are the best way to enforce data uniqueness. A better approach is a surrogate key identifiers paired with constraints to ensure uniqueness.

  • Would I use SubSonic instead of the datadesigner in Visual Studio?

  • Cheers for the reply Jon, i've read a lot of discussion on the pros/cons of using surrogate keys paired with constraints, but i've yet to find a truly irrefutable reason to use them.

    I generally try to avoid using a surrogate key on data where a natural candidate key exists - it adds another 4 bytes to each row (maybe insignificant, but it's not uncommon for tables to have rows numbering in the tens of millions), not to mention the loss in efficiency of adding an additional constraint to the table to ensure uniqueness.

    Doesn't DLINQ support multiple column primary keys?

  • Why should I use sub-sonic when Linq will be offered by MS soon?!!

  • @Ranji: Why would you want to use Subsonic over Linq?

    Well, you may want to ship an application that only requires the .NET 2.0 runtimes and not the .NET 3.5 ones for a start.

    The Linq to SQL stuff also offers nothing in terms of UI generation/scaffolding.

    [)amien

  • Reply to Ranji,

    Linq is only a query tool on top of that, there are no utilities to persist the data. Subsonic is twice the product in a 10th of the time.

  • I need a query,it can getsql() like:

    SELECT A.Message,B.UserName FROM SYSShortMessage A INNER JOIN SYSUser B ON A.RecieveID=B.ID

    I can use the ExecuteJoinedDataSet() method??

Comments have been disabled for this content.