3

I'm having major issues trying to connect an existing SQLite database to my C# application. I've installed SQLite bundle for 32 and 64 bit, I've installed SQLite/SQL Server Compact Tools extension, installed SQLite dlls into GAC, added various different provider tags suggested by dozens of blog posts to my app.config, to my 32 bit machine config, to my 64 bit machine config. Does anybody know just what exactly does one have to do to make Visual Studio let me hook up my SQLite DB to my application?

I'm trying to add a new ADO.NET Entity Data Model to my application by using the option "EF Designer from database". I'm either getting hit with "Unable to find the requested .Net Framework Data Provider" and Visual Studio cannot even find my DB in the dialog (even though I can see it in the Server Explorer).

Or, after I've installed an additional ADO.NET SQLite as VS extension, I can see my SQLite database and test connection is sucess ful, but a soon as hit the Next button I get this:

enter image description here

I've installed EF only through the System.Data.Sqlite NuGet package, I'm maximally confused about this whole provider business.

Please HELP! Thanks!!!

DanDan
  • 1,038
  • 4
  • 15
  • 28
  • already tried rebuilding with what I have so far, didn't help – DanDan Oct 26 '18 at 16:43
  • I'm guessing but ... Looks like an entity framework issue to me ... see https://en.wikipedia.org/wiki/Entity_Framework ... The version of Entity Provider your are installing is not liked by your project / Visual Studio. You need to get both things sharing a version they like. – Atrix Oct 26 '18 at 17:55
  • 1
    I have guidelines here: https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider – ErikEJ Oct 27 '18 at 07:05
  • thanks so much @ErikEJ for the link, the About dialog confirms that I have SQLite in GAC, but it says "System.Data.SQLite DDEX provider - No". I've fiddled around with the machine config already with no success, I'll redo it exactly according to your guideline and see what happens. If that doesn't help, how do I get the DDEX provider? – DanDan Oct 27 '18 at 09:12
  • 1
    Start by following my instructions – ErikEJ Oct 27 '18 at 13:22
  • Followed the exact steps provided in the link, now it works like a breeze! thanks again @ErikEJ – DanDan Oct 27 '18 at 18:15

1 Answers1

4

Ok, by following a provided helpful link to a detailed guideline I was finally able to get everything up and running. I've done exactly what's described here: https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider

However I'm taking the liberty of providing some additional tips for anybody stumbling over the same issue.

  1. I'm still confused about what machine config to manipulate (VS is 32 bit but I'm developing a 64 bit application), so I manipulated both without trying which one matters in that context.
  2. The duplicate closing tag for the DbProviderFactories in the machine config that's desribed in the mentioned github page as well as in numerous other blogs might not always be the problem and was not the case on my machine. But if you encounter it, clean it up anyway.
  3. If you apply the described changes, double check to make sure that you yourself do not introduce new duplicate tags, otherwise your Visual Studio will start up with an unusual looking start page and crahs, after fixing it all of your pinned editor and tooling windows will be gone and you'll have to re-arrange everything!
  4. Your existing SQLite database (even if visible in the Server Explorer) might still not show up in the Choose Your Data Connection section of the Entity Data Model Wizard after hitting Next.
  5. In that case just select New Connection..., Continue, enter your connection string there into the mask and test the connection before hitting OK. Then your database should be selected and it should work.
  6. In case you don't know the connection string of your SQLite database, but managed to get it into the Server Explorer, you can get it by right clicking and selecting Properties in the Server Explorer, it will show up in the Properties window.

enter image description here

enter image description here

enter image description here

DanDan
  • 1,038
  • 4
  • 15
  • 28
  • 1
    Hi, the `SQLite Provider (Simple for EF6 by ErikEJ)` is not shows up on Entity Data Model Wizard with `New Connection..., Continue`, but I can Add SQLite Connection in `SQLite Compact Toolbox` and `Server Explore`, I am use VS2019 – Moon soon Dec 13 '19 at 08:00
  • @TangMonk I have the same issue but unfortunately no solution. – HendrikKoelbel Aug 24 '20 at 08:02
  • I have just installed everything as instructed (VS2019), and have the same problem. There is no provider listed other than: .NET Framework Data Provider for SQL Server Furthermore, Erik's screenshot on this page: https://marketplace.visualstudio.com/items?itemName=ErikEJ.SQLServerCompactSQLiteToolbox Shows a right-click entry to Add Entity Data Model (EDMX) to project... I don't have that option in v4.8.735 of the Toolbox. This is preventing me from using SQLite. I'm thinking of looking for another database which DOES work... – radders May 30 '22 at 14:03