C# SQlite Connection String Format – C#

The Problem:

A developer wants to establish a connection with a SQLite database using C#. They have downloaded SQLite for .NET 4.5 and have two versions available: "mixed mode" and "non-mixed mode". They are unsure which version to use and how to specify the SQLite version in the connection string. Additionally, they want to know if the version specified in the connection string should match the version of SQLite they downloaded.

The Solutions:

Solution 1:

To establish a connection with a SQLite database in C#, the following connection string can be used:

sqlite_conn = new SQLiteConnection("Data Source=C:\SQLITEDATABASES\SQLITEDB1.sqlite;Version=3;");

The connection string specifies the following values:

1. "Data Source=C:\SQLITEDATABASES\SQLITEDB1.sqlite": This is the path to the SQLite database file (.sqlite) on your computer. Replace "C:\SQLITEDATABASES\SQLITEDB1.sqlite" with the actual file path to your database.

2. "Version=3;": This indicates that Version 3 of the SQLite database engine is being used. The SQLite DLL version you obtained from the "SQLite Website" may be different, but it will still work with SQLite version 3.

In this connection string, I have provided a specific path to the SQLite database file, "SQLITEDB1.sqlite", and specified that we are using SQLite version 3. You can modify these values to match your database’s location and version.

Solution 2: Sqlite Connection String Format

There are various types of Sqlite Connection Strings. Below are a few examples:

Basic:

`Data Source=c:\mydb.db;Version=3;`

Version 2 is not supported by this class library.

In-Memory Database:

An SQLite database is normally stored on disk, but it can also reside in memory.

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

Using UTF16:

`Data Source=c:\mydb.db;Version=3;UseUTF16Encoding=True;`

With a password:

`Data Source=c:\mydb.db;Version=3;Password=myPassword;`

Solution 3: System Data SQLite Versioning and Mixed/Non-Mixed Mode

SQLite is a C-based database, while System.Data.SQLite is a .NET wrapper around SQLite that enables .NET programs to interact with the native SQLite code.

Mixed-Mode Assemblies:

• Mixed-mode assemblies contain both native code (such as the C SQLite code) and .NET code (the wrapper functions) in a single file.
• Using mixed-mode assemblies is convenient for development or personal use on your own machine.
• However, mixed-mode assemblies have limitations when distributing applications to customers, especially in the context of 64-bit Windows.

Non-Mixed Mode (Recommended for Distribution):

• For distributing SQLite-based applications, it is strongly recommended to use the non-mixed mode approach.

• This means distributing:

  1. The all .NET .dll System.Data.SQLite.dll (which works on both 32-bit and 64-bit architectures)
  2. A 32-bit .dll x86\SQLite.Interop.dll
  3. A 64-bit .dll x64\SQLite.Interop.dll

• The all .NET wrapper (System.Data.SQLite.dll) determines the architecture at runtime and selects the appropriate 32-bit or 64-bit .dll accordingly.

Version Discrepancy:

• The version difference you’ve noticed is due to the distinction between SQLite and System.Data.SQLite.
• SQLite is the underlying C-based database, while System.Data.SQLite is the .NET wrapper.
• The version you cited (System.Data.SQLite 1.0.84.0 (3.7.15.2)) refers to the version of the System.Data.SQLite wrapper, not the version of SQLite itself.

Recommendation:

• Follow the distribution recommendations outlined above for non-mixed mode.
• Use the version of System.Data.SQLite that corresponds to your specific requirements and compatibility needs.