using System;

using System.Threading.Tasks;

using Faithlife.Data;

using Faithlife.Data.SqlFormatting;

using Microsoft.Data.Sqlite;

public class Program

{

    public static async Task Main()

    {

        // create connection; open automatically and just in time

        await using var connector = DbConnector.Create(

            new SqliteConnection("Data Source=:memory:"),

            new DbConnectorSettings { AutoOpen = true, LazyOpen = true });

        // create widgets table

        await connector.Command(@"

create table widgets (

    id integer primary key autoincrement,

    name text not null,

    height real not null)")

            .ExecuteAsync();

​

        // insert widgets in a transaction

        var widgets = new[]

        {

            new Widget("First", 6.875),

            new Widget("Second", 1.414),

            new Widget("Third", 3.1415),

        };

        await using (await connector.BeginTransactionAsync())

        {

            foreach (var widget in widgets)

            {

                await connector.Command(Sql.Format($@"

insert into widgets (name, height)

values ({widget.Name}, {widget.Height})"))

                    .Cache().ExecuteAsync();

            }

            await connector.CommitTransactionAsync();

        }

        // get short widgets

        var maxHeight = 5.0;

        foreach (var widget in await connector

            .Command(Sql.Format(

                $"select name, height from widgets where height <= {maxHeight}"))

            .QueryAsync<Widget>())

        {

            Console.WriteLine(widget.ToString());

        }

        // get minimum and maximum heights

        var (min, max) = await connector

            .Command("select min(height), max(height) from widgets")

            .QuerySingleAsync<(double, double)>();

        Console.WriteLine($"minimum height {min}, maximum height {max}");

    }

    private record Widget(string Name, double Height);

}

​