Installing Sqlite with ado.net on a WPF .NET Framework project, is relatively easy if you know the steps.
Thanks to MagicAndre for providing guidance. Here we go:
1 - Install the toolbox from here. This is straight forward.
2 - Install GAC from here. Now here is where I was failing. I was
installing the 64-bit package only, while the 32 is needed for the GAC. So download and install:
- sqlite-netFx46-setup-bundle-x86-2015-1.0.115.0.exe
- sqlite-netFx46-setup-bundle-x64-2015-1.0.115.0.exe
When installing the 32-bit package, be careful to check as per below image:
3 - If you haven’t created your db, download DB Browser from here. Create your db, place a copy within your project, and include it. You can set the build action to content if you are going to distribute the app later.
4 - Create a solution in visual studio, and nugget install:
System.Data.SQLite: This will auto install the other 4 packages as well as
EntityFramework
5 - In visual studio, go to your server explorer, right click Data connections, add connection
Change your data source to: SQLite Provider (Simple for EF6 by ErikEJ) (SQLite Provider (Simple for EF6 by ErikEJ))
A new window will open
The connectionString line at the top would be in this format:
Data source="C:\Users...\Documents\Visual Studio 2019\Projects\sqlite\sqlite\demo.db";Version=3;
Data source=path;Version=3;
Click ok and the connection will appear in server explorer.
6 - ADO.NET:
Right click solution, add, add new item. Go to Data and select ado.net
EF Designer from database (database first) and your connection should appear:
Click next, select all tables required, and click finish. Your edmx file will then be prepared and opened.
7 - Within your app.config entityFramework section, the following lines should be there:
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
<provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
</providers>
Below should be a system.data section as:
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SQLite.EF6" />
<add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
<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>
Finally, If you are going to use Foreign keys, add:
Foreign Key Constraints=On;
between the path and the version of the connection string, in the app.config file.
I think that's about it to get started. Good luck.
