/ (Cipher)

Simple.Sqlite

A very simple Sqlite wrapper to plug spiders with it

Underlying library: Microsoft.Data.Sqlite

Compatibility

Huge compatibility, supports:

• NET 8.0
• NET 6.0
• NetCore 3.1
• Net Framework 4.6.1
• Net Standard 2.0
  • Net Core 2.0+
  • Mono 5.4+
  • Xamarin.iOS 10.14+
  • UWP 10.0.16299+
  • Unity 2018.1+

Table of Contents

• Simple.Sqlite
  • Compatibility
  • Table of Contents
  • Modules and Options
  • How to get started:
• ConnectionFactory - Allow to use as Extensions
  • How to use:
  • What this module automates ?
    • Auto fill parameters
    • Migration
  • Using with Dependency Injection
    • Using with ASP .Net
• NoSqliteStorage - No-sql document storage
  • How to use:
  • What this module automates ?
• ConfigurationDB - Configuration storage
  • How to use:
  • What this module automates ?
• Backup
• Reutilizing types

Modules and Options

There are three modules in this package

• Extension-based database manipulation
• SqliteDB -> Sqlite wrapper
• No-sql document storage
• Configuration storage

How to get started:

Install the NuGet package: Install-Package Simple.Sqlite

Look at the examples int the project Test and the Samples folder

Or follow any of examples bellow

For Cipher (password) support, use NuGet package: Install-Package Simple.Sqlite.Cipher instead

ConnectionFactory - Allow to use as Extensions

A Sqlite connection factory for ISqliteConnection to use with extension methods

How to use:

// Create a new instance
using var cnn = Simple.Sqlite.ConnectionFactory.CreateConnection("myExtendedStuff.db");

// Create a DB Schema
cnn.CreateTables()
   .Add<MyData>()
   .Commit();

var d = new MyData()
{
    //fill your object
};
// call INSERT
cnn.Insert(d);

// use GetAll to retrieve all data
var allData = cnn.GetAll<MyData>();
// Use queries to get back data
var allBobs = cnn.Query<MyData>("SELECT * FROM MyData WHERE MyName = @name ", new { name = "bob" });

What this module automates ?

Auto fill parameters

This library provides a Query operation similar to Dapper, it can return a query as an Enumerable of your class

var allData = cnn.GetAll<MyData>();

And supports objects (even anonymous) as parameters

var allBobs = cnn.Query<MyData>("SELECT * FROM MyData WHERE MyName = @name ", new { name = "bob" });

Also, it supports easy Insertion

var d = new MyData()
{
    //fill your object
};
// call INSERT
cnn.Insert(d);

And a VERY efficient, transaction based BulkInsertion

MyData[] lotsOfData = getLotsOfData();

// call INSERT
cnn.BulkInsert(lotsOfData);

Tip: For multi-million insertion, 5k blocks are a good start point

Migration

This library has a very simple Migration tah can:

• Create new tables
• Add columns to existing tables

To update your db schema just call CreateTables() and add your classes with Add<T> and then Commit()

// Create a connection...
using var cnn = ConnectionFactory.CreateConnection("myStuff.db");
// ... or new SqliteDB instance (see more below)
SqliteDB cnn = new SqliteDB("myStuff.db");


// Create a DB Schema
 var migrationResult = cnn.CreateTables()
                          .Add<MyData>()
                          .Commit();

A TableCommitResult will be returned with all changes made

This command will not migrate DATA only the schema

You can make changes on the table definition before it commits with:

cnn.CreateTables()
   .Add<MyData>()
      .ConfigureTable(t => { /* change last added table here */ })
   .Add<NextTable>()
   .Commit();

You can use Attributes on your properties to create columns marked with:

• PrimaryKey
• Allow Null
• Not Null
• Default Value
• Unique

// column tweak example
cnn.CreateTables()
  .Add<MyData>()
     .ConfigureTable(t => t["MyId"].IsUnique = true)
   .Commit();

Also,

• Int properties with PK Attribute is also created as Auto-Increment
• If neither Not-Null nor Allow-Null is applied, the library will assume by the attribute type

Using with Dependency Injection

// Create a new instance
var db = ConnectionFactory.FromFile("myExtendedStuff.db");
// add to DI
services.AddSingleton(db);

// Execute schema Migration
using var cnn = db.GetConnection();
var result = cnn.CreateTables()
   .Add<MyData>()
   .Commit();

// result contains migration details

Using with ASP .Net

[ApiController]
public class MyController : Controller
{
  private readonly ConnectionFactory db;
  private readonly ILogger logger;
  public AuthController(IDatabase db, ILogger<MyController> logger){
    this.db = db;
    this.logger = logger;
  }

  [HttpGet]
  public async Task<IActionResult> GetData(){
    using var cnn = db.GetConnection();
    return cnn.GetWhere<MyData>("SomeField", 1234).ToArray();
  }
}

NoSqliteStorage - No-sql document storage

Need a fast to setup, no-installation no-sql database ? This module get you covered

Your data will be stored encoded (serialized) with BSON

How to use:

// create a new instance
NoSqliteStorage db = new NoSqliteStorage("myStuff.db");
// build your complex type
MyData d = new MyData()
{
    MyId = (int)DateTimeOffset.Now.ToUnixTimeSeconds(),
    MyName = "My name is bob",
    MyBirthDate = DateTime.Now,
    MyUID = Guid.NewGuid(),
    MyWebsite = new Uri("http://example.com"),
    MyDecimalValue = 123.4M,
    MyDoubleValue = 456.7,
    MyFloatValue = 789.3f,
    MyEnum = MyData.eIntEnum.Zero,
};
// Store it serialized with BSON
db.Store(d.MyUID, d);
// simple call Retrieve<T> to get your data deserialized back
var d2 = db.Retrieve<MyData>(id);

What this module automates ?

No-install, ready to use no-sql database

• No tables
• No migration
• no-nothing 'relational'

ConfigurationDB - Configuration storage

Need to store some options or user settings ? This module will cover that

How to use:

// Create a new instance
ConfigurationDB db = new ConfigurationDB("myStuff.db");

// store your data with Key-Category pair
db.SetConfig<string>("Font", "User.UI.FrontPanel", "Arial");
db.SetConfig<Color>("BackColor", "User.UI.FrontPanel", Color.Red);

// retrieve your data with "default option"
var myFont = db.GetConfig<string>("Font", "User.UI.FrontPanel", "Times");
var myBkg  = db.GetConfig<Color>("BackColor", "User.UI.FrontPanel", Color.Green);

// remove config
db.RemoveConfig("Font", "User.UI.FrontPanel");

What this module automates ?

This automates various configuration save-set scenarios

Natively supported types:

• All C# "primitive" types
• string
• decimal
• DateTime
• TimeSpan
• Guid
• byte[]
• System.Drawing.Color

Primitive types is all types that type.IsPrimitive == true. Is all types you expect to be primitives: int, float, double, byte, etc...

Check all on this MSDN article

Backup

When a new SqliteDB instance is created a new backup of the database file is made.

As NoSqliteStorage and ConfigurationDB internally uses SqliteDB, they also create a backup

First, the database file is copied and compressed as *.bak.gz Later backups, the old *.bak.gz is renamed to *.old.gz and a new *.bak.gz is created

See an example with a database named data.db:

• On first execution, there is no file
  • a new empty data.db will be created
  • a new also empty data.db.bak.gz is created
• On later execution,
  • the backup data.db.bak.gz will be renamed to data.db.old.gz
  • a new data.db.bak.gz will be created from data.db

Every execution, you have the previous version available as a backup

• To disable backups, you can use the static property as follows:

//this will disable the creation of backups of any posterior instances
SqliteDB.EnabledDatabaseBackup = false;
//this is specially useful when the database reaches a significant size

If you decided to use multiple instances with the same database file, you can avoid multiple backups by reutilizing types

Reutilizing types

If you already have a database and want to stick more stuff to it, you can simply:

• Already have a SqliteDB instance ?

var noSql = NoSqliteStorage.FromDB(mySqliteDB);
var cfg = ConfigurationDB.FromDB(mySqliteDB);

• Already have a NoSqliteStorage instance ?

var db = SqliteDB.FromDB(noSql);
var cfg = ConfigurationDB.FromDB(mySqliteDB);

• Already have a ConfigurationDB instance ?

var db = SqliteDB.FromDB(cfg);
var noSql = NoSqliteStorage.FromDB(cfg);