Edit

Connection strings

  • Article

A connection string is used to specify how to connect to the database. Connection strings in Microsoft.Data.Sqlite follow the standard ADO.NET syntax as a semicolon-separated list of keywords and values.

Keywords

The following connection string keywords can be used with Microsoft.Data.Sqlite:

Data Source

The path to the database file. DataSource (without a space) and Filename are aliases of this keyword.

SQLite treats paths relative to the current working directory. Absolute paths can also be specified.

If empty, SQLite creates a temporary on-disk database that's deleted when the connection is closed.

If :memory:, an in-memory database is used. For more information, see In-Memory databases.

Paths that start with the |DataDirectory| substitution string are treated the same as relative paths. If set, paths are made relative to the DataDirectory application domain property value.

This keyword also supports URI Filenames.

Mode

The connection mode.

Value Description
ReadWriteCreate Opens the database for reading and writing, and creates it if it doesn't exist. This is the default.
ReadWrite Opens the database for reading and writing.
ReadOnly Opens the database in read-only mode.
Memory Opens an in-memory database.

Cache

The caching mode used by the connection.

Value Description
Default Uses the default mode of the underlying SQLite library. This is the default.
Private Each connection uses a private cache.
Shared Connections share a cache. This mode can change the behavior of transaction and table locking.

Password

The encryption key. When specified, PRAGMA key is sent immediately after opening the connection.

Warning

Password has no effect when encryption isn't supported by the native SQLite library.

Note

The Password keyword was added in version 3.0.

Foreign Keys

A value indicating whether to enable foreign key constraints.

Note

The Foreign Keys keyword was added in version 3.0.

Value Description
True Sends PRAGMA foreign_keys = 1 immediately after opening the connection.
False Sends PRAGMA foreign_keys = 0 immediately after opening the connection.
(empty) Doesn't send PRAGMA foreign_keys. This is the default.

There's no need to enable foreign keys if, like in e_sqlite3, SQLITE_DEFAULT_FOREIGN_KEYS was used to compile the native SQLite library.

Recursive Triggers

A value that indicates whether to enable recursive triggers.

Note

The Recursive Triggers keyword was added in version 3.0.

Value Description
True Sends PRAGMA recursive_triggers immediately after opening the connection.
False Doesn't send PRAGMA recursive_triggers. This is the default.

Default Timeout

The default timeout (in seconds) for executing commands. The default value is 30. Command Timeout is an alias of this keyword.

This value can be overridden using DefaultTimeout which in turn can be overridden using CommandTimeout.

Note

The Default Timeout keyword was added in version 6.0.

Pooling

A value indicating whether the connection will be pooled.

Note

The Pooling keyword was added in version 6.0.

Value Description
True The connection will be pooled. This is the default.
False The connection won't be pooled.

Connection string builder

You can use SqliteConnectionStringBuilder as a strongly typed way of creating connection strings. It can also be used to prevent connection string injection attacks.

var connectionString = new SqliteConnectionStringBuilder(baseConnectionString)
{
    Mode = SqliteOpenMode.ReadWriteCreate,
    Password = password
}.ToString();

Examples

Basic

A basic connection string with a shared cache for improved concurrency.

Caution

Mixing shared-cache mode and write-ahead logging is discouraged. For optimal performance, remove Cache=Shared when the database is configured to use write-ahead logging.

Data Source=Application.db;Cache=Shared

Encrypted

An encrypted database.

Data Source=Encrypted.db;Password=MyEncryptionKey

Read-only

A read-only database that cannot be modified by the app.

Data Source=Reference.db;Mode=ReadOnly

In-memory

A private, in-memory database.

Data Source=:memory:

Sharable in-memory

A sharable, in-memory database identified by the name Sharable.

Data Source=Sharable;Mode=Memory;Cache=Shared

See also