SqlDatabase - free embedded on disk and in memory sql database for .net, c#, vb.net, mono and xamarin

How to's for SQL Database

Here you can find different ways of doing tasks. All the How to's are designed for individuals who already posses basic sql knowledge, for starters see documentation to get started.

Create disk based database

SqlDatabase.Net support on-disk and in-memory database schema or combination of two in single connection. After creating connection object use this connection string.

C# :                    
ConnectionObject.ConnectionString = "SchemaName=Sales;datasource=file://C:\\FolderName\\DatabaseFile.db;";
                    
VB.NET :                    
ConnectionObject.ConnectionString = "SchemaName=Sales;datasource=file://C:\FolderName\DatabaseFile.db;"

Other method is to use uri keyword, it's same and data source as two seperate words is also valid.

C# :
ConnectionObject.ConnectionString = "SchemaName=Sales;uri=file://C:\\FolderName\\DatabaseFile.db;";

VB.NET :
ConnectionObject.ConnectionString = "SchemaName=Sales;uri=file://C:\FolderName\DatabaseFile.db;"

Each key value is seperated by semicolon (;) then key = value.

Create in memory database

To create in memory database use the @memory keyword, you can initialize in-memory database either with connection object initialization or provide it later in connection string before opening connection.

C# :
ConnectionObject.ConnectionString = "SchemaName=Sales;datasource=file://@memory;";

VB.NET :
ConnectionObject.ConnectionString = "SchemaName=Sales;datasource=file://@memory;"

You can give schema any name, when accessing tables, views etc. use SchemaName.ObjectName e.g. Sales.Orders or Sales.ViewName

C# :                  
ConnectionObject.ConnectionString = "SchemaName=Sales;uri=file://@memory;";
                    
VB.NET :
ConnectionObject.ConnectionString = "SchemaName=Sales;uri=file://@memory;"

uri or datasource key = @memory tells the sqldatabase.net.dll to create entire database in memory.

Attach on Disk or in Memory database

You can use ATTACH Database command to attach database to existing connection, maximum 50 databases can be attached which can either reside in memory or on disk. Attach database command allow to combine multiple schema in single connection.

SQL :
ATTACH DATABASE '@memory' AS 'memdb1';

SQL :
ATTACH DATABASE 'C:\\FolderPath\\DatabaseName.db' AS 'db1';

To access objects use the schema name as either memdb1 or db1 e.g. memdb1.TableName, db1.TableName etc. Note: Attach database command first parameter is uri e.g. memory or file and second is the schema name. The command should be executed against existing connection.

Backup Database

You can use Backup Database command to backup existing database to disk or even from disk to memory or from memory to disk.

SQL :
BACKUP DATABASE 'memdb1' AS 'C:\\Backups\\memdb1backup.db';

SQL :
BACKUP DATABASE 'db1' AS 'C:\\Backups\\db1backup.db';

In first example memdb1 which is a in memory database is copied as memdb1backup.db file.
In second example db1 which is a on disk database is also backed up to disk as db1backup.db file.

Backup Restore to convert Database

SQL :

ATTACH DATABASE '@memory' AS 'memdb1';

BACKUP DATABASE 'C:\\Backups\\memdb1backup.db' AS 'memdb1';

In above example we are attaching a database in memory and then from disk restoring or taking a backup to that memory location. This technique is used to convert on disk database to in memory database for faster access or from even from removeable disk, the command is still backup database but works like restore. Later the same database can again be backed up to disk using backup database command e.g.

BACKUP DATABASE 'memdb1' AS 'C:\\Backups\\memdb1backup.db';

Encrypting Database

SQLDatabase.Net.dll support AES 256 encryption only and it is done on page level. To Encrypt you will have to provide a secret password or key in system commands (SYSCMD).
Note: Be careful when using encryption, all backups are also encrypted, in case key is lost or forgotten data will be lost. Any decryption method for AES 256 is not known.

SQL :
SYSCMD KEY = 'SuperSecretKey';

To change the password or secret key you can rekey the database, depending on size of the database it can take a while.

SQL :                    
                     
SYSCMD KEY = 'SuperSecretKey';  
                    
SYSCMD REKEY = 'NewSuperSecretKey';

ReKey should be executed after key system command, providing incorrect key will result in halt (hang) state or other unexpected results.

Decrypting Database

To Decrypt the database or to remove password, you can rekey it with empty key and take backup.
Note: You have to know the initial key to do the rekey and will need disk space to store the decrypted file.

SQL :
                   
SYSCMD KEY = 'SuperSecretKey';                   
                   
SYSCMD REKEY = ''; 
                   
BACKUP DATABASE 'sdbn' as 'C:\\FolderName\\Decryption.db';

Above code should be executed in the above order. The 'sdbn' is the default schema name, change that value to your SchemaName value from connection string. If you did not specify the schema name then use the sdbn.