Mobile and Tablets

Library 2.5.0.0 and greater runs without any changes on Mobile, Tablets, Desktop and Server systems and keeps the same database file format, you can even simply copy the data file to sync and all library versions will be able to read it.

Simply add reference in your Xamarin Android, iOS, UWP or cross platform project as following. You can also download Example Android App which is a Visual Studio 2017 Solution, for Visual Studio 2015 go to downloads page and look for example android project.



In addition reference to System.Data is also required. Library use IDisposable Interface .

The database file path must be proper full path, it can be created using following example code.

The example creates "afile.db" file on Android / Droid under Xamarin. It is also valid for iOS see the link after the code.

var aFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);

var dbfilepath = System.IO.Path.Combine(aFolder, "afile.db");

using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("uri=file://" + dbfilepath + ";"))
{
cnn.Open();
}

External Storage

To perform ACID transaction the library creates a file called -journal by default at the same location as the main database file. Starting with version 2.6.0.0 a user can also provide the location of journal file. Since journal file add extra I/O load on storage, the slow external storage devices like SD Cards which have slow or limited I/O can slow down the performance of write operations (insert, update and delete).

Such storage types are widely used on mobile operating systems, on mobile / tablet devices you can either change journal mode to in-memory, turn it off. You can also change it to keep it as persistent file on separate storage (disk) to use multiple disk controllers and to reduce the create and delete cycles. However file will keep growing and will require deletion after some time.

SYSCMD journal_mode = persist;

SYSCMD journal_mode = truncate ;

The fastest method while using the journal is to use in memory journal option. Each row processed can take up to 4KB of memory(subject to size of particular row and how transaction will be committed).

Note: When application prevents journaling or moves it to in-memory journal, if storage device produces an error before transaction is written to disk then there is a chance of data loss.
However In-Memory journals are still safe as long as there are no hardware or operating system fatal errors. Since during in-memory journaling a journal is still created in main memory, if there is an error during write operation, the transaction can still be rolled back from memory based journal without any loss of data. Due to this fact on mobile phones and tablet computers journal file should be moved to in-memory rather than creating it on disk for each operation.

SYSCMD journal_mode = memory;

SYSCMD journal_mode = off;

In C#

The following code is in C# and primary purpose is to make sure that each time connection is opened the journal mode is set to in memory. If you are opening database in read-only mode or not performing any write transaction e.g. insert, updates then you do not have to follow this method since no journal is created.

string connstr = "SchemaName=dbo;uri=file://examplefile";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(connstr))
{
cnn.Open();
if (cnn.State != ConnectionState.Open) { return; }

using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
	// You will have to run it each time you open connection
	// It will need to be performed on each schema file
	// following does not contain schema so default schema file location
	// is updated syntax is : [SchemaName].journal_mode
	cmd.CommandText = "SYSCMD journal_mode = memory ; ";
	cmd.ExecuteNonQuery();

	// For other schemas in this case db1 is the schema name
	cmd.CommandText = "ATTACH DATABASE 'c:\\Data\\db1.db' AS 'db1'";
	cmd.ExecuteNonQuery();

	// Changing journal mode for db1 schema
	cmd.CommandText = "SYSCMD db1.journal_mode = memory ; ";
	cmd.ExecuteNonQuery();

	// Your other database related commands can go after above command
	//cmd.CommandText = "Create table xyz (id Integer);";
	//cmd.CommandText = "insert.."
	//cmd.CommandText = "delete.."

}
}

Above methods are not required, but helpful when developing in Xamarin or similar technologies they will benefit the application end users. Tip: To help yourself you can use the using directive to eliminate typing the full name space.

The Example Android App produces following screen shot, click on mail icon to check if in memory and on disk database has been created, a message on Debug/Console window is also shown.