Too Easy: Instant Data Access Layer with SubSonic andSQLite

It’s frightening just how much pain I have in starting a new application project. Creating an SQL database, deploying a schema, building an ORM access layer, and putting all the pieces into source control to deploy, version, update that database, both on the development SQL Express, and the production SQL Server instances—it takes time. It takes work. The only thing worse than putting in all that effort is doing the cowboy bit with the tools and NOT doing it. Then you have various unsynchronized, untestable, messy databases and deployed versions of code. Oook!

SubSonic 3.0 introduced the SimpleRepository and Auto-Migrations. These Ruby-on-Rails-inspired features, when coupled with SQLite can make your DAL-creation tasks just disappear. Let’s give it a try!

  1. Download and install System.Data.SQLite. This distribution of SQLite is a single mixed-mode dll that contains a recent SQLite build as well as ADO.NET bindings for the database. Also in the package is a linq provider, and Visual Studio Server Explorer support.
  2. Download SubSonic 3. There’s all sorts of great stuff in this download. Three different data access models. Modifiable templates. Examples. But you only need the single dll from the binaries folder. Toss the rest in a drawer and look at it later.
  3. Add some the references to your project.
  4. Create a RepositoryFactory class:
    public class SubsonicRepositoryFactory{    public static SimpleRepository GetRepository()    {        var provider = SubSonic.DataProviders.ProviderFactory.GetProvider("Data Source=|DataDirectory|my.db", "System.Data.SQLite");        var repository = new SimpleRepository(provider, SimpleRepositoryOptions.RunMigrations);        return repository;    }}

    This isn’t strictly necessary…but I hate putting my connection strings in all those angle brackets, and prefer to detect my production/test/development environments in code rather than in XML.

  5. You’re done! Make your own POCOs and use commands like SubsonicRepositoryFactory.GetRepository().Add(myObject);
  6. Go home. You’ve worked hard. Relax.

This is good stuff, and it certainly can take some of the pressure off of starting up a new project. With data access and persistence just ‘done’, you can get to logic and value quicker. And you always have the option of upgrading to a more ‘serious’ data layer if you need it later. I’m really curious if SubSonic/SQLite can provide a reasonably performant small-to-midsize backing store to a web service application server. If your applications and various UIs all access their data through the web services, who cares what the persistence storage is, and until that back end is doing enough work to require multiple servers, how much trouble can you get into by caching your objects in memory and persisting them to disk using SQLite?

It’s also worth noting that SimpleRepository doesn’t have any wiring for child collections yet. Rob has said on StackOverflow that he intends to do some work on that, but it’s not in there yet. However, it shouldn’t be too hard to drop some convenience methods on your POCOs (extension methods if you’re worried about polluting your classes) to emit Lists or IEnumerables of linked collections.

Don’t plug your Clear Wireless Modem into yourMacintosh

Update: The most recent versions of the drivers may have solved these problems. Works for me now.

WARNING! If you plug your Clear Wireless modem into a Macintosh computer with Clear’s driver installed, the modem will be flashed with a firmware that makes it incompatible with Windows. This firmware flash is not reversible. You will need to return your modem to Clear to get a new Windows-compatible one. More details below.

I got connected with Clear Wireless in Portland 4 days ago. So far I’m very happy with the bandwidth. Connected to my laptop, I’m getting 3mbs down and 200kbps up speeds. For mobile, unlimited internet, this is fantastic. Even better, I’m getting 100-160ms packet latencies, which is about one-third of what I was getting on my cellular wireless modem.

I also purchased the Clear Spot WiFi/WWAN router. This little device is really cool, letting me share my WIMAX connection with other computers, my iPhone, or whatever. Even better, it lets me use the WIMAX modem without installing drivers. This is great because the drivers for WWAN modems are terrible….and Clear doesn’t have average quality drivers.

Windows 7 Drivers

Ok. So there aren’t Windows 7 drivers. Yet. The folks at Clear have told me I can expect to see them on Thursday, when the official launch date happens. In the meantime, install the connection manager from your CD or Driver Thumb Drive(in the box), and do a little googling for instructions on installing the older version of the drivers. Scott Hanselman wrote up something on this. You can also download the 64-bit driver from here for a couple months.

Also, be sure not to unplug the Modem before using the ‘Safely Eject’ feature of Windows….I get a BSOD every time.

The Win7 drivers work. They aren’t shiny or easy to install, but they work. That’s much better than the…

Mac OS X Drivers

When Clear says they support Mac OS X, they are…well…reaching. Their driver is marked ‘Beta’ and it shows. After the install, the Connection Manager will spend 5 minutes or so just sitting there. No status message, and a red light on the modem. Is it working? Is it doing something? Who knows. After that, it will connect, and you’re set to go. Same good service.


If you are running Snow Leopard (Mac OS X 10.6) you get nothing. No error message. No indication of failure. No connection. It just doesn’t work. Saying Windows 7 isn’t released yet is technically true, and I understand why those drivers aren’t out. OS X 10.6 has been out since August 28th. It seems foolish that an early-adopter service like Clear doesn’t support an early-adopter OS. That’s how it is, though. Clear people have told me unofficially that we should see a 10.6 driver ‘by the end of the month’. We’ll see.

You can’t go back again

The reason for the 5 minute wait when you install your Mac drivers is apparently that the drivers are installing a fresh firmware on your modem. As I stated at the top, this firmware isn’t compatible with the Windows drivers. Nor does Clear have a flash utility available that can downgrade it. You have to return your modem for a replacement. Clear has told me that this should be free, since it’s “Their Fault”. Be nice when you call, and they should hook you up.

WiFi to the rescue

Luckily, the Clear Spot can still connect to the modem with it’s new, corrupted, Mac OS (10.5 or earlier)-only firmware. I really like the driver-less mobile internet experience. No need for the hassle of incomplete, incompatible drivers or connection managers. Just Windows 7’s shiny new WiFi connection tool and fast internets.

The Clear Spot (Cradlepoint PHS-300) is a great router, works with non-Clear broadband modems, and is $50 cheaper when you get it from Clear. The battery seems to last a bit more than 3 hours with the network in use. You can get extra batteries and a car charger from Clear, but I think an external Tekkeon battery is probably a better idea. When you get it from 3Gstore, it comes with a USB cable that I’m hoping will let me charge my Clear Spot from my laptop. One less wall wart in my kit bag.

There is a slight glitch. Most of the pictures you’ll see of the Clear Spot will show it with the modem plugged right into it. It turns out this is a bad idea. WiFi and WiMax radios seem to interfere. Make sure you leave the router on Channel 1. I also didn’t get anything approaching full speeds until I used a USB extension cable to put some space between the two devices. Once I did that, speeds were right up there.

Good support experience

Clear’s service is definitely in early-adopter mode, but I had a really excellent experience both with the store staff, and with the folks at technical support. It took me 20 minutes on the phone to figure out my Mac/PC Firmware problem, but the rep was patient and eager to help, and that is an excellent experience these days.

I like my Clear service and I recommend it if you want some better mobile bandwidth.