using System;
using System.Collections.Generic;
using ServiceStack;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using ServiceStack.DataAnnotations;

/*** ASYNC in OrmLite
 * Essentially all OrmLite public API's have async equivalents of the same
 * name with an additional conventional `*Async` suffix. The Async API's also
 * take an optional CancellationToken making converting sync code trivial,
 * where you just add the `*Async` suffix and `await` keyword as seen below:
 ***/

public class User
{
    public long Id { get; set; }

    [Index]
    public string Name { get; set; }
    public DateTime CreatedDate { get; set; }
    public override string ToString() => Name;
}

var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = dbFactory.Open();     //Open ADO.NET DB Connection

db.DropAndCreateTable<User>(); //DROP (if exist) and CREATE Table from User POCO

await db.InsertAsync(          //INSERT multiple Users by params
    new User { Id = 1, Name = "A", CreatedDate = DateTime.Now },
    new User { Id = 2, Name = "B", CreatedDate = DateTime.Now },
    new User { Id = 3, Name = "C", CreatedDate = DateTime.Now },
    new User { Id = 4, Name = "C", CreatedDate = DateTime.Now });

var rowsC = await db.SelectAsync<User>(x => x.Name == "C"); //SELECT by typed expression
"No of 'C' Rows: {0}, Ids:".Print(rowsC.Count);  //= 2
rowsC.ConvertAll(x => x.Id).PrintDump();         //= 3,4

await db.DeleteAsync<User>(x => x.Name == "C");             //DELETE by typed expression

var remainingC = await db.SelectAsync<User>("Name= @name", new { name="C" }); //Custom SQL
"No of 'C' Rows: {0}".Print(remainingC.Count);   //= 0

var rowsLeft = await db.SelectAsync<User>();
"Rows Left: {0}".Print(rowsLeft.Count);          //= 2
rowsLeft.PrintDump();                            //= A,B