more quick actions
minimize quick actions
Topic starter
https://www.sqlite.org/index.html
SQLite is probably the most used database engine in the world but SQLite editor no more share installer (.msi).
However it is possible to install a third party provider with the tool : "ProviderInstaller.exe".
To be detected by PcVue, each ADO.NET provider must be installed in the Global Assembly Cache (GAC) of the .NET framework and referenced in the machine.config file. The tool "ProviderInstaller.exe" is there to facilitate this task.
-- How to install SQLite ADO.NET Provider --
1. Download SQLite Precompiled Binaries for .NET http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
Select following version of binaries: Precompiled Statically-Linked Binariesfor 32-bit Windows (.NET Framework 4.6)
Exemple: sqlite-netFx46-static-binary-Win32-2015-1.0.116.0.zip
2. Extract archive and identify following files:
- System.Data.SQLite.dll
- SQLite.Interop.dll
3. Download Attachment "ProviderInstaller - SQLite.zip"
4. Extract "ProviderInstaller - SQLite.zip" to "ProviderInstaller - SQLite"
5. Replace following files in folder "ProviderInstaller - SQLite" by the version downloaded at step 1.
- System.Data.SQLite.dll
- SQLite.Interop.dll
6. Open CMD prompt as Administrator
7. Change directory to be in "ProviderInstaller - SQLite" directory
Ex:
cd "C:Usersarnaud.degreDownloadProviderInstaller - SQLite"
8. Run following command:
ProviderInstaller.exe /install
The installation is complete !!
The provider is now visible in PcVue SQL Connection (Data source : Other providers).
-- Connection string --
. Connection string without password :
Data Source=C:UsersardeDesktopmydb.db;Version=3;
. Connection string with password :
Data Source=C:UsersardeDesktopmydb.db;Version=3;Password=MySecret;
. In-memory database
Data Source=:memory:;Version=3;New=True;
Found more samples here
-- Sql queries --
. Create a table
CREATE TABLE [MyTable] (col1 INTEGER NOT NULL)
. Insert a value
INSERT INTO [MyTable] (col1) VALUES (1)
. Display table content
SELECT * FROM [MyTable]
Found more samples here
-- Troubleshooting --
Test connection failed in SqlConnection
"Unable to open database file"
Check database path, extension or database file is not corrupted.
Check svDbConnect service account has full rights on database file.
"Exception hos been thrown by the target of an invocation."
Check you deploy x86 DLL (System.Data.SQLite.dll / SQLite.Interop.dll) in GAC folder and not x64.
Posted : 21/11/2022 11:28 pm
