Obsolete
Go to the new SQLite on Visual Studio with NuGet post instead.
Be sure to read upgrade instructions I've just posted as a follow-up to this post.
I’m just starting to play with SQLite and the System.Data.SQLite library created by Robert Simpson and taken over by the SQLite.org folks. In attempting to get things working in Visual Studio 2010, I ran into a few issues, so this post is as much a reminder for myself as it is a help for other .NET developers who wish to use SQLite from within Visual Studio 2010.
The current binary installers from sqllite.org for the System.Data.SQLite .NET assembly do not include the Visual Studio designers or the ADO.NET driver required for use of SQLite in the Server Explorer. So here’s the winning combination I’ve found for getting my environment set up properly.
- Download and install version 1.0.66.0 from sourceforge.net because current binary installs on SQLite.org at system.data.sqlite.org do not include the Visual Studio designer or ADO.NET driver install.
- Download and install the latest versions (x86 and x64) from system.data.sqlite.org (currently 1.0.73.0 which contains the SQLite 3.7.6.3 engine).
Important Note: If you have Visual Studio 2008 and 2010, be sure to choose both when prompted as I found reports from others who had problems otherwise.
- Copy the x86 binaries into and overwriting file at C:\Program Files (x86)\SQLite.NET\bin (assuming you used the default install location for version 1.0.66.0 and you're running x64 Windows). And if you are on an x64 box, copy the x64 binaries to the C:\Program Files (x86)\SQLite.NET\bin\x64 directory, overwriting existing files.
Now you can open Visual Studio 2010 and navigate to the Server Explorer, right-click the Data Connections node, choose Add Connection and click the Change button for Data source. You can then select the SQLite Database file source like this:
Click the new button and navigate to your desired directory and supply a file name and you’ll end up with something like this:
With a new data connection, you can use the table designer, but it has limitations and it’s probably not the best approach with an embedded database engine from a development perspective anyway, since generally you’re going to want your app to be able to create the database from script embedded in code.
So now all that’s left to get started writing code against your database is to add your references like this:
And since Entity Framework supports SQLite, you can add your ADO.NET Entity Data Model to your project and it will produce a nice connection string like this for you (VB-like line _ breaks added):
<connectionStrings>
<add name="testEntities"
connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl| _
res://*/Model1.msl;provider=System.Data.SQLite; _
provider connection string="data source=C:\Code\Projects\test.db""
providerName="System.Data.EntityClient" />
</connectionStrings>
or a non EF 4 connection string of
<connectionStrings>
<add name="mytest"
connectionString="data source=C:\Code\Projects\test.db;"
providerName="System.Data.SQLite" />
</connectionStrings>
Now remember, model first is not currently supported, so you need to create your data and then update your model from data. You also need to make sure that you add the following to your config file or you’ll get a nasty runtime error:
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" />
</startup>
UPDATE (30 minutes later) Just a bit more testing reveals that I missed one step. Very important to know and use.
First, I reinstalled the x86 and then x64 latest installs and checked the checkboxes to install to the GAC and modify the path. Then I ran “test” from the command line and after modifying the connection string by giving the file a path like C:\temp\test.db, the tests ran fine. Looking at the chm help file and the test.exe.config revealed the trick I needed.
The 1.0.66.0 install adds a factory reference to your .NET machine.config file like this:
</DbProviderFactories>
...
<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>
While this entry makes the Data Connection and designer possible, it will cause you problems when trying to run using the latest 1.0.73.0 referenced assemblies until you add this to your own config file:
<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>
Next step: experiment with updating my machine.config in its many incarnations to point to the latest and greatest x64 version 1.0.73.0 with token db937bc2d44ff139 like this:
</DbProviderFactories>
...
<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.73.0,
Culture=neutral,
PublicKeyToken=db937bc2d44ff139" />
</DbProviderFactories>
Note that this will move the machine.config reference to the MSIL (Any CPU) version rather than the x86 version. For my machine, this means making changes in (and remember to open as Administrator so you can save the file):
C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG\machine.config
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config
Rebooting, just to be paranoid.
And now, run the same test after commenting out the local app.config’s <system.data><DbProviderFactories> node and everything works as expected. No more bad image exception. And the Data Connection designer continues to operate as expected. Now on to some real coding.