sqliteentity-frameworkado.netvisual-studio-2019system.data.sqlite

Visual Studio 2019 - WPF .Net Framework 4.8 - Adding SQLite with ADO.NET

I am trying to add SQLite with ADO.Net, and I am really struggling with it.

This is the packages I have installed:

What am I missing? How do we use sqlite with ado.net in vs2019?

Solution

  • 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:

    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;

    enter image description here

    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:

    enter image description here

    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.