Cross posted from mojoPortal.com

In the week prior to my vacation I had started working with SubSonic to see if it could be useful in mojoPortal since it has support for a number of databases, I figured it could reduce the work of supporting all these different dbs.

SubSonic is basically a toolkit for interogating databases for their schema and has the ability to generate db specific sql statements for common tasks. It also has a code generation feature so it can generate .NET classes based on database tables. It can be used as an OR mapper, that is, you could use the generated classes as your business objects, you could either inherit from them or use partial classes to bolt on custom functionality on top of what was generated by SubSonic. But for people like me who just never bought into the whole OR mapper thing, the generated classes can just be thought of as data objects that abstract the database table. It can return standard data in the form of IDataReader which is what the mojoPortal business objects like to consume and allows using SubSonic without having any particular dependency on SubSonic in my business classes.

It comes with providers for MS SQL, Oracle, MySql and SQLite and there were some other partially complete providers that I found in the wild. So my plan was, for proof of concept, to take a simple feature like the links module and try to re-implement it for all the dbs by using SubSonic. The short story is, yes I got it working for all 5 of the currently supported databases in mojoPortal. I re-implemented a new SQLite provider using Mono.Data.Sqlite which I already knew works both on Windows and on Linux/Mono. I found a postgresql provider here (thanks to Justin Greene and Maurício Machado) that was usable with a little bit of work. I also found a starter implementation for Firebird Sql (thanks to  Ricardo García) that I was able to complete and get working.

I've since gone on to implement the data layer for the WebStore feature for MySql, mostly using SubSonic. And the things that did use SubSonic are completely re-usable for the other data layers so the bulk of the work is already done for implementing the web store for postgre sql and Firebird.

SubSonic also comes with a thing called the scaffold page which is basically a web page that allows managing data in all of the tables in your database. Just drop it in and it works. I've modified the one in mojoPortal a little for various little issues I encountered and also to add security checks to control access to the page.

Since the code templates used by SubSonic are basically .aspx pages, I got the idea of making a browser based gui for code generation. I implemented that today and got the initial proof of concept done in about 4 hours of playing around. So at the moment its kind of like a poor man's Codesmith in the browser.

At any rate, I made a quick tutorial video about use of SubSonic in mojoPortal, sort of a developers introduction to it. Its pretty cool stuff so I hope you'll have a look.

An Intoduction to SubSonic as Used in mojoPortal