using (var stream = new MemoryStream(Encoding.UTF8.GetBytes("I'm a byte stream")))
using (var db = SQLite3.Open(":memory:"))
{
    db.ExecuteAll(
        @"CREATE TABLE foo (w int, x float, y string, z blob);
            INSERT INTO foo (w,x,y,z) VALUES (0, 0, '', null);");

    db.Execute("INSERT INTO foo (w, x, y, z) VALUES (?, ?, ?, ?)", 1, 1.1, "hello", stream);

    var dst = db.Query("SELECT rowid, z FROM foo where rowid = ?", db.LastInsertedRowId)
                .Select(row => db.OpenBlob(row[1].ColumnInfo, row[0].ToInt64(), true))
                .First();

    using (dst) { stream.CopyTo(dst); }

    foreach (var row in db.Query("SELECT rowid, * FROM foo"))
    {
        Console.Write(
                    row[0].ToInt64() + ": " +
                    row[1].ToInt() + ", " +
                    row[2].ToInt64() + ", " +
                    row[3].ToString() + ", ");

        if (row[4].SQLiteType == SQLiteType.Null)
        {
            Console.Write("null\n");
            continue;
        }

        using (var blob = db.OpenBlob(row[4].ColumnInfo, row[0].ToInt64(), false))
        {
            var str = new StreamReader(blob).ReadToEnd();
            Console.Write(str + "\n");
        }
    }
}

using (var db = SQLiteDatabaseConnectionBuilder.InMemory.BuildAsyncDatabaseConnection())
using (var stream = new MemoryStream(Encoding.UTF8.GetBytes("I'm a byte stream")))
{
    await db.ExecuteAllAsync(
        @"CREATE TABLE foo (w int, x float, y string, z blob);
          INSERT INTO foo (w,x,y,z) VALUES (0, 0, '', null);");

    await db.ExecuteAsync("INSERT INTO foo (w, x, y, z) VALUES (?, ?, ?, ?)", 1, 1.1, "hello", stream);

    var dst =
        await db.Query("SELECT rowid, z FROM foo where y = 'hello'")
                .Select(row => db.OpenBlobAsync(row[1].ColumnInfo, row[0].ToInt64(), true))
                .FirstAsync()
                .ToTask()
                .Unwrap();

    using (dst)
    {
        await stream.CopyToAsync(dst);
    }

    await db.Query("SELECT rowid, * FROM foo")
            .Select(row =>
                row[0].ToInt64() + ": " +
                row[1].ToInt() + ", " +
                row[2].ToInt64() + ", " +
                row[3].ToString() + ", " +
                row[4].ToString())
            .Do(str => { Console.WriteLine(str); });
}

// Per thread builder to limit the number of instances.
private static readonly ThreadLocal<TestObject.Builder> testObjectBuilder = new 
        ThreadLocal<TestObject.Builder>(() => new TestObject.Builder());

public void Example()
{
    // You must provide a result selector to convert a result set row into an object.
    // By making this an explicit parameter, SQLitePCL.pretty can support immutable object builders.
    var resultSelector = 
        ResultSet.RowToObject(
            () => testObjectBuilder.Value, 
            o => ((TestObject.Builder)o).Build());

    using (var db = SQLite3.OpenInMemory())
    {
        // Creates the table and indexes if they do not yet exist.
        db.InitTable<TestObject>();

        // Insert a new object into the database that has nullable values.
        // InsertOrReplace returns the resulting object in the database along with
        // its new primary key and any default values assigned by the database.
        var inserted = 
            db.InsertOrReplace(
                new TestObject.Builder() { Value = "Hello" }.Build(), 
                resultSelector);

        // Lookup the object in the database.
        TestObject found;
        if (!db.TryFind(inserted.Id.Value, resultSelector, out found))
        {
            throw new Exception("item not found");
        }

        // We can use linq like syntax to write queries against the database.
        var query = 
            SqlQuery.From<TestObject>()
                    .Select()
                    // This creates a named bind parameter ":value"
                    .Where<string>((x, value) => x.Value.Contains(value))
                    .OrderBy(x => x.Value)
                    .Take(100)
                    .Skip(0);

        using (var stmt = db.PrepareStatement(query))
        {
            stmt.BindParameters[":value"].Bind("ell");
            // This will throw if no results are returned
            stmt.Query().First();
        }

        // Delete the object from the database.
        TestObject deleted;
        if (!db.TryDelete(inserted.Id.Value, resultSelector, out deleted))
        {
            throw new Exception("deletion failed");
        }
    }
}


public sealed class TestObject : IEquatable<TestObject>
{
    public class Builder
    {
        private long? id;
        private string value;

        public long? Id { get { return id; } set { this.id = value; } }
        public string Value { get { return value; } set { this.value = value; } }

        public TestObject Build()
        {
            return new TestObject(id, value);
        }
    }

    private long? id;
    private string value; 

    private TestObject(long? id, string value)
    {
        this.id = id;
        this.value = value;
    }

    [PrimaryKey]
    public long? Id { get { return id; } }

    public string Value { get { return value; } }

    public bool Equals(TestObject other)
    {
        return this.Id == other.Id && 
            this.Value == other.Value;
    }

    public override bool Equals(object other)
    {
        return other is TestObject && this.Equals((TestObject)other);
    }

    public override int GetHashCode()
    {
        int hash = 17;
        hash = hash * 31 + this.Id.GetHashCode();
        hash = hash * 31 + this.Value.GetHashCode();

        return hash;
    }
}