.NET SQLite Connection Strings

SQLite connection strings provide the parameters necessary to establish successful connection to SQLite databases, ensuring proper communication between the application and the database engine.

This guide will walk you through the key components of SQLite connection strings, their construction, and best usage practices.

SQLite Connection Strings

This setup is particularly useful for applications that need to dynamically create or connect to existing SQLite databases without manual intervention. The following code snippet illustrates this process:

using Devart.Data.SQLite;
...
SQLiteConnection conn = new SQLiteConnection();
conn.ConnectionString = @"DataSource=database.db;FailIfMissing=False;";

Connection Properties

The following table lists the valid names for values within the SQLite connection strings.

Name	Description
Attach	The list of databases to attach, separated with semicolons.
Automatic Index	Determines whether SQLite automatic indexing is enabled. The default value is true.
Auto Vacuum	Determines what happens when a transaction that deletes data from a database is committed. See AutoVacuumMode for information on the supported values.
Binary GUID	Determines how GUIDs are stored. If true, GUID columns are stored in binary form, otherwise GUID columns are stored as text.
Busy Timeout	Sets an SQLite busy handler that sleeps for a specified amount of time when a table is locked. The handler will sleep multiple times until at least the specified number of milliseconds of sleeping have accumulated. After this, the handler returns 0. Specifying this parameter will cause a call to sqlite3_busy_timeout function when connecting.
Cache Size	The maximum number of database disk pages. Each page uses about 1.5 kilobytes of memory. The default cache size is 2000. If you are doing UPDATEs or DELETEs that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement.
Cache Spill	Determines whether to allow spilling dirty cache pages to the database file in the middle of a transaction. The default value is true.
Case Sensitive Like	Determines whether the LIKE operator performs the case-sensitive comparison. By default, the value is false.
Cell Size Check	Determines whether to perform additional checks on database b-tree pages as they are initially read from disk. The default value is false.
Checkpoint Full FSync	Determines whether the F_FULLFSYNC syncing method is used during checkpoint operations on systems that support F_FULLFSYNC. The default value is false.
Count Changes	Determines whether the database engine should return the number of inserted, updated, or deleted rows.
Data Source	The path and name of the database to which to connect. This parameter supports both file system paths and URI format.
DateTime Format	The format of DATETIME values. If set to "Ticks", DATETIME values are expressed in ticks. Otherwise, Datetime fields are formatted according to ISO8601.
Enable Load Extension	Determines whether an SQLite extension library can be loaded from the from the named file with the statement SELECT load_extension(file_name);. It can be useful, for example, for the full-text search modules.
Enlist	Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true. SQLite engine itself doesn't support distributed transactions. Our provider emulates this support to allow using the TransactionScope class. This parameter is not supported in the .NET Standard 1.3 compatible assembly.
FailIfMissing	Determines what to do when the database file is missing. If true, SQLiteConnection throws an exception if it cannot find the database file. If false, an empty database is created.
Foreign Key Constraints	Determines whether the foreign key constraints are enforced. See SQLiteForeignKeyConstraints for the information about the supported values.
Full Column Names	Determines the format of autogenerated names. If true, the database engine names columns according to format <table-name/alias> <column-name>.
Full FSync	Determines whether or not the F_FULLFSYNC syncing method is used on systems that support it.
Ignore Check Constraints	Determines whether the check constraints are enforced. The default value is false.
Initialization Command	Specifies a database-specific command that should be executed immediately after establishing the connection.
Journal Mode	Determines SQLite journal mode for the connection. For the list of the supported values see JournalMode.
Journal Size Limit	The maximum size of the log file in bytes. If the journal file exceeds this size after commit, it is truncated. Negative values mean no limit. The default value is -1.
Legacy File Format	Determines whether backward compatibility of the database file is enabled.
Load Extension	Defines the list of SQLite extension libraries, which are loaded when the connection opens. Applied only when the Enable Load Extension connection string parameter is set to true.
Locking	Determines database locking mode. For the list of the supported values see LockingMode.
Max Page Count	The maximum number of pages in the database file.
Page Size	The page size in newly created databases. Must be a power of two greater than or equal to 512 and less than or equal to 8192.
Read Only Database	Determines whether to open a database in a read-only mode. An exception is thrown if a database does not exist. Available only if the connection uses the UTF8 encoding.
Read Uncommitted	Gets or sets the process isolation level. The default level is SERIALIZABLE (false).
Recursive Triggers	Determines whether the recursive triggers are turned on. The default value is false.
Reverse Unordered Selects	Determines whether the result of the SELECT statement without the ORDER BY clause will be retrieved in the reverse order of what it normally would. The default value is false.
Secure Delete	Determines whether to overwrite the deleted data with zeroes.
Short Column Names	Determines the format of autogenerated names.
Synchronous	Determines the synchronization mode of write operations. See SynchronizationMode for the list of supported values.
Temp Store	Determines the location of temporary files - user's TEMP folder, custom folder, or RAM. See TempStoreMode for the list of supported values.
Temp Store Directory	The directory to store temporary files in if Temp Store is set to File.
Threads	The maximal number of auxiliary threads the prepared statement can launch to assist with a query. The default value is 0, which means no auxiliary threads are allowed.
Transaction Scope Local	If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within the scope of TransactionScope, our provider will use only one connection internally. The default value is false. This parameter is also not supported in the .NET Standard 1.3 compatible assembly.
UTF16	Determines whether the connection uses UTF16 encoding.
WAL Auto Checkpoint	The write-ahead log auto-checkpoint interval - the number of the write-ahead log pages, after which the checkpoint is performed.
Writable Schema	Determines whether the SQLITE_MASTER tables can be changed using UPDATE, INSERT, and DELETE statements. However, note that editing the SQLITE_MASTER table in such a way can result in a corrupt database file.

SQLiteConnectionStringBuilder

In this example, we explore how to use the SQLiteConnectionStringBuilder class from the Devart.Data.SQLite library to construct a connection string for an SQLite database.

using Devart.Data.SQLite;
...
SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder();
conn.DataSource = @"database.db";
conn.FailIfMissing = false;
conn.Locking = LockingMode.Exclusive;
conn.AutoVacuum = AutoVacuumMode.Full;
conn.ConnectionTimeout = 20;
SQLiteConnection sqLiteConnection = new SQLiteConnection(conn.ConnectionString);

SQLite Encrypted Connection Strings

Our library supports a variety of built-in encryption methods, including TripleDES, Blowfish, AES128, AES192, AES256, Cast128, and RC4. Additionally, we provide integration with advanced encryption solutions such as SQLiteCrypt and SQLCipher, which typically require separate licenses but are included in our product at no extra cost.

SQLite AES256 Encrypted Connection

Here is a code example of AES256 encryption:

using System;
using Devart.Data.SQLite;

namespace SQLiteAES256Example
{
   class Program
   {
    	static void Main(string[] args)
    	{
        	// Open/create an unencrypted database
        	SQLiteConnection conn = new SQLiteConnection("DataSource=sakila.db;Encryption=AES256;FailIfMissing=false;");
        	conn.Open();
        	conn.ChangePassword("best"); // Encrypt the database with the password "best"
        	conn.Close();

        	// Open the encrypted database with AES256 encryption
        	conn = new SQLiteConnection("DataSource=sakila.db;Encryption=AES256;Password=best; FailIfMissing=false;");
        	conn.Open();
        	Console.WriteLine("Encrypted database opened successfully.");
        	conn.Close();
    	}
   }
}

SQLite SQLiteCrypt Encrypted Connection

This example demonstrates how to use SQLiteCrypt encryption with the Devart.Data.SQLite library to secure your SQLite database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Devart.Data.SQLite;

namespace SQLiteCryptSample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Open/create an unencrypted database
            SQLiteConnection conn = new SQLiteConnection("DataSource=database.db;Encryption=SQLiteCrypt;EncryptionLicenseKey=00000-000-0000000-00000;FailIfMissing=false;");
            conn.Open();
            conn.ChangePassword("best"); // Encrypt the database with the password "best"
            conn.Close();

            // Open the already encrypted database
            conn = new SQLiteConnection("DataSource=database.db;Encryption=SQLiteCrypt;EncryptionLicenseKey=00000-000-0000000-00000;FailIfMissing = false;password=best");
            conn.Open();
            conn.ChangePassword("best2"); // Change password to "best2"
            conn.Close();

            // Open the database with the new password
            conn = new SQLiteConnection("DataSource=database.db;Encryption=SQLiteCrypt;EncryptionLicenseKey=00000-000-0000000-00000;FailIfMissing=false;password=best2");
            conn.Open();
            conn.ChangePassword(""); // Assign an empty password - decrypt the database
            conn.Close();

            // Open the unencrypted database
            conn = new SQLiteConnection("DataSource=database.db;FailIfMissing = false;");
            conn.Open();
        }
    }
}

SQLite SQLCipher Encrypted Connection

This example provides instructions on encrypting, accessing, and decrypting SQLite databases with SQLCipher, ensuring your data remains secure. Below is the code snippet for these operations:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Devart.Data.SQLite;

namespace SQLCipherSample
{
	class Program
	{
    	static void Main(string[] args)
    	{
        	// Creating a new database without encryption
        	SQLiteConnection conn = new SQLiteConnection("DataSource=dbUnencrypted.db;FailIfMissing=false;Pooling=false");
        	conn.Open();

        	// Add a table to the database, because it must not be empty if we want to encrypt it with SQLCipher
        	SQLiteCommand cmd = new SQLiteCommand(@"CREATE TABLE DEPT (
                                                    	DEPTNO INTEGER PRIMARY KEY,
                                                    	DNAME VARCHAR(14),
                                                    	LOC VARCHAR(13)
                                                	);", conn);
        	cmd.ExecuteNonQuery();
        	cmd.CommandText = "INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');";
        	cmd.ExecuteNonQuery();
        	conn.Close();

        	// Use the SQLCipherExport method to create a new database dbEncrypted.db with SQLCipher encryption
        	// and copy data from the unencrypted database dbUnencrypted.db
        	conn = new SQLiteConnection("DataSource=dbUnencrypted.db;Encryption=SQLCipher;FailIfMissing=false;Pooling=false;EncryptionLicenseKey=OmNjXDZDiz....ljNw");
        	conn.Open();
        	conn.SQLCipherExport("dbEncrypted.db", "password");
        	conn.Close();

        	// Open the encrypted with SQLCipher dbEncrypted.db database
        	conn = new SQLiteConnection("DataSource=dbEncrypted.db;Encryption=SQLCipher;FailIfMissing=false;Password=password;Pooling=false;EncryptionLicenseKey=OmNjXDZDiz....ljNw");
        	conn.Open();
        	cmd.CommandText = "select dname from dept where deptno=10";
        	cmd.Connection = conn;
        	Console.WriteLine(cmd.ExecuteScalar());
        	Console.WriteLine("The encrypted database is successfully opened");
        	conn.ChangePassword("newPass"); //Use the ChangePassword method to change the password for the dbEncrypted.db database
        	conn.Close();

        	// Open the encrypted with SQLCipher dbEncrypted.db database with the new password
        	conn = new SQLiteConnection("DataSource=dbEncrypted.db;Encryption=SQLCipher;FailIfMissing=false;Password=newPass;Pooling=false;EncryptionLicenseKey=OmNjXDZDiz....ljNw");
        	conn.Open();
        	cmd.CommandText = "select dname from dept where deptno=10";
        	cmd.Connection = conn;
        	Console.WriteLine(cmd.ExecuteScalar());
        	Console.WriteLine("The database with the new password is successfully opened");
        	conn.Close();

        	// decrypt the database
        	// Use the SQLCipherExport method to create a new database dbUnencrypted1.db without SQLCipher encryption and copy
        	// data from the encrypted database dbEncrypted.db
        	conn = new SQLiteConnection("DataSource=dbEncrypted.db;Encryption=SQLCipher;FailIfMissing=false;Password=newPass;Pooling=false;EncryptionLicenseKey=OmNjXDZDiz....ljNw");
        	conn.Open();
        	conn.SQLCipherExport("dbUnencrypted1.db", "");
        	conn.Close();

        	// Open the decrypted database
        	conn = new SQLiteConnection("DataSource=dbUnencrypted1.db;FailIfMissing=false;Pooling=false");
        	conn.Open();
        	cmd.CommandText = "select dname from dept where deptno=10";
        	cmd.Connection = conn;
        	Console.WriteLine(cmd.ExecuteScalar());
        	Console.WriteLine("The decrypted database is successfully opened");
        	conn.Close();
    	}
	}
}

SQLite Encrypted Connection Properties

SQLite encrypted connection properties are important for securing access to your database.
Key properties include:

Password	The user's password to connect to an encrypted database
Encryption	Determines the encryption for SQLite extension to use. Note that if you want to use encryption mode other than None (which means no encryption), you need to buy the corresponding extension separately and compile the SQLite engine with it. dotConnect for SQLite supports SEE, CEROD, SQLCipher, and SQLiteCrypt encryption extensions.
SQLiteCrypt License Key	The software license key for SQLiteCrypt extension. Necessary if connecting to SQLiteCrypt-encrypted databases.

Pooling Connection String Options

Pooling connection string options help us optimize the database connections management and performance.

Validate Connection	Specifies whether to validate connections that are being got from the pool.
Pooling	If true, by default, the SQLiteConnection object is drawn from the appropriate pool or is created and added to the appropriate pool.
Max Pool Size	The maximum number of connections allowed in the pool. Setting the *Max Pool Size* value of the *ConnectionString* can affect performance. The default value is 100.
Min Pool Size	The minimum number of connections allowed in the pool. The default value is 0.

Advanced Connection String Options

Advanced connection string options allow additional customization and control over database connections.
Key options include:

License Key	Specify your license key in this parameter. This is required only when using .NET Standard compatible assemblies. See Licensing .NET Standard (.NET Core) Projects for more information.
Default Command Timeout	The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.
Connection Lifetime	When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that period (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to the pool).
Connect Timeout/Connection Timeout	The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error message. The default value is 15.

Conclusion

SQLite connection strings are essential for linking applications to SQLite databases. Without them, it would be impossible to ensure proper communication between the application and the database engine used as a part of the application back-end. This article offers comprehensive guides on crafting SQLite connection strings, detailing their parameters and the various methods for their implementation.

In this context, dotConnect for SQLite emerges as an invaluable tool that simplifies these connections, enabling the development of high-performance applications built on SQLite. To fully explore the capabilities of dotConnect for SQLite, download the Free Trial and see how it performs under your specific workload!