Knowledge Base

Details:

A – How to install SQLite ADO.NET Provider

1. Download SQLite Precompiled Binaries for .NET 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 “ProviderInstaller – SQLite” directory
Ex:

cd "%USERPROFILE%\Download\ProviderInstaller - 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).

B – Data Source connection string

On PcVue side (Application Explorer), you must declare an “Other providers” data source:

To enter the connection string, you must select the second tab (Other).
You will find below some connection strings examples.

– Connection string without password:

Data Source=C:\mydb.db;Version=3;

– Connection string with password:

Data Source=C:\mydb.db;Version=3;Password=MySecret;

– In-memory database:

Data Source=:memory:;Version=3;New=True;

Found more examples here

C – Data Source Sql queries

On PcVue side (Application Explorer), you can add Sql queries to data connections you created:

You will find below some SQL queries examples.

– 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

D – Troubleshooting

Test connection failed in SqlConnection

"Unable to open database file"

Check database full path is valid.
Check if database file is not corrupted using another utility such as DB Browser for SQLite.
Check svDbConnect service account has full rights on database file.

"Exception has 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 DLL.

Download attachments: ProviderInstaller – SQLite.zip