Database operations

Create Database

Create dababase file if not exist

var context = new DbContext(_pathOfDb);
context.Database.CreateFile();

Note : the database can be autocreate by the DbContext if you set autoCreateDatabaseFile to true.

DbContext dbContext = new DbContext(_pathOfDb, autoCreateDatabaseFile : true);

Create In memory Database

DbContext dbContext = DbContext.InMemory();

Get SQLite version of DB

Get the SQLite version of database.

DbContext dbContext = new DbContext(_pathOfDb);
var version = await dbContext.Database.GetSQLiteVersionAsync();

User version of database

The user_version pragma will to get or set the value of the user-version integer at offset 60 in the database header. The user-version is an integer that is available to applications to use however they want. SQLite makes no use of the user-version itself.

Get the user version

DbContext dbContext = new DbContext(_pathOfDb);
var version = await dbContext.Database.GetUserVersionAsync();

Set the user version

DbContext dbContext = new DbContext(_pathOfDb);
await dbContext.Database.SetUserVersionAsync(currentUserVersion);

VACUUM

The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space.

DbContext dbContext = new DbContext(_pathOfDb);
await dbContext.Database.VacuumAsync();

WAL

There are advantages and disadvantages to using WAL instead of a rollback journal. Advantages include:

• WAL is significantly faster in most scenarios.
• WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
• Disk I/O operations tends to be more sequential using WAL.
• WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.

Enable Write-Ahead Log (WAL)

DbContext dbContext = new DbContext(_pathOfDb);
await dbContext.Database.EnableWALAsync();

Disable Write-Ahead Log (WAL)

DbContext dbContext = new DbContext(_pathOfDb);
await dbContext.Database.DisableWALAsync();