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!
- 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.
- 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.
- Add some the references to your project.
- 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.
- You’re done! Make your own POCOs and use commands like SubsonicRepositoryFactory.GetRepository().Add(myObject);
- 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.
