Using SQLite

One of the updates in MicroLite 2.1 was support for SQLite, however there are a couple of things you need to do if you wish to use SQLite which you don’t for MS SQL. This is due to the fact that the .NET framework supports MS SQL natively, whereas the SQLite implementations of the System.Data classes are provided by SQLite.org.

The easiest way to get started is to install the System.Data.SQLite NuGet package, although you can download and install it from SQLite.org if you don’t/can’t use NuGet.

As usual you will still need to add a named connection string to your app.config:

<connectionStrings>
    <add name="SQLiteTest"
         connectionString="Data Source=C:\MyApp\SQLiteTest.db3;Version=3;" 
         providerName="System.Data.SQLite" />
</connectionStrings>

Notice that the Data Source points to the SQLite database file, see connectionstrings.com for additional information.

You also need to add another section to your app.config so that the .NET framework knows about the System.Data.SQLite provider:

<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SQLite" />
        <add name="SQLite Data Provider" 
             invariant="System.Data.SQLite" 
             description=".Net Framework Data Provider for SQLite" 
             type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.82.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </DbProviderFactories>
</system.data>

Make sure that the version specified matches the version you have installed or you will get an exception thrown by the .NET runtime at startup when it tries to resolve the assembly.

Then finally, in you application startup, you need to call the new overload method for ForConnection so that you can specify the SQLiteDialect:

var sessionFactory = Configure
    .Fluently()
    .ForConnection(connectionName: "SQLiteTest", sqlDialect: "MicroLite.Dialect.SQLiteDialect")
    .CreateSessionFactory();

It is also worth noting that SQLite does not support table schemas so don’t specify one when mapping the table. The schema value on the table attribute was made optional in MicroLite 2.1:

[Table("Customers")]
public class Customer
{
}

This article was written for version 2.1.0 of the MicroLite framework.

Update 1

As of MicroLite 4.0.1, use the ForSQLiteConnection method instead:

var sessionFactory = Configure
    .Fluently()
    .ForSQLiteConnection("SQLiteTest")
    .CreateSessionFactory();

Update 2

As of version 1.0.91.0, System.Data.SQLite now has a dependency on EntityFramework… You should use the System.Data.SQLite.Core package instead.

Advertisements

One thought on “Using SQLite

  1. Pingback: Using SQLite (Updated) | MicroLite ORM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s