Using Linq-to-Sql with Sqlite
I was recently working on a little example project (to illustrate using OpenID) and I realized that I needed a datastore. I didn’t really want to go through the hassle of creating a database in Sql Server, so I thought I would try and use Linq-to-Sql with Sqlite as the provider.
Sqlite is a really cool provider for example projects (it ships with Rails as the default provider) and it’s reasonably easy to setup provided you sidestep a few issues.
Download Sqlite
Go to the download page for Sqlite and grab the command-line shell and the dll for the library. As the name suggests, the command-line shell allows you to create databases and issue sql statements. It also has a few other features such as allowing you to view the schemas of the tables you’re working with.
Download DbLinq
Head over to the project wiki for DbLinq – this is an open-source Linq-to-Sql provider for a number of databases, including Oracle, PostgreSQL, MySQL and Sqlite.
The library download includes the dlls for all the different databases and also includes DbMetal – a command line tool that allows you to generate the datacontext files, similar to the GUI Linq-to-Sql provides for Sql Server databases.
For some bizarre reason the library download does not include the ADO.Net provider (System.Data.SQLite.DLL) which is required by DbMetal. I needed to grab the source download and copy this dll into the same folder as the DbMetal.exe file to get it to work.
Create a Sqlite Database
To create your Sqlite database simply invoke the Sqlite.exe file with the name of the database file to use. The convention seems to be to use lowercase for the filename, so something like
sqlite.exe yourproject.dbshould do the trick. If you’re used to using Sql Server Management Studio it feels a little clumsy to write all the create statements by hand, but if you head over to the getting started page you should find enough examples to get you going.
Also make sure you take note of the way Sqlite handles auto-increment fields. Also keep in mind that you need to specify your primary key as INTEGER primary key, not INT primary key if you want the primary key to be an alias for the row id.
Use DbMetal to generate your Datacontext
Now you simply need to invoke the DbMetal.exe file to generate your datacontext.
DBMetal.exe /namespace:Namespace /provider:SQLite "/conn:Data Source=database.db" /code:CodeFile.csDbMetal generates a class called Main. I prefer that my generated class be called DataSource, so I simply renamed it. (It doesn’t seem like you can override this default)
Get it running
To get the generated code compiling you’ll need to add a reference to the System.Data.Linq library. (Keep in mind that this is a .Net 4 library)
Now when using your datacontext you can’t simply use a connection string, you need to add a reference to the System.Data.SQLite dll and then create an instance of the SQLiteConnectionString class. If you don’t do this the code seems to assume you’re trying to connect to a Sql Server database.
private readonly DataSource dataSource = new DataSource(new SQLiteConnection(@"Data Source=database.db;DbLinqProvider=sqlite;"));And that’s it! Now you should be able to write Linq queries against your Sqlite database just like you would with a Sql Server database. Happy coding.