Setup

Add this package to a netstandard compatible project:

Required: add ONLY ONE of the following packages to your common project:

• SQLitePCLRaw.bundle_e_sqlite3 for a normal database file
• SQLitePCLRaw.bundle_e_sqlcipher for an encrypted database file

Then call the init method once. That can be in your App.cs:

SQLitePCL.Batteries_V2.Init()

Features

• Netstandard 2+
• Uses SQLitePCLRaw for sqlite raw communication
• Compatible with SQLitePCLRaw standard and cypher
• Stable and used in tons of apps

For a key/value store based on sqlite, or a drop-in replacement (alternative) to the unstable Akavache, check KeyValueLite

Other Features (compared to oysteinkrog)

• Multiple primary key support Ex:

publicclassPrivacyGroupItem{[PrimaryKey]publicintGroupId{get;set;}[PrimaryKey]publicintContactId{get;set;}}db.Delete<PrivacyGroupItem>(groupId,contactId);

• Projections now have the expected result type Ex: IEnumerable<int> ids = from pgi in db.Table<PrivacyGroupItem>() where pgi.PrivacyGroupId == groupId select pgi.ContactId;

• New method to query simple types (ie: string, ...) as Query can query only complex types (ie: T must be a class/stuct with a default constructor) Signature: IEnumerable<T> ExecuteSimpleQuery<T>(string query, params object[] args) Usage: ExecuteSimpleQuery<string>("select 'drop table ' || name || ';' from sqlite_master where type = 'table'")

• No asynchronous API. Use Task.Run() if you want asynchronous calls.
  Note that while SQLitePCLRaw states that the database can be accessed by multiple threads simultaneously, experience proves that you should always prevent multithreaded access, otherwise rare random crash occur. You can use SemaphoreSlim to serialize calls.

• Another trick
  Use transactions! In sqlite they speed up all queries a lot.

Original Fork

https://github.com/praeclarum/sqlite-net

Usage

Note: see unit tests for more examples.

Define the database schema using a code first approach.

publicclassDbStock{[PrimaryKey,AutoIncrement]publicintId{get;set;}[MaxLength(8)]publicstringSymbol{get;set;}}publicclassDbValuation{[PrimaryKey,AutoIncrement]publicintId{get;set;}[Indexed()]publicintStockId{get;set;}[Indexed("Stock",1)]//This defines an index with multiple keyspublicDateTimeTime{get;set;}[Indexed("Stock",2)]publicdecimalPrice{get;set;}}

Create the schema

vardbFilePath=Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);dbFilePath=Path.Combine(dbFilePath,"store.db3");varexists=File.Exists(dbFilePath);varisFirstInit=!exists;if(isFirstInit){//Make sure folder existsvarfolderPath=Path.GetDirectoryName(dbFilePath);Directory.CreateDirectory(folderPath);File.CreateText(dbFilePath).Dispose();}db=newSQLiteConnection(dbFilePath);if(isFirstInit){//Create schemadb.CreateTable<DbStock>();db.CreateTable<DbValuation>();//You may store your schema version using Xamarin Essentials//Xamarin.Essentials.Preferences.Set("DbVersion", 1); }

Read, Add, Update, Delete rows in tables

Simple add, update and delete:

varstock=newDbStock(){Symbol="EUR"};db.Insert(stock);stock.Symbol="USD";db.Update(stock);db.Delete(stock);db.DeleteAll(allStocks);//Delete by iddb.DeleteIn<DbDay>(new[]{1,2,3});

After the Insert call, stock.Id will be set, because Id has the AutoIncrement attribute.

Simple query using LINQ. Most linq operators work:

varstocksStartingWithA=db.Table<DbStock>().Where(stock =>stock.Symbol.StartsWith("A")).OrderBy(stock =>stock.Symbol).ToList();varallStocks=db.Table<DbStock>().ToList();

Advanced queries using SQL:

vardbValuation=db.Query<DbValuation>("select * from DbValuation where StockId = ?",stock.Id);db.Execute("delete * from DbValuation where StockId = ?",stock.Id);

The T in db.Query<T> specifies the object to create for each row. It can be a table class, or any other class whose public properties match the query columns.

publicclassVal{publicdecimalMoney{get;set;}publicDateTimeDate{get;set;}}publicstaticIEnumerable<Val>QueryVals(SQLiteConnectiondb,Stockstock){returndb.Query<Val>("select 'Price' as 'Money', 'Time' as 'Date' from Valuation where StockId = ?",stock.Id);}

Using an encrypted database file

Add the nuget SQLitePCLRaw.bundle_e_sqlcipher to your common project.

Option 1 (preferred)

Use the encryptionKey parameter in the constructor of SQLiteConnection.

Then use the db as usual.

With this option, the encryption key is kept in memory. That should not be an issue.

Option 2 (roots)

Add this code right after opening or creating the db, and before any other db call:

stringkey="yourCryptingKey";vardb=newSQLiteConnection(filePath);varok=db.ExecuteScalar<string>($"PRAGMA key = '{key}';");if(ok!="ok")thrownewException("Bad key");

If you will use InsertAll(), InsertOrUpdateAll(), ReplaceAll() you must create a new class deriving from SQLiteConnection and override Clone() so it sets the encryption key after cloning.

Then use the db as usual.

Final Thoughts

You can read the version of the cypher lib using the code. Check the Zenetik website for more information.

varcipherVer=db.ExecuteScalar<string>("PRAGMA cipher_version");if(String.IsNullOrWhiteSpace(cipherVer))thrownewException("This build is not using SQL CIPHER");

Using transactions

Warning: all transactions methods create a state in this connection (the transaction depth).
Be sure to not share the connection with other simultaneous threads.
You can use using var tempConnection = connection.Clone() to prevent this issue.

The following methods use Clone to clone the connection and prevent any interaction of the transaction they create with your code:
InsertAll(), InsertOrUpdateAll(), ReplaceAll()
They all have a boolean parameter to disable this behavior (beware of performances), which will also prevent a correct rollback in case an exception occurs.

Standard transaction:

BeginTransaction();
...Commit();//or Rollback();

Nested transactions:

varsavepoint=SaveTransactionPoint();
...Release(savepoint);//orRollbackTo(savepoint);

Limitations

Most databases (except SQLServer) store DateTimeOffset as UTC, forgetting the offset part. SQlite is not an exception.