System.Data.SQLite and SubSonic 3: Works great in VS2008, but not in VS2010

Question

Last year, I used SubSonic 3 and SQLite in a VS2008 project very successfully and was quite pleased with the results. Just recently, I tried to setup SubSonic 3 and SQLite in a VS2010 project and have been met with the inner exception when trying to instantiate a new SimpleRepository:

The type initializer for 'System.Data.SQLite.SQLiteFactory' threw an exception

Just to make sure I wasn't going crazy, I tried the exact same code and app.config file in VS2008 and no problem. Weird!

Currently, I'm using SubSonic 3.0.0.4 and the x64 version of System.Data.SQLite 1.0.73.0 (3.7.6.3) (though I tried the 32 bit version as well.) I added both DLLs as a Reference and set "Copy Local" to TRUE.

My App.Config looks like:

  <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" />
    </DbProviderFactories>
  </system.data>

  <connectionStrings>
    <add name="myDatabase" connectionString="Data Source=C:\DB\mydatabase.db3" providerName="System.Data.SQLite"/>
  </connectionStrings>

And my code looks like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SubSonic.Repository;

namespace SubSonicSqliteTestConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            var repo = new SimpleRepository("myDatabase", SimpleRepositoryOptions.RunMigrations);
        }
    }
}

Any ideas?

Answer 1

I am using SubSonic 3 with Visual Studio 2010 and SQLite 1.0.66 and it works. However there are a few things you have to do:

• Your application has to be x86 (not AnyCPU) or you get a BadImageFormatException on 64 bit machines
• You have to change the Target Framework from "Framework 4.0 Client Profile" to "Framework 4.0"

• You have to add (or modify) this to your app.config file. SQLite won't work without the useLegacyV2RuntimeActivationPolicy flag set to true

  <startup useLegacyV2RuntimeActivationPolicy="true">
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
  

• My Factory initialisation code looks like this (it includes the Version and the PublicKeyToken). I got this settings from the machine.config file on my dev machine where I ran the setup and have choosen to integrate SQLite to Visual Studio 2010 (from the All Programs\SQLite folder). The file is located @ %WinDir%\Microsoft.NET\Framework\<FrameworkVersion>\CONFIG

  <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.66.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
  
      </DbProviderFactories>
  </system.data>
  

• My connection strings contains slashes instead of backslashes in the path and contains a version

  <connectionStrings>
      <add name="connectionstringname"
                 connectionString="Data Source=c:/temp/mydatabase.db;Version=3;"
                 providerName="System.Data.SQLite"/>
  </connectionStrings>
  

Hope that helps.

Update: I see you use a x64 version of SQLite, so forget about the first hint. But I leave it, maybe it is helpful for others.